Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |