cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Deliminate multiple columns in order

Hi, is there a way in Power Query to deliminate multiple columns into rows simultaneously. For example, I have the table below:

 

PowerBI123456_1-1593810274997.png

 

And want it to look like this

PowerBI123456_2-1593810292590.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User VI
Super User VI

Re: Deliminate multiple columns in order

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQx0jHWMdExVdJRctRx0nHWcdFxBbIhokASyDbUA6rSA7L1gGJ6QLV6pkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}, {"Value.4", type text}, {"Value.5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type number}})
in
    #"Changed Type1"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Super User V
Super User V

Re: Deliminate multiple columns in order

Hi,

Here's another M code that works.  I leant this from @mahoneypat post here just yesterday.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([Column 1], ","), Text.Split([Column 2], ","), Text.Split([Column 3], ","), Text.Split([Column 4], ",")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Column 1", "Column 2", "Column 3", "Column 4"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", type text}, {"Custom.3", Int64.Type}, {"Custom.4", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom.1", "Column 1"}, {"Custom.2", "Column 2"}, {"Custom.3", "Column 3"}, {"Custom.4", "Column 4"}})
in
    #"Renamed Columns"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Super User VI
Super User VI

Re: Deliminate multiple columns in order

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQx0jHWMdExVdJRctRx0nHWcdFxBbIhokASyDbUA6rSA7L1gGJ6QLV6pkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}, {"Value.4", type text}, {"Value.5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type number}})
in
    #"Changed Type1"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Super User V
Super User V

Re: Deliminate multiple columns in order

Hi,

Here's another M code that works.  I leant this from @mahoneypat post here just yesterday.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([Column 1], ","), Text.Split([Column 2], ","), Text.Split([Column 3], ","), Text.Split([Column 4], ",")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Column 1", "Column 2", "Column 3", "Column 4"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", type text}, {"Custom.3", Int64.Type}, {"Custom.4", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Custom.1", "Column 1"}, {"Custom.2", "Column 2"}, {"Custom.3", "Column 3"}, {"Custom.4", "Column 4"}})
in
    #"Renamed Columns"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted
Helper IV
Helper IV

Re: Deliminate multiple columns in order

@mahoneypat @Ashish_Mathur Thanks and sorry for the delay in response. I have some edits in Power Query that I have made already, do I just add that M code on? 

Highlighted
Super User VI
Super User VI

Re: Deliminate multiple columns in order

It depends on what those other changes are, but you may be able to do this suggested approach before or after.  Just follow the same approach and try it out.  If you are comfortable making changes in Advanced Editor, you can append the steps starting with the custom column, but you'll likely need to change the code a little to reference the new previous step.

 

Regards,

Pat

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors