Forum Discussion
Multiple unpivot in one table
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
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"
4 Replies
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.⚡
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"Anonymous , refer if this blog to unpivot twice can help
