Rearrange a delimited string

[jamienourish]

I have a column with the string "morning, evening, afternoon"

I need this as a string but in the order "morning, afternoon, evening"

How can I do this withing PowerBi? Is there a way I can make it into a list rearrange and join again?

[v-juanli-msft]

Hi 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @jamienourish 

Situation1:

In Edit queries, add a custom column

Situation 2:

In Edit queries, Create a conditional column

Close&&apply

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz]

Hi @jamienourish 

sure please see the attached file with the solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[jamienourish]

Thank you,

I seem to be having a problem doing this with direct query. any ideas?

[mussaenda]

Unfortunately you cannot use these steps for direct query.

Don't know how to help. Sorry.

@Mariuszmight help you.

[Mariusz]

Hi @jamienourish 

To make it work for the direct query you can create a table like below and create a relationship on 'Table A'[Text] and 'Table B'[In] and use "Out" column in your reports.

Table B = 
ADDCOLUMNS(
    ROW( "In", "morning, evening, afternoon" ),
    "Out", "morning, afternoon, evening"
)

Or change the order at the source

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[mussaenda]

Hi @jamienourish ,

I am giving you 2 options:

1.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys0vysvMS1eK1YlWSi1LhbMT00pSi/Ly8/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column3", "Column2"}, {"Column2", "Column3"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"

2.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys0vysvMS1eK1YlWSi1LhbMT00pSi/Ly8/OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Conditional Column" = Table.AddColumn(Source, "Index", each if Text.Contains([Column1], "morning") then 1 else if Text.Contains([Column1], "afternoon") then 2 else if Text.Contains([Column1], "evening") then 3 else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Index", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

Then just merge the number to your fact table and sort your column based on your new column