Table expand causing contents of text field to be reordered

[rustwood]

I have data that looks something like this:

ID  Value

1    A 

1    C

2    A

2    B

2    C

3    B

Any ID that has a value for C is particularly important, so I sort it (via another column) such that when I group it, C appears first in the list.  I then end up with this:

ID  Values

1    C, A

2    C, A, B

3    B

That works fine until I merge in another table and expand it.  At that point, some of the records end up with values in an order I don't understand (C,A,D,B).  It seems crazy, but I can see the change when I switch from the merge step to the expand table step in the query editor.  I suspect it is due to query optimization on the back end, but I have no idea why it seems to be affecting some records but not others.  I have a convoluted workaround in which I add numeric prefixes to those values in the "correct" order, then manually resort those values and text.remove the prefixes.  That seems like a very long walk for this though so I'm wondering if there is a better way.

[PhilipTreacy · Accepted solution]

@rustwood 

PQ method for storing/sorting data is let's say inconsistent compared to what it shows you on screen.  It's internal sorting/ordering is/can be different to the way it displays the data so you end up with situations like this.

You can use Buffer functions like List.Buffer and Table.Buffer to force a consistent state.

So in your case with the table merges use Table.Buffer to buffer the tables before you do the merge.

Table.Buffer - PowerQuery M | Microsoft Learn

In your query add a manual step like this

...,
BufferedTable = Table.Buffer(#"Previous Step"),

in

BufferedTable

and merge that

Regards

Phil

[rustwood]

Thanks @PhilipTreacy and @Anonymous . 

I'd seen mention of the table.buffer when I searched for info on this problem, but I didn't understand how/where it would be applied.  PhilipTreacy got me to the finish line with that and it works in my model.   My original sort approach wasn't as elegant as the code in the very detailed response posted by v-heq-msft, so I'll be using the improved code for a more complicated sort elsewhere in my project.

Both solutions are welcome additions to my PQ toolbox.

[Omid_Motamedise]

in the step that you sort the rows, a formula like Table.sort(......) will generated, add Table.Buffer and edit this step as Table.Buffer(Table.SOrt(......))

[Anonymous · Accepted solution]

Hi @rustwood ,
Thanks for PhilipTreacy reply.
For the first table you can use this code

let
    Source = Table.FromRecords({
        [ID = 1, Value = "A"],
        [ID = 1, Value = "C"],
        [ID = 2, Value = "A"],
        [ID = 2, Value = "B"],
        [ID = 2, Value = "C"],
        [ID = 3, Value = "B"]
    }),
    AddCustom = Table.AddColumn(Source, "SortOrder", each if [Value] = "C" then 0 else if [Value] = "A" then 1 else if [Value] = "B" then 2 else 3),
    SortedTable = Table.Sort(AddCustom,{{"ID", Order.Ascending}, {"SortOrder", Order.Ascending}}),
    GroupedTable = Table.Group(SortedTable, {"ID"}, {{"Values", each Text.Combine(List.Transform(Table.Sort(_, {{"SortOrder", Order.Ascending}})[Value], each _), ", "), type text}})
in
    GroupedTable

After merge and expand table

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PhilipTreacy · Accepted solution]

@rustwood 

PQ method for storing/sorting data is let's say inconsistent compared to what it shows you on screen.  It's internal sorting/ordering is/can be different to the way it displays the data so you end up with situations like this.

You can use Buffer functions like List.Buffer and Table.Buffer to force a consistent state.

So in your case with the table merges use Table.Buffer to buffer the tables before you do the merge.

Table.Buffer - PowerQuery M | Microsoft Learn

In your query add a manual step like this

...,
BufferedTable = Table.Buffer(#"Previous Step"),

in

BufferedTable

and merge that

Regards

Phil