Forum Discussion
Local Grouping / Index Issues
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/38490
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
Hi Anonymous,
Thank you for your effort but I need to group them as I have more than one part number (this was a simplified example, as stated in the original post) so my local index will read 1,2,3,4,5,6,7,1,2,3,1,2,...etc. Unfortunately, it is the grouping and expanding of said group that breaks it which is why your solution is working.
I found a workaround by adding two index columns at the same time to one table, expanding both (essentially making a Cartesian product) and filtering down to where the two indices are equal. It seems like a lot of extra row creation to work around what looks to be a bug in the table expansion step but it works. Thanks for your help!
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 Min", each Table.AddIndexColumn(_,"MinKey",1,1)},{"Index Max", each Table.AddIndexColumn(_,"MaxKey",0,1)}}), #"Expanded Index Min" = Table.ExpandTableColumn(#"Grouped Rows", "Index Min", {"Qty", "MinKey"}, {"Qty", "MinKey"}), #"Expanded Index Max" = Table.ExpandTableColumn(#"Expanded Index Min", "Index Max", {"Qty", "MaxKey"}, {"Qty.1", "MaxKey"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Index Max", each ([MinKey] = [MaxKey])) in #"Filtered Rows"Part Qty MinKey Qty.1 MaxKey ABC 1 1 1 0 <-- Keep this row (MinKey = MaxKey) ABC 1 1 5 1 ABC 1 1 25 2 ABC 1 1 30 3 ABC 1 1 50 4 ABC 1 1 60 5 ABC 1 1 100 6 ABC 1 1 200 7 ABC 5 2 1 0 ABC 5 2 5 1 ABC 5 2 25 2 <-- Keep this row (MinKey = MaxKey) ABC 5 2 30 3 ABC 5 2 50 4 ABC 5 2 60 5 ABC 5 2 100 6 ABC 5 2 200 7 ABC 25 3 1 0 ABC 25 3 5 1 ABC 25 3 25 2 etc.
