In Power Query: Row has three rows in one cell – UPPER part. I want to separate each multiple box ro

[DSR]

[Shravan133 · Accepted solution]

1. Select the column to split:

   • In the Power Query Editor, select the column that contains the multiple values you want to split into separate rows.

2. Split column by delimiter:

   • Go to the "Transform" tab.
   • Click on "Split Column" and choose "By Delimiter".
   • Choose the delimiter that separates the values in your cell (e.g., comma, semicolon, space). In your case a Space? or new line?
   • In the Split Column by Delimiter dialog, select "Each occurrence of the delimiter" if your data is separated by the delimiter.

3. Unpivot columns:

   • After splitting the column, the values will be in separate columns.
   • Select the original columns (excluding the newly created split columns).
   • Go to the "Transform" tab and click on "Unpivot Columns" and then "Unpivot Other Columns".
   • This will turn the split columns into rows.

[v-cgao-msft · Accepted solution]

Hi @DSR ,

Split to rows by #(lf) like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lHycAvy9wtR0FXwCPKJjCmKyfMPQRcJRxZQitWJVnJyIktnLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column2", Text.Trim, type text}})
in
    #"Trimmed Text"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[DSR · Accepted solution]

Hello Shravan133,

Your method worked. Thank you. DSR

[v-cgao-msft · Accepted solution]

Hi @DSR ,

Split to rows by #(lf) like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lHycAvy9wtR0FXwCPKJjCmKyfMPQRcJRxZQitWJVnJyIktnLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column2", Text.Trim, type text}})
in
    #"Trimmed Text"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Shravan133 · Accepted solution]

1. Select the column to split:

   • In the Power Query Editor, select the column that contains the multiple values you want to split into separate rows.

2. Split column by delimiter:

   • Go to the "Transform" tab.
   • Click on "Split Column" and choose "By Delimiter".
   • Choose the delimiter that separates the values in your cell (e.g., comma, semicolon, space). In your case a Space? or new line?
   • In the Split Column by Delimiter dialog, select "Each occurrence of the delimiter" if your data is separated by the delimiter.

3. Unpivot columns:

   • After splitting the column, the values will be in separate columns.
   • Select the original columns (excluding the newly created split columns).
   • Go to the "Transform" tab and click on "Unpivot Columns" and then "Unpivot Other Columns".
   • This will turn the split columns into rows.

[DSR · Accepted solution]

Hello Shravan133,

Your method worked. Thank you. DSR

[Shravan133 · Accepted solution]

@DSR Thats good. Can you please accept my response as a solution.