ORDER A TEXT IN A COLUMN POWERQUERY

Question

I would like to know if you can assist me in the following matter:

in PowerQuery I have a column like this: input

and i would like to Sort the column "CANAL / BU" like this:

I tried to sort the function but sort the entire column not the text.

could you guys please so kind to assist me?

regards,

user_2872922 · Answer

From your example, it appears you only want to sort the text in a single column.

You can do that by:

Split each text line by the "/" into a List.
Sort the List
Recombine the List using the "/" delimiter.

A line of M Code that will do that:

    #"Sort Column" = Table.TransformColumns(#"Previous Step",
    {"CANAL/BU", each Text.Combine(List.Sort(Text.Split(_,"/")),"/")})

Of course, you could also do this by adding a custom column and sorting there; but with the Table.TransformColumns method, you don't need the extra column.

Here is code that will reproduce the first few columns of your table, and show the sorting step in situ:

let
    Source = Table.FromRecords(
        {
            [AREA="Channel Marketing",
            #"CANAL/BU"="WT/NT/O",
            GPID=70181170]
        }
    ),
    #"Change Type" = Table.TransformColumnTypes(Source,{{"AREA", type text}, {"CANAL/BU", type text}, {"GPID", Int64.Type}}),

    #"Sort Column" = Table.TransformColumns(#"Change Type",
        {"CANAL/BU", each Text.Combine(List.Sort(Text.Split(_,"/")),"/")})

in
    #"Sort Column"

Output

ORDER A TEXT IN A COLUMN POWERQUERY

1 Answers