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 expansion after the merge). As soon as I expand the second table, my index column remains but the data reverts to source order so in effect, the index numbers are now wrong:
If you follow one Index number (i.e. MinKey = 4), you will see that the 4th Qty value is 30 until the final step where it changes to 100 which is incorrect.
To group and index my rows I'm using:
= Table.Group(#"Sorted Rows", {"Part"}, {{"Index Table", each Table.AddIndexColumn(_,"MinKey",1,1)}})= Table.Group(#"Sorted Rows", {"Part"}, {{"Index Table", each Table.AddIndexColumn(_,"MaxKey",0,1)}})
In my actual data there are multiple part numbers so it is essentially a local group/index but the above is a simplified version which still shows the behaviour.
Thank you for your help.
Bekah
7 Replies
Add the GroupBy & Indexing steps AFTER the merge step, but BEFORE the expanding step. That way the ordering will not be reshuffeld to source data after expanding.
BekahLoSurdo can you just simply provide raw sample data in table format and expected output, not sure what you are trying to achieve, it is hard to understand these tiny images. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hi 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
Hi BekahLoSurdo ,
Please try to update the code in group steps as blow:
= Table.Group(#"Sorted Rows", {"Qty"}, {{"Index Table", each Table.AddIndexColumn(_,"MinKey",1,1)}})
= Table.Group(#"Sorted Rows", {"Qty"}, {{"Index Table", each Table.AddIndexColumn(_,"MaxKey",0,1)}}) If the above method not working, please provide your expected result with screen shot and explain more details on it. Thank you.
Best Regards
Rena
Anonymous,
If I partition by Qty, each line will have an Index value of 1 instead of the correct 1-8. I am partitioning by Part because I need a local index for each Part in my data. I am working on another example now. Thanks!
