CODEWITHSUNDEEP
powerbipowerquery
Ben Cruz
Ben Cruz

Reputation: 55

ORDER A TEXT IN A COLUMN POWERQUERY

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

in PowerQuery I have a column like this: input Example 1

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

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

could you guys please so kind to assist me?

regards,

Upvotes: 1

Views: 736

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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
enter image description here

Upvotes: 2

Related Questions