form from/to areas of single lines works very slow

P

pinarello

New Member
Joined
Jun 1, 2019
Messages
42
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

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


zipFederal stateCounty / CityType
4880SachsenNordsachsenCounty
4886SachsenNordsachsenCounty
4889SachsenNordsachsenCounty
4895BrandenburgElbe-ElsterCounty
4910BrandenburgElbe-ElsterCounty
4916BrandenburgElbe-ElsterCounty
4924BrandenburgElbe-ElsterCounty
4928BrandenburgElbe-ElsterCounty
4931BrandenburgElbe-ElsterCounty
4932BrandenburgElbe-ElsterCounty
4934BrandenburgElbe-ElsterCounty
4936BrandenburgElbe-ElsterCounty
4938BrandenburgElbe-ElsterCounty
6108Sachsen-AnhaltHalle SaaleTown
6110Sachsen-AnhaltHalle SaaleTown
6112Sachsen-AnhaltHalle SaaleTown
6114Sachsen-AnhaltHalle SaaleTown
6116Sachsen-AnhaltHalle SaaleTown
6118Sachsen-AnhaltHalle SaaleTown
6120Sachsen-AnhaltHalle SaaleTown
6122Sachsen-AnhaltHalle SaaleTown
6124Sachsen-AnhaltHalle SaaleTown
6126Sachsen-AnhaltHalle SaaleTown
6128Sachsen-AnhaltHalle SaaleTown
6130Sachsen-AnhaltHalle SaaleTown
6132Sachsen-AnhaltHalle SaaleTown
6179Sachsen-AnhaltSaalekreisCounty
6184Sachsen-AnhaltSaalekreisCounty
6188Sachsen-AnhaltSaalekreisCounty
6193Sachsen-AnhaltSaalekreisCounty
6198Sachsen-AnhaltSaalekreisCounty
6217Sachsen-AnhaltSaalekreisCounty
6231Sachsen-AnhaltSaalekreisCounty
6237Sachsen-AnhaltSaalekreisCounty
6242Sachsen-AnhaltSaalekreisCounty
6246Sachsen-AnhaltSaalekreisCounty
6249Sachsen-AnhaltSaalekreisCounty
6255Sachsen-AnhaltSaalekreisCounty
6258Sachsen-AnhaltSaalekreisCounty
6259Sachsen-AnhaltSaalekreisCounty
6268Sachsen-AnhaltSaalekreisCounty
6279Sachsen-AnhaltSaalekreisCounty
6295Sachsen-AnhaltMansfeld-SüdharzCounty
6308Sachsen-AnhaltMansfeld-SüdharzCounty
6311Sachsen-AnhaltMansfeld-SüdharzCounty
6313Sachsen-AnhaltMansfeld-SüdharzCounty
6317Sachsen-AnhaltMansfeld-SüdharzCounty
6333Sachsen-AnhaltMansfeld-SüdharzCounty
6343Sachsen-AnhaltMansfeld-SüdharzCounty
6347Sachsen-AnhaltMansfeld-SüdharzCounty
6366Sachsen-AnhaltAnhalt-BitterfeldCounty
6369Sachsen-AnhaltAnhalt-BitterfeldCounty
6385Sachsen-AnhaltAnhalt-BitterfeldCounty
6386Sachsen-AnhaltAnhalt-BitterfeldCounty
6388Sachsen-AnhaltAnhalt-BitterfeldCounty
6406Sachsen-AnhaltSalzlandkreisCounty
6408Sachsen-AnhaltSalzlandkreisCounty
6420Sachsen-AnhaltSaalekreisCounty
6425Sachsen-AnhaltSalzlandkreisCounty
6429Sachsen-AnhaltSalzlandkreisCounty
6449Sachsen-AnhaltSalzlandkreisCounty
6456Sachsen-AnhaltMansfeld-SüdharzCounty
6458Sachsen-AnhaltHarzCounty
6463Sachsen-AnhaltHarzCounty
6464Sachsen-AnhaltMansfeld-SüdharzCounty
6466Sachsen-AnhaltMansfeld-SüdharzCounty
6467Sachsen-AnhaltMansfeld-SüdharzCounty
6469Sachsen-AnhaltMansfeld-SüdharzCounty
6484Sachsen-AnhaltHarzCounty
6485Sachsen-AnhaltHarzCounty
6493Sachsen-AnhaltHarzCounty
6502Sachsen-AnhaltHarzCounty
6526Sachsen-AnhaltMansfeld-SüdharzCounty
6528Sachsen-AnhaltMansfeld-SüdharzCounty
6536Sachsen-AnhaltMansfeld-SüdharzCounty
6537Sachsen-AnhaltMansfeld-SüdharzCounty
6542Sachsen-AnhaltMansfeld-SüdharzCounty
6543Sachsen-AnhaltMansfeld-SüdharzCounty
6556ThüringenKyffhäuserkreisCounty
6567ThüringenKyffhäuserkreisCounty
6571ThüringenKyffhäuserkreisCounty
6577ThüringenKyffhäuserkreisCounty
6578ThüringenKyffhäuserkreisCounty
6618Sachsen-AnhaltBurgenlandkreisCounty
6628Sachsen-AnhaltBurgenlandkreisCounty
6632Sachsen-AnhaltBurgenlandkreisCounty
6636Sachsen-AnhaltBurgenlandkreisCounty
6638Sachsen-AnhaltBurgenlandkreisCounty
6642Sachsen-AnhaltBurgenlandkreisCounty
6647Sachsen-AnhaltBurgenlandkreisCounty
6648Sachsen-AnhaltBurgenlandkreisCounty
6667Sachsen-AnhaltBurgenlandkreisCounty
6679Sachsen-AnhaltBurgenlandkreisCounty
6682Sachsen-AnhaltBurgenlandkreisCounty
6686Sachsen-AnhaltBurgenlandkreisCounty
6688Sachsen-AnhaltBurgenlandkreisCounty
6711Sachsen-AnhaltBurgenlandkreisCounty
6712Sachsen-AnhaltBurgenlandkreisCounty
6721Sachsen-AnhaltBurgenlandkreisCounty
6722Sachsen-AnhaltBurgenlandkreisCounty
6729Sachsen-AnhaltBurgenlandkreisCounty
6749Sachsen-AnhaltAnhalt-BitterfeldCounty
6766Sachsen-AnhaltAnhalt-BitterfeldCounty
6772Sachsen-AnhaltWittenbergCounty
6773Sachsen-AnhaltAnhalt-BitterfeldCounty

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>





<colgroup><col></colgroup><tbody>
</tbody>






Translated from German to English with www.DeepL.com
 
Sort by date Sort by votes
is that what you want?
(part of the table)
Federal stateCounty_CityTypeMinMax
HessenTown ATown
600000​
600010​
HessenTown BTown
600200​
600210​
HessenTown ATown
600500​
600500​
HessenTown BTown
600600​
600600​
SachsenLeipzigCounty
4316​
4316​
SachsenLeipzigTown
4317​
4319​
SachsenLeipzigCounty
4328​
4328​
SachsenLeipzigTown
4329​
4349​
SachsenLeipzigCounty
4356​
4356​
SachsenLeipzigTown
4357​
4357​
SachsenLeipzigCounty
4416​
4420​
SachsenNordsachsenCounty
4425​
4435​
SachsenLeipzigCounty
4442​
4463​
SachsenNordsachsenCounty
4509​
4519​
SachsenLeipzigCounty
4523​
4575​
ThüringenAltenburger LandCounty
4600​
4639​
SachsenLeipzigCounty
4643​
4687​
SachsenMittelsachsenCounty
4703​
4749​
SachsenNordsachsenCounty
4758​
4779​
SachsenLeipzigCounty
4808​
4828​
SachsenNordsachsenCounty
4838​
4889​
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
so here is M-code for table above (source table: 2000 rows)

Code: 
// 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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Syntax
Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top