How to split grouped addresses into individual rows and reorder by a specific column in Power Query?

[luzrueda]

Hallo!

Ich habe eine Tabelle, in der mehrere Hausnummern in einer einzigen Spalte (durch Kommas getrennt) für jede Adresse gruppiert sind, und diese Gruppen wiederholen sich über mehrere Zeilen. Die Tabelle enthält mehrere Spalten, in denen die Daten unverändert bleiben müssen, mit Ausnahme der "Hausnummern", die individuell auf der Grundlage der Werte in der Spalte "Energie" neu angeordnet werden müssen.

---

Beispielszenario:

Für die Adresse "Am Dünneckenberg" erscheint in 7 Zeilen die Gruppe der Hausnummern 1,2,6,10,14. Die gewünschte Transformation sollte:

1. Teilen Sie die Hausnummern in einzelne Zeilen auf.
2. Ordnen Sie die Hausnummern basierend auf den höchsten Werten in der Spalte Energie neu an, wobei die Reihenfolge beibehalten wird.
   • Beispielhafte Aufgabe:
     • Hausnummer 1 → 30.4
     • Hausnummer 2 → 27
     • Hausnummer 6 → 27
     • Hausnummer 10 → 21
     • Hausnummer 14 → 20
3. Nachdem Sie die Hausnummern für die ersten Zeilen zugewiesen haben, wiederholen Sie diese in der gleichen Reihenfolge für die nachfolgenden Zeilen, bis alle Zeilen vollständig zugewiesen sind.
4. Behalten Sie alle anderen Spalten in der Tabelle genau so bei, wie sie sind, und stellen Sie sicher, dass sie an den transformierten Zeilen ausgerichtet sind.

---

Wichtige Anforderungen:

1. Teilen Sie die gruppierte Spalte "Hausnummern" in einzelne Zeilen auf.
2. Ordnen Sie die Hausnummern basierend auf den höchsten Werten in der Spalte "Energie" neu an.
3. Wiederholen Sie die Hausnummern bei Bedarf in der gleichen Reihenfolge für weitere Zeilen.
4. Lassen Sie alle anderen Spalten unverändert und richten Sie sie an den entsprechenden Zeilen aus.

---

Alle Vorschläge, wie diese Transformation in Power Query erreicht werden kann, wären sehr willkommen. Wenn möglich, wären Beispiele oder M-Code-Schnipsel hilfreich. Vielen Dank! Hier finden Sie ein Beispiel, wie die Tabelle aussieht (Original) und wie sie aussehen soll (gewünschte Tabelle)

Original

Adresse	Adresse Nummer	Code	Kunst	Schwelle	Energie
Am Dünneckenberg	1,2,6,10,14	1	HK	18	20
Am Dünneckenberg	1,2,6,10,14	2	HK	26	27
Am Dünneckenberg	1,2,6,10,14	3	OKAY	10	11
Am Dünneckenberg	1,2,6,10,14	4	HK	26	21
Am Dünneckenberg	1,2,6,10,14	5	HK	26	30,4
Am Dünneckenberg	1,2,6,10,14	6	HK	26	27
Am Dünneckenberg	1,2,6,10,14	7	LOS	10	14
Am Mooskamp	1,2,3	8	UW	32	20
Am Mooskamp	1,2,3	9	UW	32	18
Am Mooskamp	1,2,3	10	KO	10	8
Am Mooskamp	1,2,3	11	HK	32	23,6
Am Mooskamp	1,2,3	12	KO	10	8
Am Mooskamp	1,2,3	13	HK	32	29,2
Am Mooskamp	1,2,3	14	KO	10	5,5
Am Mooskamp	4,5,6	15	KW	32	18
Am Mooskamp	4,5,6	16	KW	32	18
Am Mooskamp	4,5,6	17	KW	32	24
Am Mooskamp	4,5,6	18	KO	10	9
Am Mooskamp	4,5,6	19	HK	32	23,6
Am Mooskamp	4,5,6	20	KO	10	5,9
Am Mooskamp	8,9,10	21	HK	32	14,3
Am Mooskamp	8,9,10	22	UW	32	18
Am Mooskamp	8,9,10	23	HK	32	20,4
Am Mooskamp	8,9,10	24	HK	32	19
Am Mooskamp	8,9,10	25	HK	32	15,8
Am Mooskamp	8,9,10	26	HK	32	15,8
Am Mooskamp	8,9,10	27	HK	32	15,8
Am Rappelsberg	1,2,3	28	HK	32	29
Am Rappelsberg	1,2,3	29	HK	26	20
Am Rappelsberg	1,2,3	30	HK	32	23

Gewünschte Tabelle

Adresse	Adresse Nummer	Code	Kunst	Schwelle	Energie
Am Dünneckenberg	14	1	HK	18	20
Am Dünneckenberg	2	2	HK	26	27
Am Dünneckenberg	2	3	OKAY	10	11
Am Dünneckenberg	10	4	HK	26	21
Am Dünneckenberg	1	5	HK	26	30,4
Am Dünneckenberg	6	6	HK	26	27
Am Dünneckenberg	1	7	LOS	10	14
Am Mooskamp	3	8	UW	32	20
Am Mooskamp	1	9	UW	32	18
Am Mooskamp	2	10	KO	10	8
Am Mooskamp	2	11	HK	32	23,6
Am Mooskamp	3	12	KO	10	8
Am Mooskamp	1	13	HK	32	29,2
Am Mooskamp	1	14	KO	10	5,5
Am Mooskamp	6	15	KW	32	18
Am Mooskamp	4	16	KW	32	18
Am Mooskamp	4	17	KW	32	24
Am Mooskamp	5	18	KO	10	9
Am Mooskamp	5	19	HK	32	23,6
Am Mooskamp	6	20	KO	10	5,9
Am Mooskamp	8	21	HK	32	14,3
Am Mooskamp	10	22	UW	32	18
Am Mooskamp	8	23	HK	32	20,4
Am Mooskamp	9	24	HK	32	19
Am Mooskamp	8	25	HK	32	15,8
Am Mooskamp	9	26	HK	32	15,8
Am Mooskamp	10	27	HK	32	15,8
Am Rappelsberg	1	28	HK	32	29
Am Rappelsberg	3	29	HK	26	20
Am Rappelsberg	2	30	HK	32	23

[dufoq3]

Hi @luzrueda, another apporach:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZPPSgMxEMZfRXIOIZn82c2xoFBZpFAVKWUPVRYPtdvFvp43X8yd6sZOSGPqISEhv29mMl+yXrPZ7ur686Pvu5dt1z9376+MM8WBO64kVwZ345g3uKjHCSRreYkMJhk4nKpCmR7Hopmt8EDipAqFhuYrlVki01KYQqH73/2qcTzMb8L1Qrq7/f6w3eyGHwX2Afu9WjwusTAgvU/APoJHt87Dx9y399Mqiwb/v2vQwuVoKA+saWAvIEcbEtgKm4ANtxzdUOhq85RtRWDdBWxFWEiZF9iaFOxzqC9p8USDjDqRCl1zz4/HQO1TRugsDiXP6BePPDz5Pknc0GLylVsKW5EvxV2GV2fx5WYYurfDyffFW0IdPdc/8OApOPJ107iW0RNgbfsF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t, #"address number" = _t, Code = _t, Art = _t, threshold = _t, Energy = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Energy", type number}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"Address"}, {{"All", each [
            // aDetail = GroupedRows{0}[All],
            aDetail = _,
            aAddedIndex = Table.AddIndexColumn(aDetail, "Index", 0, 1, Int64.Type),
            aHouseNumbers = List.Buffer(List.Transform(Text.Split(aAddedIndex{0}[address number], ","), Number.From)),
            aSortedRows = Table.Sort(aAddedIndex,{{"Energy", Order.Descending}}),
            aAddedIndex2 = Table.AddIndexColumn(aSortedRows, "Index2", 0, 1, Int64.Type),
            aIntegerDivide = Table.TransformColumns(aAddedIndex2, {{"Index2", each Number.IntegerDivide(_, List.Count(aHouseNumbers)), Int64.Type}}),
            aGroupedRows = Table.Group(aIntegerDivide, {"Index2"}, {{"All", each _, type table}, {"T", each 
                Table.FromColumns(Table.ToColumns(_) & {List.FirstN(aHouseNumbers, Table.RowCount(_))}, Value.Type(_ & #table(type table[HouseNumber=number], {}))), type table}}),
            aCombined = Table.Combine(aGroupedRows[T]),
            aSortedRows2 = Table.Sort(aCombined,{{"Index", Order.Ascending}}),
            aRemovedColumns = Table.RemoveColumns(aSortedRows2,{"address number"}),
            aRenamedColumns = Table.RenameColumns(aRemovedColumns,{{"HouseNumber", "address number"}}),
            aRemovedOtherColumns = Table.SelectColumns(aRenamedColumns, Table.ColumnNames(aDetail))
          ][aRemovedOtherColumns], type table}}, 0),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

[luzrueda]

@jgeddes thank you so much for such great solution!

[lbendlin]

Ja, sind wir denn hier bei "Wuensch Dir Was" ?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZNNTsMwEIWvgryehT3+SbysxAIpVJFACKEqi4IiFqVp1F6PHRfDU4jpRK7rsrCVyN8bj9+zVyux2N7cfn0OQ/+26YfXfv8uQChAcKAkKEN/Ydw19FGHCaXooESGkwwdTVWhTIfRNosXWpA0qUKh4fuVyiyTaQmmUOj+d74qjPv2MR4vbrfc7Q6b9Xb8VZAP5PfTM7WFzPkE6hkakjqPHvdt2ukri8bsfzrQ4HI0lhfWvLAHzNGGFbZgE7ABC5SEokSbvBWRdVewFWMxFVxka9awz6G+xOKJRjlzIlW6Bg/HZeTxKRPczOF4+RL9wbMETx5OEje8lXzflsMW8q246/DqLP6wHsf+43DycOmUWM8u6wU8JoqOPds0ruXsAoiu+wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Adresse = _t, #"Adresse Nummer" = _t, Code = _t, Kunst = _t, Schwelle = _t, Energie = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Energie", type number}},"de"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Adresse","Adresse Nummer"}, {{"Rows", each Table.AddIndexColumn(Table.Sort(_,{"Energie",Order.Descending}), "Index", 0, 1, Int64.Type), type table [ Code=nullable text, Kunst=nullable text, Schwelle=nullable text, Energie=nullable text, Index=Int64.Type]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AN", each List.FirstN(List.Repeat(Text.Split([Adresse Nummer],","),Table.RowCount([Rows])),Table.RowCount([Rows]))),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Code", "Kunst", "Schwelle", "Energie", "Index"}, {"Code", "Kunst", "Schwelle", "Energie", "Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Rows",each [Adresse Nummer],each [AN]{[Index]},Replacer.ReplaceValue,{"Adresse Nummer"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Adresse", "Adresse Nummer", "Code", "Kunst", "Schwelle", "Energie"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Code", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Code", Order.Ascending}})
in
    #"Sorted Rows"

[jgeddes · Accepted solution]

Hier ist ein Beispielcode, der das gewünschte Ergebnis liefert.
Fügen Sie diesen Code in den erweiterten Editor einer leeren Abfrage ein.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZPPSgMxEMZfRXIOIZn82c2xoFBZpFAVKWUPVRYPtdvFvp43X8yd6sZOSGPqISEhv29mMl+yXrPZ7ur686Pvu5dt1z9376+MM8WBO64kVwZ345g3uKjHCSRreYkMJhk4nKpCmR7Hopmt8EDipAqFhuYrlVki01KYQqH73/2qcTzMb8L1Qrq7/f6w3eyGHwX2Afu9WjwusTAgvU/APoJHt87Dx9y399Mqiwb/v2vQwuVoKA+saWAvIEcbEtgKm4ANtxzdUOhq85RtRWDdBWxFWEiZF9iaFOxzqC9p8USDjDqRCl1zz4/HQO1TRugsDiXP6BePPDz5Pknc0GLylVsKW5EvxV2GV2fx5WYYurfDyffFW0IdPdc/8OApOPJ107iW0RNgbfsF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t, #"address number" = _t, Code = _t, Art = _t, threshold = _t, Energy = _t]),
    change_energy_type = Table.TransformColumnTypes(Source,{{"Energy", Int64.Type}}),
    group_rows = Table.Group(change_energy_type, {"Address", "address number"}, {{"_allRows", each _, type table [Address=nullable text, address number=nullable text, Code=nullable number, Art=nullable text, threshold=nullable number, Energy=nullable number]}}),
    sort_nested = Table.TransformColumns(group_rows, {{"_allRows", each Table.Buffer(Table.Sort(_, {"Energy", Order.Descending}))}}),
    split_tables = Table.AddColumn(sort_nested, "splitTables", each Table.Split([_allRows], List.Count(Text.Split([address number], ",")))),
    remove_unsplit_tables = Table.RemoveColumns(split_tables,{"_allRows"}),
    expand_split_tables = Table.ExpandListColumn(remove_unsplit_tables, "splitTables"),
    add_nested_index = Table.TransformColumns(expand_split_tables, {{"splitTables", each Table.AddIndexColumn(_, "Index", 0, 1)}}),
    extract_address_number = Table.TransformColumns(add_nested_index, {{"splitTables", each Table.AddColumn(_, "extractedAddress", each let numberList = Text.Split([address number], ",") in numberList{Number.Mod([Index], List.Count(numberList))})}}),
    expand_nested = Table.ExpandTableColumn(extract_address_number, "splitTables", {"Code", "Art", "threshold", "Energy", "extractedAddress"}, {"Code", "Art", "threshold", "Energy", "extractedAddress"}),
    remove_old_address_number = Table.RemoveColumns(expand_nested,{"address number"}),
    rename_address_number = Table.RenameColumns(remove_old_address_number,{{"extractedAddress", "address number"}}),
    change_types = Table.TransformColumnTypes(rename_address_number,{{"Code", Int64.Type}, {"Art", type text}, {"threshold", type number}, {"Energy", type number}, {"address number", Int64.Type}}),
    sort_by_code = Table.Buffer(Table.Sort(change_types,{{"Code", Order.Ascending}})),
    reorder_columns = Table.ReorderColumns(sort_by_code,{"Address", "address number", "Code", "Art", "threshold", "Energy"})
in
    reorder_columns