Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello
You can download my PBI file from here.
I hope this helps.
Hello
You can download my PBI file from here.
I hope this helps.
@Anonymous , refer if this blog to unpivot twice can help
https://kohera.be/blog/power-bi/how-to-unpivot-twice/
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |