Power Query und eindeutige Zeilen zuordnung

[Jockel]

Hallo, hier steht aber der Bereich_Verweis auf WAHR... Ich bin mir ziemlich sicher, dass du den SVERWEIS() nicht verstanden hast - sorry...

[Knochenjonny]

Mir ist noch was aufgefallen bei SVERWEIS, falls jemand das selbe Problem hatt.
Ich beziehe mich jetzt auf meine fertige Tabelle wo ich nicht nach @Name suche sondern nach @Bytes

Ich hatte zuerst =SVERWEIS([@Bytes];tbl_Sprachen;2;FALSCH) so wie mir das empfohlen wurde.
Da hatte ich aber noch in der Verweis Tabelle nur 2 Spalten, Bytes & Sprache.
Bei den über 8000 Einträgen wurde es aber etwas schwer nur anhand der Größe zu wissen um welchen Anime es sich genau handelt :D

Also habe ich in der Verweis Tabelle vorne noch zwei Spalten angefügt (Folder Path und Name), selbstverständlich habe die zur Tabelle hinzugefügt.
Nun habe ich das Ergebnis in der 4 Spalte erwartet, also Formel angepasst =SVERWEIS([@Bytes];tbl_Sprachen;4;FALSCH), hatt nicht funktioniert 

Ein bisschen probieren brachte mich erst nicht weiter und ich habe es dann so realisiert wie in meinem Bild aus dem vorletzten Post.
Folder Path und Name einfach hinter die ersten beiden Spalten gepackt dann ging die Formel
=SVERWEIS([@Bytes];tbl_Sprachen;2;FALSCH) wieder.

Das wollte ich aber genauer probieren, der Ästhetik wegen :19: .

Wenn im SVERWEIS die Matrix als tbl_Sprachen steht wird, wie SpaltenVERWEIS halt arbeitet, in der ersten Spalte nach dem Suchkriterium gesucht und bei Erfolg die Zelle Rechts daneben ausgegeben, wenn die Matrix nur 2 Spalten umfasst und der Spaltenindex auf  2 steht, nicht das mir jemand sagt ich kapiere die Zusammenhänge nicht. 
(wahlweise gerundet Wahr also es muss nur ansatzweise Stimmen, oder bei genauer Suche  Falsch es muss Exakt der Wert gefunden werden sonst #NV).


Nun stand als Suchkriterium @Bytes
Und in der ersten Spalte von meinem Verweis stand nun Folder Path, da konnte keine Übereinstimmung gefunden werden ^^

Da ich nun die Funktionsweise von Sverweis etwas besser kenne habe ich statt der ganzen Tabelle (es wird ja immer die erste Spalte durchsucht) einfach den Bereich angegeben.
=SVERWEIS([@Bytes];Verweis!C:D;2;FALSCH)

Jetzt sucht Excel in der gesamten Spalte C nach meinem Suchkriterium (@Bytes) und wenn es gefunden wird gibt es das Ergebnis aus D wieder weil es die 2 Spalte ist.

So steht das auch direkt bei Microsoft
Suchkriterium    (erforderlich)
Der Wert, nach dem Sie suchen möchten. Der gesuchte Wert muss sich in der ersten Spalte des Zellbereichs befinden, den Sie als Matrix angeben.

Warum Excel nicht einfach alle Zellen durchsucht wenn ich eine Matrix angebe und dann bei Erfolgreicher Suche das Ergebnis ausgibt welches sich dann entweder in der 2,3,4,... Spalte der Matrix befindet (Spaltenindex) keine Ahnung.
Vielleicht zu Aufwendig   War für mich noch ein letzter Stolperstein und wenn es anderen hilft  :100:

Jetzt ist die Tabelle jedenfalls hübsch sortiert, arbeitet wie gewünscht und ich danke shift-del für seine Hilfe und Zielführenden Anregungen 

[shift-del]

=SVERWEIS([@Bytes];Verweis!C:D;2;FALSCH)

Zwei Unschönheiten. Du benutzt nicht die struktuierten Verweise und man sollte nicht ohne Not auf eine komplette Spalte verweisen.
Wenn die Suchspalte nicht als erste Spalte in der Verweistabelle steht dann verwendet man INDEX()/VERGLEICH().

Code:

=INDEX(tbl_Sprachen[Sprache];VERGLEICH([@[Attributes.Size]];tbl_Sprachen[Bytes];0))

Ehrlich gesagt halte ich den Abgleich nach Dateigröße statt nach Dateiname für kontraproduktiv. Wenn mal Änderungen an einer Datei vorgenommen werden dann ändert sich auch die Dateigröße und schon stimmt die Zuordnung nicht mehr.

PS: Bitte unterlasse die satzweise Formatierung mit einer anderen Schriftart. Beim Zitieren kriege ich dann die Krise.

[Knochenjonny]

Alles klar °_° die Idee ist ja richtig gut, werde ich mich heute Nacht einmal mit beschäftigen. INDEX und VERGLEICH scheint interessant :)

Den Bezug auf den Namen zu legen ist aber sehr ... schwierig ^^, ich erstelle von all meinen DVD/BD ein Backup und sortiere dann entsprechend einem gleichen Schema.
Wenn ich keine Namen für die Folgen einer Serie habe, heißen alle Dateien EP01,EP02,EP03 und aufsteigend.
Nun haben aber knapp 90% meiner Sammlung genau diese Namen, da ich zu faul bin jeder Folge den Folgentitel hinzuzufügen.
Deswegen habe ich ganz viele EP01.
Da ich keinen Grund sehe an meinen Backups etwas zu verändern bleibt die Größe in Bytes mein eindeutigster Indikator von allen :)

Es sei denn man könnte aus zwei oder mehr Spalten eine ID erzeugen also Dateiname+Pfad   
Jetzt habe ich Lust das zu probieren  :22: Nein eigentlich entspricht meine Tabelle im jetzigen Zustand exakt dem was ich mir erwartet habe 

Das mit dem Formatieren tut mir ehrlich leid :(
Ich habe selbst gemerkt das plötzlich alles anders formatiert war nach Copy&Paste >_> habe extra noch alles markiert und selbe Schriftart und Schriftgröße ausgewählt.

[Knochenjonny]

Ich war fleißig und habe einmal einen Auszug meiner Tabelle als Anhang beigefügt.
Der Inhalt sollte beim Speichern erhalten geblieben sein, da ja niemand genau die Ordner und Dateien selber so hat.
Den Power Query Editor kann man ohne die Quelle vergessen der Lädt halt nichts und zeigt nur eine Warnung.

Umgesetzt habe ich jetzt meine Idee 2 Spalten als eindeutige  ID zusammenzuführen (innerhalb der Query), das mit den Bezug auf die Dateigröße hat mich doch nervös gemacht ^_^
Nun gibt es einen Index der den Pfad+Dateiname beinhaltet, also das ändert sich definitiv nicht mehr da ich meine Sammlung bereits richtig benannt habe und alles was neu ist folgt dem selben Schema.
Und Power Query schneidet mir nun die Dateiendung bei Name gleich mit ab, eine Spalte ausblenden hingegen geht definitiv nicht :(

Nun benötige ich auch nur noch 2 Spalten für meinen Bezug und weiss dennoch welcher Datei ich eine Sprache zuweise :D
Ergo behalte ich den SVERWEIS bei aber jetzt in der Form wie empfohlen wurde.

Was ich noch bemerkt habe bei meinen Versuchen, wenn nun eine neue Datei in einem Ordner hinzu kommt und ich die Query aktualisiere wird alles brav so abgearbeitet wie es soll,
neue Zeile in der Query, keine neue Zeile in der Bezug Tabelle, da noch nicht vorhanden in der Sprache steht als SVERWEIS #NV.

Jetzt wollte ich nur noch testen wie ich die neuen Infos in die Tabelle (tbl_Sprache) bekomme und war der Meinung Zeile hinzufügen und Text einfügen...
Ging nicht, Option war ausgegraut.
Wirklich vieeeele Versuche Später dann mit einer Test Tabelle brachte die Erkenntnis ^^

Tabellen habe ich einfach erstellt indem ich die gesamten Spalten markiert habe, warum auch nicht, dadurch waren aber ALLE Zeilen der ersten 2 Spalten (A,B) komplett als Tabelle formatiert (wirklich alle ^^)
Nun ich kann verständlicherweise in einer Excel Tabelle keine neuen Zeilen hinzufügen wenn bereits alle als Tabelle formatiert sind xD

Lösung ich habe die Tabelle nur in dem Bereich erstellt wie sie aktuell groß war =$A$1:$B$8655  :05:
Nun kann ich manuell neue Zeilen einfügen wenn neue Filme dazu kommen.

Das war mir wichtig zu erzählen falls da jemand auch drüber stolpert

Excel ist zwar keine Datenbank aber es lässt sich dennoch gut dafür missbrauchen. ^^

[Jockel]

Ich war fleißig ...

Achja...  den Rest habe ich gar nicht erst gelesen...

[Willie017]

hallo

warum der Umweg über die Bezugstabelle und SVERWEIS() bzw. INDEX()/VERGLEICH? Da Du Powerquery schon am Laufen hast empfiehlt sich doch eine selbst referenzierende Tabelle. Hier mal am Beispiel Deiner ersten Beispieltabelle

1. Erstelle eine neue leere Abfrage, ersetze den Code im erweiterten Editor durch den folgenden und benenne die Abfrage Sprachen. Erstelle nur eine Verbindung
   

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Folder Path", type text}, {"Name", type text}, {"Extension", type text}, {"Attributes.Size", Int64.Type}, {"Attributes.Hidden", type logical}, {"Sprache", type text}})
in
    #"Geänderter Typ"

       2. Erweitere die ursprüngliche Abfrage Test um folgende 2 Schritte, das fügt die ursprüngliche mit der soeben erstellten Abfrage zusammen

Code:

   #"Zusammenführte Abfragen" = Table.NestedJoin(#"Umbenannte Spalten",{"Folder Path", "Name"},Sprachen,{"Folder Path", "Name"},"Sprache",JoinKind.LeftOuter),
   #"Erweiterte Sprache" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Sprache", {"Sprache"}, {"Sprache"})
in
   #"Erweiterte Sprache"

Deine ursprüngliche Abfrage Test sieht jetzt also so aus

Code:

let
    Quelle = Folder.Files("C:\Test"),
    #"Entfernte Spalten" = Table.RemoveColumns(Quelle,{"Content"}),
    #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Entfernte Spalten",{"Folder Path", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes"}),
    #"Duplizierte Spalte" = Table.DuplicateColumn(#"Neu angeordnete Spalten", "Attributes", "Attributes - Kopie"),
    #"Erweiterte Attributes" = Table.ExpandRecordColumn(#"Duplizierte Spalte", "Attributes", {"Size"}, {"Attributes.Size"}),
    #"Erweiterte Attributes - Kopie" = Table.ExpandRecordColumn(#"Erweiterte Attributes", "Attributes - Kopie", {"Hidden"}, {"Attributes - Kopie.Hidden"}),
    #"Entfernte Spalten1" = Table.RemoveColumns(#"Erweiterte Attributes - Kopie",{"Date modified", "Date accessed", "Date created"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten1",{{"Attributes - Kopie.Hidden", "Attributes.Hidden"}}),
    #"Zusammenführte Abfragen" = Table.NestedJoin(#"Umbenannte Spalten",{"Folder Path", "Name"},Sprachen,{"Folder Path", "Name"},"Sprache",JoinKind.LeftOuter),
    #"Erweiterte Sprache" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Sprache", {"Sprache"}, {"Sprache"})
in
    #"Erweiterte Sprache"

Ab jetzt kannst Du die Sprache händisch in die Spalte Sprache eintragen und die Sortierung bleibt trotzdem erhalten, bei neuen Dateien bleibt die Sprache-Zelle leer bis du sie überschreibst

[Jockel]

hallo

warum der Umweg ..? Da Du Powerquery schon am Laufen hast ...

Hallo, "Öhm" das habe ich auch schon gefragt... ... Antwort:" weil ich keine Ahnung habe wie Power Query funktioniert." Ob da der code jetzt was ändert beim fleißigen TE..? "Öhm"...?

[Knochenjonny]

Danke für den Vorschlag mit der Query Variante Willie.
Ich hatte beim erstellen einer neuen Abfrage nicht genau gewusst wie, also habe ich bei "Power Query" auf "aus Tabelle/Bereich" geklickt damit ich überhaubt anfangen konnte zu probieren :)
Habe aber gemerkt das es reicht den Editor zu öffnen und dort dann eine neue leere Abfrage erstellt werden kann, praktisch :P

Das einzige was ich nicht umsetzen konnte bzw nicht nachvollziehen konnte ist "Erstelle nur eine Verbindung", was genau damit gemeint war wusste ich nicht.

Ich habe jetzt also zwei Query's die sich gegenseitig vergleichen ?

Es klappt jedenfalls, aber um das richtig nachvollziehen zu können müsste ich erstmal Power Query lernen.
Die anderen Sachen wie INDEX oder SVERWEIS sind recht gut verständlich und die Funktionsweise ist auch übersichtlich, die Query mit dem Editor.... da gibt es schon paar mehr Möglichkeiten.

Der Grund wieso ich überhaupt mit Power Query angefangen habe war einfach das ich eine Möglichkeit gesucht habe einen Ordner von der Festplatte in Excel einzulesen.
Im Netz stand nimm Power Query und bei öffnen einfach "aus Datei -> aus Ordner", habe ich so gemacht und wie man Spalten anpasst durch extrahieren usw. habe durch probieren gelernt.
Aber im Editor durch Probieren Dinge zu ändern, ohne zu wissen was ich da überhaupt mache  :s , suboptimal.

Danke trotzdem für das Aufzeigen dieses Alternativen Weges.


Ps. hab damit eben noch kurz experimentiert, wenn ich (als Beispiel) 4 neue Dateien erstelle und die Abfrage aktualisiere, erscheinen die Dateien auch an der Stelle wie sie im Windows Explorer sortiert sind.
Nun habe ich 2 davon eine Sprache definiert und getestet was die Abfrage macht wenn ich die Dateien wieder lösche.
Aus der Query verschwinden die neuen Zeilen dann. Wie zu erwarten war.
Wenn ich jetzt aber wieder neue Dateien erstelle die den selben Namen haben wie die vorherigen gelöschten und ich aktualisiere die Abfrage, dann erscheint die neue Datei plötzlich am Ende obwohl die ja Alphabetisch da nicht hingehört 

[Jockel]

... aber um das richtig nachvollziehen zu können müsste ich erstmal Power Query lernen...

Und was spricht dagegen..?