Deliminate multiple columns in order

[PowerBI123456]

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

And want it to look like this

[mahoneypat · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

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.

[mahoneypat · Accepted solution]

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

[PowerBI123456]

@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? 

[mahoneypat]

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