AW: Power Query mehrere Ergebnisspalten
24.04.2023 14:44:59
Yal
Hallo zusammen,
wenn der Anzahl von Spalten fest ist (wovon ich in einer Sharepointliste ausgehen kann), dann ganz klassich: Spalten mit Semikolon kombinieren, entpivotieren, nach Semikolon trennen, gruppieren.
let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Time of Reason 1", Int64.Type}, {"Reason of Reason 1", type text}, {"Time of Reason 2", Int64.Type}, {"Reason of Reason 2", type text}, {"Time of Reason 3", Int64.Type}, {"Reason of Reason 3", type text}, {"Time of Reason 4", Int64.Type}, {"Reason of Reason 4", type text}}),
ZusSpa = Table.CombineColumns(Table.TransformColumnTypes(#"Geänderter Typ", {{"Time of Reason 1", type text}}, "de-DE"),{"Time of Reason 1", "Reason of Reason 1"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Zusammengeführt"),
ZusSpa1 = Table.CombineColumns(Table.TransformColumnTypes(ZusSpa, {{"Time of Reason 2", type text}}, "de-DE"),{"Time of Reason 2", "Reason of Reason 2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Zusammengeführt.1"),
ZusSpa2 = Table.CombineColumns(Table.TransformColumnTypes(ZusSpa1, {{"Time of Reason 3", type text}}, "de-DE"),{"Time of Reason 3", "Reason of Reason 3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Zusammengeführt.2"),
ZusSpa3 = Table.CombineColumns(Table.TransformColumnTypes(ZusSpa2, {{"Time of Reason 4", type text}}, "de-DE"),{"Time of Reason 4", "Reason of Reason 4"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Zusammengeführt.3"),
EntpivSp = Table.UnpivotOtherColumns(ZusSpa3, {}, "Attribut", "Wert"),
#"Spalte nach Trennzeichen teilen" = Table.SplitColumn(EntpivSp, "Wert", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Wert.1", "Wert"}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Spalte nach Trennzeichen teilen",{{"Wert.1", Int64.Type}, {"Wert", type text}}),
#"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ1", {"Wert.2"}, {{"Summe", each List.Sum([Wert.1]), type number}})
in
#"Gruppierte Zeilen"
VG
Yal