form from/to areas of single lines works very slow

[pinarello]

Hello,


actually I just wanted to find out how to create intervals from a list of single values with Power Query.


For example, a sales representative can easily see which postal code areas he is responsible for.


As an exercise object I found a list containing the postal codes of counties and cities in Germany. In this case, it is not important whether the list is complete, since it is only an exercise.


In the first version I am I am in such a way vorgegengen, as I would have realized it also with Excel formulas. I also came to a useful result, but with catastrophic response times.


I suspected that this could be due to the fact that I use both backward and forward indexing in the query and also address several fields using indexing.


So I changed the query so that now only a backward indexing is necessary and to avoid the multiple use, I concatenated the relevant fields. I also deleted all necessary auxiliary columns as fast as possible.


But no matter what I do, the query always calculates forever. In the total list it's about forming the intervals from about 8200 lines, but even if I only run the query for 105 lines and 19 from7to ranges are formed, it takes about 12 seconds on my PC. My company laptop even needs about 20 seconds.


Maybe someone has an idea what could be the cause and how the problem of the long runtime can be solved.


See here the code and examples of input

[TABLE="width: 1093"]
<colgroup><col></colgroup><tbody>[TR]
[TD]let[/TD]
[/TR]
[TR]
[TD] Source = Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content],[/TD]
[/TR]
[TR]
[TD] Userdefined_Col = Table.AddColumn(Quelle, "County/City - Type", each [#"County / City"] & "@" & [Type]),[/TD]
[/TR]
[TR]
[TD] Removed_col = Table.RemoveColumns(Userdefined_Col,{"County / City", "Type"}),[/TD]
[/TR]
[TR]
[TD] sorted_lines = Table.Sort(Removed_col,{{"zip", Order.Ascending}, {"County/City - Type", Order.Ascending}}),[/TD]
[/TR]
[TR]
[TD] Index_Col = Table.AddIndexColumn(sorted_lines, "Index", 0, 1),[/TD]
[/TR]
[TR]
[TD] userdefined_col = Table.AddColumn(Index_Col, "from", each if [Index] = 0 [/TD]
[/TR]
[TR]
[TD] or [#"County/City - Type"] <> Index_Col[#"County/City - Type"]{[Index]-1} [/TD]
[/TR]
[TR]
[TD] then [zip][/TD]
[/TR]
[TR]
[TD] else null),[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] fill_down = Table.FillDown(userdefined_col,{"from"}),[/TD]
[/TR]
[TR]
[TD] Removed_col2 = Table.RemoveColumns(fill_down,{"Index"}),[/TD]
[/TR]
[TR]
[TD] sorted_lines2 = Table.Sort(Removed_col2,{{"zip", Order.Descending}, {"County/City - Type", Order.Descending}}),[/TD]
[/TR]
[TR]
[TD] Index_Col2 = Table.AddIndexColumn(sorted_lines2, "Index", 0, 1),[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] Userdefined_col2 = Table.AddColumn(Index_Col2, "to", each if [Index] = 0[/TD]
[/TR]
[TR]
[TD] or Index_Col2[#"from"]{[Index]-1} <> [#"from"] [/TD]
[/TR]
[TR]
[TD] then [zip] [/TD]
[/TR]
[TR]
[TD] else null),[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] filtered_lines = Table.SelectRows(Userdefined_col2, each ([#"to"] <> null)),[/TD]
[/TR]
[TR]
[TD] Removed_col3 = Table.RemoveColumns(filtered_lines,{"Index", "zip"}),[/TD]
[/TR]
[TR]
[TD] Changed_type = Table.TransformColumnTypes(Removed_col3,{{"from", type text}, {"to", type text}}),[/TD]
[/TR]
[TR]
[TD] Userdefined_col3 = Table.AddColumn(Changed_type,[/TD]
[/TR]
[TR]
[TD] "from_2", each "0" & [#"from"]),[/TD]
[/TR]
[TR]
[TD] Userdefined_col4 = Table.AddColumn(Userdefined_col3, "to_2", each "0" & [#"to"]),[/TD]
[/TR]
[TR]
[TD] Changed_type2 = Table.TransformColumnTypes(Userdefined_col4,{{"from_2", type text}, {"to_2", type text}}),[/TD]
[/TR]
[TR]
[TD] Split_col_by_pos = Table.SplitColumn(Changed_type2, "from_2", Splitter.SplitTextByPositions({0, 5}, true), {"del_1", "zip-from"}),[/TD]
[/TR]
[TR]
[TD] Split_col_by_pos2 = Table.SplitColumn(Split_col_by_pos, "to_2", Splitter.SplitTextByPositions({0, 5}, true), {"del_2", "zip-to"}),[/TD]
[/TR]
[TR]
[TD] Removed_col4 = Table.RemoveColumns(Split_col_by_pos2,{"from", "to", "del_1", "del_2"}),[/TD]
[/TR]
[TR]
[TD] Split_col_by_sign = Table.SplitColumn(Removed_col4, "County/City - Type", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"County/City", "Type"}),[/TD]
[/TR]
[TR]
[TD] sorted_lines3 = Table.Sort(Split_col_by_sign,{{"Federal state", Order.Ascending}, {"County/City", Order.Ascending}, {"Type", Order.Ascending}, {"zip-from", Order.Ascending}})[/TD]
[/TR]
[TR]
[TD]in[/TD]
[/TR]
[TR]
[TD] sorted_lines3


[TABLE="width: 471"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]zip[/TD]
[TD]Federal state[/TD]
[TD]County / City[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD="align: right"]4880[/TD]
[TD]Sachsen[/TD]
[TD]Nordsachsen[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4886[/TD]
[TD]Sachsen[/TD]
[TD]Nordsachsen[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4889[/TD]
[TD]Sachsen[/TD]
[TD]Nordsachsen[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4895[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4910[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4916[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4924[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4928[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4931[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4932[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4934[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4936[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]4938[/TD]
[TD]Brandenburg[/TD]
[TD]Elbe-Elster[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6108[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6110[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6112[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6114[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6116[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6118[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6120[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6122[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6124[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6126[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6128[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6130[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6132[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Halle Saale[/TD]
[TD]Town[/TD]
[/TR]
[TR]
[TD="align: right"]6179[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6184[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6188[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6193[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6198[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6217[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6231[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6237[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6242[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6246[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6249[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6255[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6258[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6259[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6268[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6279[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6295[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6308[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6311[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6313[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6317[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6333[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6343[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6347[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6366[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Anhalt-Bitterfeld[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6369[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Anhalt-Bitterfeld[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6385[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Anhalt-Bitterfeld[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6386[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Anhalt-Bitterfeld[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6388[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Anhalt-Bitterfeld[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6406[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Salzlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6408[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Salzlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6420[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Saalekreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6425[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Salzlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6429[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Salzlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6449[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Salzlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6456[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6458[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Harz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6463[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Harz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6464[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6466[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6467[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6469[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6484[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Harz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6485[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Harz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6493[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Harz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6502[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Harz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6526[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6528[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6536[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6537[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6542[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6543[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Mansfeld-Südharz[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6556[/TD]
[TD]Thüringen[/TD]
[TD]Kyffhäuserkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6567[/TD]
[TD]Thüringen[/TD]
[TD]Kyffhäuserkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6571[/TD]
[TD]Thüringen[/TD]
[TD]Kyffhäuserkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6577[/TD]
[TD]Thüringen[/TD]
[TD]Kyffhäuserkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6578[/TD]
[TD]Thüringen[/TD]
[TD]Kyffhäuserkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6618[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6628[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6632[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6636[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6638[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6642[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6647[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6648[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6667[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6679[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6682[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6686[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6688[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6711[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6712[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6721[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6722[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6729[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Burgenlandkreis[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6749[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Anhalt-Bitterfeld[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6766[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Anhalt-Bitterfeld[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6772[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Wittenberg[/TD]
[TD]County[/TD]
[/TR]
[TR]
[TD="align: right"]6773[/TD]
[TD]Sachsen-Anhalt[/TD]
[TD]Anhalt-Bitterfeld[/TD]
[TD]County[/TD]
[/TR]
</tbody>[/TABLE]




[/TD]
[/TR]
</tbody>[/TABLE]






Translated from German to English with www.DeepL.com

 

[sandy666]

is that what you want?
(part of the table)
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Federal state[/td][td=bgcolor:#70AD47]County_City[/td][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Min[/td][td=bgcolor:#70AD47]Max[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Hessen[/td][td=bgcolor:#E2EFDA]Town A[/td][td=bgcolor:#E2EFDA]Town[/td][td=bgcolor:#E2EFDA]

600000​

[/td][td=bgcolor:#E2EFDA]

600010​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Hessen[/td][td]Town B[/td][td]Town[/td][td]

600200​

[/td][td]

600210​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Hessen[/td][td=bgcolor:#E2EFDA]Town A[/td][td=bgcolor:#E2EFDA]Town[/td][td=bgcolor:#E2EFDA]

600500​

[/td][td=bgcolor:#E2EFDA]

600500​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Hessen[/td][td]Town B[/td][td]Town[/td][td]

600600​

[/td][td]

600600​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Leipzig[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4316​

[/td][td=bgcolor:#E2EFDA]

4316​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sachsen[/td][td]Leipzig[/td][td]Town[/td][td]

4317​

[/td][td]

4319​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Leipzig[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4328​

[/td][td=bgcolor:#E2EFDA]

4328​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sachsen[/td][td]Leipzig[/td][td]Town[/td][td]

4329​

[/td][td]

4349​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Leipzig[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4356​

[/td][td=bgcolor:#E2EFDA]

4356​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sachsen[/td][td]Leipzig[/td][td]Town[/td][td]

4357​

[/td][td]

4357​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Leipzig[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4416​

[/td][td=bgcolor:#E2EFDA]

4420​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sachsen[/td][td]Nordsachsen[/td][td]County[/td][td]

4425​

[/td][td]

4435​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Leipzig[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4442​

[/td][td=bgcolor:#E2EFDA]

4463​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sachsen[/td][td]Nordsachsen[/td][td]County[/td][td]

4509​

[/td][td]

4519​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Leipzig[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4523​

[/td][td=bgcolor:#E2EFDA]

4575​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Thüringen[/td][td]Altenburger Land[/td][td]County[/td][td]

4600​

[/td][td]

4639​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Leipzig[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4643​

[/td][td=bgcolor:#E2EFDA]

4687​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sachsen[/td][td]Mittelsachsen[/td][td]County[/td][td]

4703​

[/td][td]

4749​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Nordsachsen[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4758​

[/td][td=bgcolor:#E2EFDA]

4779​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sachsen[/td][td]Leipzig[/td][td]County[/td][td]

4808​

[/td][td]

4828​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sachsen[/td][td=bgcolor:#E2EFDA]Nordsachsen[/td][td=bgcolor:#E2EFDA]County[/td][td=bgcolor:#E2EFDA]

4838​

[/td][td=bgcolor:#E2EFDA]

4889​

[/td][/tr]
[/table]

 

[pinarello]

Yeah, that looks good!

 

[sandy666]

so here is M-code for table above (source table: 2000 rows)

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Group = Table.Group(Source, {"Federal state", "County_City", "Type"}, {{"Min", each List.Min([zip]), type number}, {"Max", each List.Max([zip]), type number}},GroupKind.Local)
in
    Group

it works for 8000 rows also, I didn't see any delay or time difference between 2k rows and 8k rows

 

[pinarello]

In the query "My code-2" I included the 2nd line in the command "Table.Buffer". This results in a much shorter runtime of about 2 minutes and 30 seconds when I run the query for 8000 lines.


This is already a big step forward, but still much too slow.


Here again the link to my workbook

 

[sandy666]

post#14 has nothing to do with post#13

 

[pinarello]

Wooooooooooowwwwwwwwwww, this is as fast as I imagined.


Only by using the additional parameter "GroupKind.Local", which is however not available in the editor of the function "Grouping", is grouped correctly now, without needing an index that does everything slowly.


And the code of the query is now only a fraction of the first attempts.


However, in order to always guarantee the correct results, the table should be sorted after the import.


Yes, when I wrote #14 , I hadn't seen your #13 yet

 

[sandy666]

so sort result as you wish, any problem?

 

[pinarello]

The sorting was only a remark, because a wrong sorting influences the group formation.


Much more interesting is the question of how you knew that the parameter "GroupKind.Local" exists

 

[sandy666]

see here (be patient ): M reference

 

[sandy666]

Syntax

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table

Arguments

• table - The Table to modify.
• key - The key columns.
• aggregatedColumns - Specifies the names and function return types.
• optional groupKind - The type of group. Possible values are GroupKind.Global (default) and GroupKind.Local.
• optional comparer - An optional argument that determines equality between group keys.

Remarks

• The type of the resulting table is computed by preserving the columns that make up the group key, including their types, and appending new columns with names and types according to the names and function return types specified in the aggregatedColumns argument.
• For each group, a record is constructed containing the key columns, including their values, along with any aggregated columns from the aggregatedColumns argument. A table of these group results is returned.
• A group can be local (GroupKind.Local) or global (GroupKind.Global). A local group is formed from a consecutive sequence of rows from an input table with the same key value. A global group is formed from all rows in an input table with the same key value. Multiple local groups may be produced with the same key value but only a single global group is produced for a given key value.
• The default groupKind value is GroupKind.Global.
• The Table.Group function may also be used to nest the rows in a group.