Forum Discussion
Raulx_7
Helper I
Helper IPivot an unpivot field
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?
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.
3 Replies
- Ashish_Mathur
Super User
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_7Thanks
- Ashish_Mathur
You are welcome.