Pivot an unpivot field

[Raulx_7]

Hi, I have one table which I've applied an unpivot 4 columns, however, now that all the transformation is done, I needed to get them back to the original form, however now that I select pivot, it only shows one value insted of the original four

This is a representation of how it looks:

Date	GameType	PriceUSD	PriceMX	ReleaseDate	Region
Dec-20	RPG	5	100	July-20	1
Dec-20	MMORPG	5	100	July-20	1
Dec-20	SHOOTER	5	100	July-20	1
Dec-20	SPORT	5	100	July-20	1
Dec-20	RPG	5	100	July-20	1
Dec-20	MMORPG	5	100	July-20	1
Dec-20	SHOOTER	5	100	July-20	1
Dec-20	SPORT	5	100	July-20	1
Dec-20	RPG	5	100	July-20	2
Dec-20	MMORPG	5	100	July-20	2
Dec-20	SHOOTER	5	100	July-20	2
Dec-20	SPORT	5	100	July-20	2

Now I wanted to return RPG, MMORPG, SHOOTER and SPORT to columns, where the values of each one of the gametypes are the price in USD and MX, 

Something like:

Date	RPG_MX	RPG_USD	MMORPG_MX	MMORPG_USD	SHOOTER_MX	SHOOTER_USD	SPORT_MX	SPORT_USD	Region
Dec-20	100	5	100	5	100	5	100	5	1
Dec-20	100	5	100	5	100	5	100	5	1
Dec-20	100	5	100	5	100	5	100	5	2

Is there a way to perform this in the Power Query Editor? 

[Ashish_Mathur · Accepted solution]

Hello

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"GameType", type text}, {"PriceUSD", Int64.Type}, {"PriceMX", Int64.Type}, {"ReleaseDate", type date}, {"Region", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "GameType", "ReleaseDate", "Region"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Price","",Replacer.ReplaceText,{"Attribute"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Region", type text}}, "en-IN"),{"GameType", "Attribute", "Region"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "ReleaseDate", "Value", "Index"}, {"Date", "ReleaseDate", "Value", "Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", Int64.Type}}),
    #"Merged Columns1" = Table.CombineColumns(#"Changed Type1",{"Merged.1", "Merged.2"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"ReleaseDate", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Merged.3", "Region"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Region", type text}})
in
    #"Changed Type3"

I hope this helps.

[Raulx_7]

Thanks

[Ashish_Mathur]

You are welcome.