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 do I pivot/unpivot below to the desire result in power query editor?
From
To
Solved! Go to Solution.
@Anonymous
Paste this code as a new Query, you can follow the same steps for any other data source:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vL38FPSUYp09fHxDwcyHAMCfFyVYnXgMk4+oa5Ayj/I0c8dIhHgGuIaBBRyD3J1BakIcHUMQpEIcnUBSzsGADXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, COLOR = _t, FRUIT = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"COLOR", type text}, {"FRUIT", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"NAME"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2)+1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Modulo", each Number.Mod([#"Integer-Division"], 2), type number),
#"Replaced Value" = Table.ReplaceValue(#"Inserted Modulo",0,2,Replacer.ReplaceValue,{"Modulo"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Modulo", type text}}, "en-US"),{"Attribute", "Modulo"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"New Column"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Index", "Integer-Division"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"New Column"]), "New Column", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"NAME", "COLOR1", "COLOR2", "FRUIT1", "FRUIT2"})
in
#"Reordered Columns"
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS ? to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Paste this code as a new Query, you can follow the same steps for any other data source:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vL38FPSUYp09fHxDwcyHAMCfFyVYnXgMk4+oa5Ayj/I0c8dIhHgGuIaBBRyD3J1BakIcHUMQpEIcnUBSzsGADXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t, COLOR = _t, FRUIT = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"COLOR", type text}, {"FRUIT", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"NAME"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2)+1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Modulo", each Number.Mod([#"Integer-Division"], 2), type number),
#"Replaced Value" = Table.ReplaceValue(#"Inserted Modulo",0,2,Replacer.ReplaceValue,{"Modulo"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Modulo", type text}}, "en-US"),{"Attribute", "Modulo"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"New Column"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Index", "Integer-Division"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"New Column"]), "New Column", "Value"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"NAME", "COLOR1", "COLOR2", "FRUIT1", "FRUIT2"})
in
#"Reordered Columns"
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS ? to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy, it somehow works but I think its too many steps and complicated. I wanted a unpivot then pivot back solution. Thanks for the query.
@Anonymous
Glad it works!
All steps are only through GUI, no manual coding; there aren't many steps actually.
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS ? to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Anonymous
@Fowmy s solution does unpivot and pivot back like you requested, please check again.
It requires some additional steps, but with that method there is not much to shorten.
(Grouping would be an alternative, but that doesn't seem what you like)
I was wondering a bit, why you want to transform your data like this. (Potentially) dynamic columns are not suitable for the Power BI datamodel.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@Anonymous ,
refer if this can help
https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Hi @amitchandak, I didnt find solution in your 3 articles, they are different from what I want to achieve. Thanks for the post
@ImkeF , can you help on this
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |