Forum Discussion

jamienourish's avatar
jamienourish
Frequent Visitor
6 years ago
Solved

Rearrange a delimited string

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?

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

7 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    6 years ago

    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

    • jamienourish's avatar
      jamienourish
      Frequent Visitor
      6 years ago

      Thank you,

       

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

      • mussaenda's avatar
        mussaenda
        Community Champion
        6 years ago

         

        Unfortunately you cannot use these steps for direct query.

        Don't know how to help. Sorry.

        Mariuszmight help you.

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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