Forum Discussion

Betty888's avatar
Betty888
Helper II
3 years ago
Solved

Sort into grouped rows column

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,

Need Help
Tips and Tricks
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

4 Replies

    • Betty888's avatar
      Betty888
      Helper II
      3 years ago

      Thank you for your reply! 

      I don't know where to put your query?

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        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