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.
here is the M code for Query Editor:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlFwVNJRMlRwDQ0C0o6GZnqGRiCGkbGepVKsDkKZE1DUCFOZCYoiZySzXAxNYSpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Revenue = _t, #"Text Before Delimiter" = _t, #"Text Before Delimiter.1" = _t]),
Tab1Selected = Table.SelectColumns(Source,{"Product", "Revenue", "Text Before Delimiter.1"}),
Tab1 = Table.RenameColumns(Tab1Selected,{{"Text Before Delimiter.1", "Text Before Delimiter"}}),
Tab2 = Table.SelectColumns(Source,{"Product", "Revenue", "Text Before Delimiter"}),
Custom1 = Tab1 & Tab2,
#"Pivoted Column" = Table.Pivot(Custom1, List.Distinct(Custom1[#"Text Before Delimiter"]), "Text Before Delimiter", "Revenue")
in
#"Pivoted Column"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.
