Forum Discussion
Local Grouping / Index Issues
Hey guys, I have out-of-order source data that I'm performing a locally grouped index on. My index persists through every step except for a merge with another table (or, more specifically, the expan...
Hi parry2k and Anonymous,
I hope this makes it easy to duplicate.
Enter source data table:
| Part | Qty |
| ABC | 1 |
| ABC | 5 |
| ABC | 25 |
| ABC | 100 |
| ABC | 30 |
| ABC | 50 |
| ABC | 60 |
| ABC | 200 |
Using this Source, make two helper tables (Table_MinKey and Table_MaxKey):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMlSK1YGxTZHYRsgcQwMDJJ4xMscUmWOGzDECaYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Qty", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Part", Order.Ascending}, {"Qty", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Part"}, {{"Index Table", each Table.AddIndexColumn(_,"MinKey",1,1)}}),
#"Expanded Index Table" = Table.ExpandTableColumn(#"Grouped Rows", "Index Table", {"Qty", "MinKey"}, {"Qty", "MinKey"})
in
#"Expanded Index Table"
| Part | Qty | MinKey |
| ABC | 1 | 1 |
| ABC | 5 | 2 |
| ABC | 25 | 3 |
| ABC | 30 | 4 |
| ABC | 50 | 5 |
| ABC | 60 | 6 |
| ABC | 100 | 7 |
| ABC | 200 | 8 |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMlSK1YGxTZHYRsgcQwMDJJ4xMscUmWOGzDECaYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Qty", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Part", Order.Ascending}, {"Qty", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Part"}, {{"Index Table", each Table.AddIndexColumn(_,"MaxKey",0,1)}}),
#"Expanded Index Table" = Table.ExpandTableColumn(#"Grouped Rows", "Index Table", {"Qty", "MaxKey"}, {"MaxQty", "MaxKey"})
in
#"Expanded Index Table"
| Part | MaxQty | MaxKey |
| ABC | 1 | 0 |
| ABC | 5 | 1 |
| ABC | 25 | 2 |
| ABC | 30 | 3 |
| ABC | 50 | 4 |
| ABC | 60 | 5 |
| ABC | 100 | 6 |
| ABC | 200 | 7 |
Finally, combine the two tables:
let
Source = Table_MinKey,
#"Merged Queries" = Table.NestedJoin(Source, {"Part", "MinKey"}, Table_MaxKey, {"Part", "MaxKey"}, "Table_MaxKey", JoinKind.LeftOuter),
#"Expanded Table_MaxKey" = Table.ExpandTableColumn(#"Merged Queries", "Table_MaxKey", {"MaxQty", "MaxKey"}, {"MaxQty", "MaxKey"})
in
#"Expanded Table_MaxKey"
| Part | Qty | MinKey | MaxQty | MaxKey |
| ABC | 1 | 1 | 5 | 1 |
| ABC | 5 | 2 | 25 | 2 |
| ABC | 25 | 3 | 100 | 3 |
| ABC | 100 | 4 | 30 | 4 |
| ABC | 30 | 5 | 50 | 5 |
| ABC | 50 | 6 | 60 | 6 |
| ABC | 60 | 7 | 200 | 7 |
| ABC | 200 | 8 | null | null |
Expected result:
| Part | Qty | MinKey | MaxQty | MaxKey |
| ABC | 1 | 1 | 5 | 1 |
| ABC | 5 | 2 | 25 | 2 |
| ABC | 25 | 3 | 30 | 3 |
| ABC | 30 | 4 | 50 | 4 |
| ABC | 50 | 5 | 60 | 5 |
| ABC | 60 | 6 | 100 | 6 |
| ABC | 100 | 7 | 200 | 7 |
| ABC | 200 | 8 | null | null |
If you "step through" the applied steps in the last table, you can see the data switch from the expected result to the wrong result at the Expanded Table_MaxKey step. How do I avoid that? Thanks!
Hi BekahLoSurdo ,
I updated the applied step code for table Table_MinKey ,Table_MaxKey and combine query, it can get the desired result. You can also update your codes to get the desired result based on below codes:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMlSK1YGxTZHYRsgcQwMDJJ4xMscUmWOGzDECaYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Qty", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Part", Order.Ascending}, {"Qty", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "MinKey", 1, 1)
in
#"Added Index"let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMlSK1YGxTZHYRsgcQwMDJJ4xMscUmWOGzDECaYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Qty", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Part", Order.Ascending}, {"Qty", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "MaxKey", 0, 1)
in
#"Added Index"let
Source = Table_MinKey,
#"Merged Queries" = Table.NestedJoin(Source, {"Part", "MinKey"}, Table_MaxKey, {"Part", "MaxKey"}, "Table_MaxKey", JoinKind.LeftOuter),
#"Expanded Table_MaxKey" = Table.ExpandTableColumn(#"Merged Queries", "Table_MaxKey", {"Qty", "MaxKey"}, {"Table_MaxKey.Qty", "Table_MaxKey.MaxKey"})
in
#"Expanded Table_MaxKey"Best Regards
Rena