Forum Discussion
Local Grouping / Index Issues
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. |