Sort into grouped rows column

[Betty888]

Dear Power Bi Community,

I have an issue with the group by function where the combined values are combination of 2 fields :

Field 1 = numbers

Field 2 = text

I’ve used the following formula to group rows :

= Table.Group(#"customized column", {"FactorA", "FactorB"}, {{"Combined value", each Text.Combine((List.Sort( [Field1 & Field2], Order.Ascending),"#(lf)"), type nullable text}})

with this formula, rows into each combined value is sorted but as following :

1 xxxx

10 xxxx

2 xxxx

while I’m looking for an order as :

1 xxxx

2 xxxx

10 xxxx

Any ideas to help with are more than welcome !

Many thanks in advance for your help !  

Regards,

[wdx223_Daniel]

List.Sort(lst,eacn Number.From(_))

[Betty888]

Thank you for your reply! 

I don't know where to put your query?

[v-yueyunzh-msft · Accepted solution]

Hi , @Betty888 

You can try this in "Advanced Editor" in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoCYnMLIFGRnFisFKuDEDU0ABLFiXCxZCA2AilEEzMFi4GFQNpS0JTBxExAYlArQNpS4VoT4WJpyOpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FactorA = _t, FactorB = _t, #"Field 1" = _t, #"Field 2" = _t]),
    test = Table.TransformColumnTypes(Source,{{"FactorA", type text}, {"FactorB", type text}, {"Field 1", Int64.Type}, {"Field 2", type text}}),
    Custom1 = Table.Group(test, {"FactorA", "FactorB"}, {"Combined value",(x)=>Table.Sort(x,"Field 1")  })
in
    Custom1

The result is as follows:

Best Regards,

Aniya Zhang

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

[Betty888]

Many thanks ! 

It was really helpful !!!