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)
Each product is assigned a different number of ICD Codes (between 1 and 130 ICDs). It's medical products and the ICD code (e.g. A16.12) shows the medical condition (e.g. headache) the medication (e.g. Aspirin) is approved for.
| Product | Revenue | Text Before Delimiter | Text Before Delimiter |
| Product A | 1 EUR | A16.12 | A23.9 |
| Product B | 2 EUR | A16.12 | A24 |
| Product C | 1 EUR | D15 | A24 |
The following table shows my desired output.
I would like to re-arrange the table, such that I can see the revenues not based on the individual product but based on ICD codes. The following table shows my desired output.
| Product | Revenue | A16.12 | D15 | A23.9 | A24 |
| Product A | 1 EUR | 1 EUR | 1 EUR | ||
| Product B | 2 EUR | 2 EUR | 2 EUR | ||
| Product C | 1 EUR | 1 EUR | 1 EUR |
Hope to hear from you!
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.⚡
8 Replies
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.
Anonymous solution attached.
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.⚡
Anonymous are you getting any error, attached again.
