Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Raulx_7
Helper I
Helper I

Pivotar un campo sin despentar

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:

FechaGameTypePriceUSDPriceMXReleaseDateRegión
Dic-20Rpg510020 de julio1
Dic-20Mmorpg510020 de julio1
Dic-20Tirador510020 de julio1
Dic-20Deporte510020 de julio1
Dic-20Rpg510020 de julio1
Dic-20Mmorpg510020 de julio1
Dic-20Tirador510020 de julio1
Dic-20Deporte510020 de julio1
Dic-20Rpg510020 de julio2
Dic-20Mmorpg510020 de julio2
Dic-20Tirador510020 de julio2
Dic-20Deporte510020 de julio2

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:

FechaRPG_MXRPG_USDMMORPG_MXMMORPG_USDSHOOTER_MXSHOOTER_USDSPORT_MXSPORT_USDRegión
Dic-2010051005100510051
Dic-2010051005100510051
Dic-2010051005100510052

¿Hay alguna manera de realizar esto en el Editor de Power Query?

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Raulx_7, traté de despivot tipo de juego y precio USD. Y un par de opciones más. Pero está creando en 2 filas.

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks

De nada.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors