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.
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.
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 |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
1 | |
1 | |
1 |