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
Highlighted
Super User III
Super User III

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 III
Super User III

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
Highlighted
Super User III
Super User III

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 III
Super User III

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 III
Super User III

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
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!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors