Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hola, tengo una tabla que he aplicado un unpivot 4 columnas, sin embargo, ahora que toda la transformación está hecha, necesitaba volver a la forma original, sin embargo ahora que selecciono pivote, sólo muestra un valor en lugar de los cuatro originales
Esta es una representación de cómo se ve:
Fecha | GameType | PriceUSD | PriceMX | ReleaseDate | Región |
Dic-20 | Rpg | 5 | 100 | 20 de julio | 1 |
Dic-20 | Mmorpg | 5 | 100 | 20 de julio | 1 |
Dic-20 | Tirador | 5 | 100 | 20 de julio | 1 |
Dic-20 | Deporte | 5 | 100 | 20 de julio | 1 |
Dic-20 | Rpg | 5 | 100 | 20 de julio | 1 |
Dic-20 | Mmorpg | 5 | 100 | 20 de julio | 1 |
Dic-20 | Tirador | 5 | 100 | 20 de julio | 1 |
Dic-20 | Deporte | 5 | 100 | 20 de julio | 1 |
Dic-20 | Rpg | 5 | 100 | 20 de julio | 2 |
Dic-20 | Mmorpg | 5 | 100 | 20 de julio | 2 |
Dic-20 | Tirador | 5 | 100 | 20 de julio | 2 |
Dic-20 | Deporte | 5 | 100 | 20 de julio | 2 |
Ahora quería devolver RPG, MMORPG, SHOOTER y SPORT a las columnas, donde los valores de cada uno de los tipos de juego son el precio en USD y MX,
Algo así como:
Fecha | RPG_MX | RPG_USD | MMORPG_MX | MMORPG_USD | SHOOTER_MX | SHOOTER_USD | SPORT_MX | SPORT_USD | Región |
Dic-20 | 100 | 5 | 100 | 5 | 100 | 5 | 100 | 5 | 1 |
Dic-20 | 100 | 5 | 100 | 5 | 100 | 5 | 100 | 5 | 1 |
Dic-20 | 100 | 5 | 100 | 5 | 100 | 5 | 100 | 5 | 2 |
¿Hay alguna manera de realizar esto en el Editor de Power Query?
Solved! Go to Solution.
Hola
Este código M funciona
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"
Espero que esto ayude.
@Raulx_7, traté de despivot tipo de juego y precio USD. Y un par de opciones más. Pero está creando en 2 filas.
Hola
Este código M funciona
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"
Espero que esto ayude.
Thanks
De nada.