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 a todos,
Me estoy perdiendo lo que espero que sea un simple paso aquí...
Tengo mis datos en este formato:
Ubicación | Tipo | Enero | Febrero | Marzo |
A | 1 | 0.1 | 0.2 | 0.1 |
A | 2 | 0.2 | 0.1 | 0.1 |
A | 3 | 0.1 | 0.1 | 0.12 |
B | 1 | 0.1 | 0.12 | 0.2 |
B | 2 | 0.12 | 0.2 | 0.2 |
B | 3 | 0.2 | 0.05 | 0.1 |
C | 1 | 0.2 | 0.1 | 0.05 |
C | 2 | 0.1 | 0.05 | 0.1 |
C | 3 | 0.05 | 0.2 | 0.12 |
y no quiero llegar a este formato:
Ubicación | Mes | Tipo 1 | Tipo 2 | Tipo 3 |
A | Enero | 0.1 | 0.2 | 0.1 |
A | Febrero | 0.2 | 0.1 | 0.1 |
A | Marzo | 0.1 | 0.1 | 0.12 |
B | Enero | 0.1 | 0.12 | 0.2 |
B | Febrero | 0.12 | 0.2 | 0.05 |
B | Marzo | 0.2 | 0.2 | 0.1 |
C | Enero | 0.2 | 0.1 | 0.05 |
C | Febrero | 0.1 | 0.05 | 0.2 |
C | Marzo | 0.05 | 0.1 | 0.12 |
Cualquier consejo sería apreciado!
Gracias a todos,
Shmuel
Solved! Go to Solution.
Hola
Este código M funciona
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location", "Type"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Type", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Type", type text}}, "en-IN")[Type]), "Type", "Value")
in
#"Pivoted Column"
Espero que esto ayude.
Hola
Este código M funciona
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location", "Type"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Type", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Type", type text}}, "en-IN")[Type]), "Type", "Value")
in
#"Pivoted Column"
Espero que esto ayude.
Hola @sdukes ,
Puede aplicar los pasos de unpivot y pivote en Power Query Editor para lograrlo, los códigos específicos como se indica a continuación:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYgM9CGkEZcfqQOSMUERR5YxRRMGkEVjSCc1QQ5gpMEkjVGFUSWMkUQNTJDud4cYiuweoBCaJJoyq0xhZGO6A2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Type = _t, Jan = _t, Feb = _t, Mar = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Type", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Jan", "Feb", "Mar"}, "Attribute", "Value"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Unpivoted Only Selected Columns", {"Location", "Attribute", "Value"}, "Attribute.1", "Value.1"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Value.1", type text}}, "zh-CN"),{"Attribute.1", "Value.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"Location", "Attribute", "Merged", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Merged]), "Merged", "Value")
in
#"Pivoted Column"
Best Regards
Rena
@sdukes revisar mi entrada de blog aquí que más o menos hablar sobre el caso de uso similar
Me gustaría❤ elogiossi mi solución ayudara.👉Si puedes pasar tiempo publicando la pregunta, también puedes hacer esfuerzos para dar a Kudos quien haya ayudado a resolver tu problema. ¡Es una muestra de agradecimiento!
⚡Visítenos enhttps://perytus.com, su ventanilla única para proyectos/formación/consulta relacionados con Power BI.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
2 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |