Forum Discussion
Split values from multiple columns into rows
Hi guys, love this forum, so many great hints, very helpful! I have a problem with my table that causes me headaches, I hope someone can help me with this. This is my status quo (simplified) E...
Anonymous are you getting any error, attached again.
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Revenue", type text}, {"Text Before Delimiter", type text}, {"Text Before Delimiter2", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product", "Revenue"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Revenue", "Revenue - Copy"), #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Value]), "Value", "Revenue") in #"Pivoted Column"Hope this helps.
Anonymous yes sir, that's it. You got it.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Here is a better version of the tables. First table is the status quo and second table is the desired output.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Revenue", type text}, {"Text Before Delimiter", type text}, {"Text Before Delimiter2", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product", "Revenue"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Revenue", "Revenue - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Value]), "Value", "Revenue")
in
#"Pivoted Column"Hope this helps.