Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BekahLoSurdo
Resolver IV
Resolver IV

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:

Index Order 1.PNGIndex Order 2.PNG

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 7
ROEL_T
Frequent Visitor

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.

v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft,

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!

parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k and @v-yiruan-msft,

I hope this makes it easy to duplicate.

Enter source data table:

PartQty
ABC1
ABC5
ABC25
ABC100
ABC30
ABC50
ABC60
ABC200

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"

 

PartQtyMinKey
ABC11
ABC52
ABC253
ABC304
ABC505
ABC606
ABC1007
ABC2008

 

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"

 

PartMaxQtyMaxKey
ABC10
ABC51
ABC252
ABC303
ABC504
ABC605
ABC1006
ABC200

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"

 

PartQtyMinKeyMaxQtyMaxKey
ABC1151
ABC52252
ABC2531003
ABC1004304
ABC305505
ABC506606
ABC6072007
ABC2008nullnull

Expected result:

PartQtyMinKeyMaxQtyMaxKey
ABC1151
ABC52252
ABC253303
ABC304504
ABC505605
ABC6061006
ABC10072007
ABC2008nullnull

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"

combine query.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft,

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"

 

PartQtyMinKeyQty.1MaxKey 
ABC1110<-- Keep this row (MinKey = MaxKey)
ABC1151 
ABC11252 
ABC11303 
ABC11504 
ABC11605 
ABC111006 
ABC112007 
ABC5210 
ABC5251 
ABC52252<-- Keep this row (MinKey = MaxKey)
ABC52303 
ABC52504 
ABC52605 
ABC521006 
ABC522007 
ABC25310 
ABC25351 
ABC253252etc.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors