Multiple unpivot in one table

[Anonymous]

Hi ,

how to unpivot Multiple column ,  multiple unpivot in one table

see the below image i have a data and result like this

How to solve this?

Thanks 

Rajesh

[CNENFRNL · Accepted solution]

Hello, @RajeshRanganath, you may want to use the following code in Power Query to achieve your goal.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJNLEnOANLuSGxnODtWJ1rJCcjzy1eASXqgcl2QuSDlzhCB4PzSouRUINsTje+KwgfpcEGy2gvVdG8Ul7jiktRR8kFR6IZmpw+aDVCVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Comments A" = _t, #"Column B" = _t, #"Comments B" = _t, #"Column C" = _t, #"Comments C" = _t]),
    Columns = List.Zip(List.Split(Table.ColumnNames(Source), 2)){0},
    Rows = Table.RowCount(Source),
    #"Reformed Table" = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source),2), each Table.FromColumns(_))),
    #"Added Index" = Table.AddIndexColumn(#"Reformed Table", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Columns{Number.IntegerDivide(_, Rows)}}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Column2]), "Column2", "Column1", List.Count)
in
    #"Pivoted Column"

[Ashish_Mathur · Accepted solution]

Hello

You can download my PBI file from here.

I hope this helps.

[amitchandak]

@Anonymous , refer if this blog to unpivot twice can help

https://kohera.be/blog/power-bi/how-to-unpivot-twice/

[CNENFRNL · Accepted solution]

Hello, @RajeshRanganath, you may want to use the following code in Power Query to achieve your goal.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJNLEnOANLuSGxnODtWJ1rJCcjzy1eASXqgcl2QuSDlzhCB4PzSouRUINsTje+KwgfpcEGy2gvVdG8Ul7jiktRR8kFR6IZmpw+aDVCVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Comments A" = _t, #"Column B" = _t, #"Comments B" = _t, #"Column C" = _t, #"Comments C" = _t]),
    Columns = List.Zip(List.Split(Table.ColumnNames(Source), 2)){0},
    Rows = Table.RowCount(Source),
    #"Reformed Table" = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source),2), each Table.FromColumns(_))),
    #"Added Index" = Table.AddIndexColumn(#"Reformed Table", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Columns{Number.IntegerDivide(_, Rows)}}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Column2]), "Column2", "Column1", List.Count)
in
    #"Pivoted Column"

[parry2k]

@Anonymous start a blank query and paste this m code  in advanced editor, it will create a table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJNLEnOANLuSGxnODtWJ1rJCcjzy1eASXqgcl2QuSDlzhCB4PzSouRUINsTje+KwgfpcEGy2gvVdG8Ul7jiktRR8kFR6IZmpw+aDVCVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Comments A" = _t, #"Col B" = _t, #"Comments B" = _t, #"Col C" = _t, #"Comments C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col A", type text}, {"Comments A", type text}, {"Col B", type text}, {"Comments B", type text}, {"Col C", type text}, {"Comments C", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Col A", "Col B", "Col C"}, "Field", "Value")
in
    #"Unpivoted Columns"

Use matrix visual put Field on Rows, Value on columns, and count on values, and you will get the result

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.⚡