Pivot/unpivot to desire format

[Anonymous]

Hi,

How do I pivot/unpivot below to the desire result in power query editor?

From

before

To

after-desire result

[Fowmy · Accepted 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

[Anonymous]

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.

[Fowmy]

@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

[ImkeF]

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.

[amitchandak]

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

[Anonymous]

Hi @amitchandak, I didnt find solution in your 3 articles, they are different from what I want to achieve. Thanks for the post

[amitchandak]

@ImkeF , can you help on this