Split values from multiple columns into rows

[Anonymous]

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!

[parry2k · Accepted solution]

@Anonymous are you getting any error, attached again.

[Ashish_Mathur · Accepted solution]

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.

[parry2k]

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

@parry2k

Unfortunately the solution file does not open for me - Could you post a screenshot of the solution? Would be great! Thank you for your help!

[parry2k · Accepted solution]

@Anonymous are you getting any error, attached again.

[Anonymous]

@parry2kThank you very much for the solution! So I basically have to unpivod the "Text Before Delimiter" Columns, correct?

[parry2k · Accepted solution]

@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.⚡

[Anonymous]

Here is a better version of the tables. First table is the status quo and second table is the desired output.

[Ashish_Mathur · Accepted solution]

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.

[hohlick]

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"