AW: aus per Formel berechneter Tabelle PQ Abfrage machen
20.11.2024 16:46:32
Yal
Hallo Christian,
es durchaus möglich und auch nicht sonderlich kompliziert.
- Füge deine Liste im Blatt "Liste" einen Überschrift mit "Film" und "Leute",
- wandle diese Liste in einer Tabelle (Menü "Einfügen"), und benenne sie in "tblListe" um
- übertrage die Tabelle in Power Query (Menü "Daten", "Aus Tabelle/Bereich"),
- mache daraus 2 Verweise, erster wird "tblListe_Index", zweiter "Ergebnis" benannt (nicht zwingend, aber für diese Anleitung relevant)
im "tblListe_Index"
- die Spalte "Leute" entfernen
- die Filme-Duplikat entfernen
- mache einen Join mit der Tabelle "Filme1"
- lass die passende Spalten raus: "Original Title" und "Release Date"
- filtere alle "Release Date" = null
- sortiere nach Release Date,
- Füge einen Index von 1 auf
M-Code sieht so aus:
let
Quelle = tblListe,
#"Gruppierte Zeilen" = Table.Group(Quelle, {"Film"}, {}),
#"Zusammengeführte Abfragen" = Table.NestedJoin(#"Gruppierte Zeilen", {"Film"}, Filme1, {"Const"}, "Filme1", JoinKind.LeftOuter),
#"Erweiterte Filme1" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Filme1", {"Original Title", "Release Date"}, {"Original Title", "Release Date"}),
#"Sortierte Zeilen" = Table.Sort(#"Erweiterte Filme1",{{"Release Date", Order.Ascending}}),
#"Gefilterte Zeilen" = Table.SelectRows(#"Sortierte Zeilen", each ([Release Date] > null)),
#"Hinzugefügter Index" = Table.AddIndexColumn(#"Gefilterte Zeilen", "Index", 1, 1, Int64.Type)
in
#"Hinzugefügter Index"
Im "Ergebnis"
- mache einen Join mit der Tabelle "tblListe_index"
- lasse Spalten "Original Title", "Release Date" und "Index" raus (ohne Präfix)
- filtere die Filme ohne Release Date raus
- mache einen Join mit der Tabelle Leute1, lass die Namen und Birth Date raus,
- rechne den Brutto-Jahresunterschied: Date.Year([Release Date]) - Date.Year([Birth Date])
- rechne den Brutto Tagesunterschied: Number.From([Release Date])-Number.From(Date.AddYears([Birth Date],[JahreDelta_brutto]))
- errechne den Netto-Jahresunterschied: if [RestTage_brutto] 0 then [JahreDelta_brutto] - 1 else [JahreDelta_brutto])
- und die Netto-Tagesunterschied: Number.From([Release Date])-Number.From(Date.AddYears([Birth Date],[JahreDelta]))
- bilde die Zeichenkette mit Rücksicht auf die Datentypen: "MRS " & Text.PadStart (Text.From([TageDelta]), 5, "0") & " " & [Original Title] & " (" & Date.ToText([Release Date]) & ") - " & [Name] & " (" & Date.ToText([Birth Date]) & ") " & Text.From([JahreDelta]) & "-" & Text.From([RestTage]))
- notiere in der richtige Reihenfole die Splaten zu behalten und "andere Spalten entfernen"
- Sortiere nach Release Date
Der M-Code sieht so aus:
let
Quelle = tblListe,
#"Zusammengeführte Abfragen" = Table.NestedJoin(Quelle, {"Film"}, tblListe_index, {"Film"}, "tblListe_index", JoinKind.LeftOuter),
#"Erweiterte tblListe_index" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "tblListe_index", {"Original Title", "Release Date", "Index"}, {"Original Title", "Release Date", "Index"}),
#"Gefilterte Zeilen" = Table.SelectRows(#"Erweiterte tblListe_index", each ([Release Date] > null)),
#"Zusammengeführte Abfragen1" = Table.NestedJoin(#"Gefilterte Zeilen", {"Leute"}, Leute1, {"Const"}, "Leute1", JoinKind.LeftOuter),
#"Erweiterte Leute1" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen1", "Leute1", {"Name", "Birth Date"}, {"Name", "Birth Date"}),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Erweiterte Leute1", "TageDelta", each Number.From([Release Date])-Number.From([Birth Date])),
#"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "JahreDelta_brutto", each Date.Year([Release Date])-Date.Year([Birth Date])),
#"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte1", "RestTage_brutto", each Number.From([Release Date])-Number.From(Date.AddYears([Birth Date],[JahreDelta_brutto]))),
#"Hinzugefügte benutzerdefinierte Spalte3" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte2", "JahreDelta", each if [RestTage_brutto] 0 then [JahreDelta_brutto] - 1 else [JahreDelta_brutto]),
#"Hinzugefügte benutzerdefinierte Spalte4" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte3", "RestTage", each Number.From([Release Date])-Number.From(Date.AddYears([Birth Date],[JahreDelta]))),
#"Hinzugefügte benutzerdefinierte Spalte5" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte4", "MRS", each "MRS " & Text.PadStart (Text.From([TageDelta]), 5, "0") & " " & [Original Title] & " (" & Date.ToText([Release Date]) & ") - " & [Name] & " (" & Date.ToText([Birth Date]) & ") " & Text.From([JahreDelta]) & "-" & Text.From([RestTage])),
#"Andere entfernte Spalten" = Table.SelectColumns(#"Hinzugefügte benutzerdefinierte Spalte5",{"Film", "Original Title", "Release Date", "Leute", "Name", "Birth Date", "Index", "JahreDelta", "RestTage", "TageDelta", "MRS"}),
#"Sortierte Zeilen" = Table.Sort(#"Andere entfernte Spalten",{{"Release Date", Order.Ascending}})
in
#"Sortierte Zeilen"
Ob besser als eine Formellösung muss jeder für sich entscheiden. Ich mag die Stringenz von PQ, weil es zu einer Logik der gut organisierten Daten zwingt.
Das keine Formel in Arbeitsblatt vorhanden ist, vermeidet Flüchtigkeitsfehler.
Auch die Quellen "Filme1.csv" und "Leute1.csv" müssen nicht in Excel geladen werden. Die Datei wird leichter.
VG
Yal