Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hola a todos,
He importado y archivo de Excel en PowerBi. El archivo tiene datos de ventas, comisiones de ventas y porcentaje de comisiones, desglosados por año y trimestre. El problema es que la fecha del año y del trimestre se encuentra encima de los encabezados de columna (consulte la tabla a continuación). ¿Cómo podría llevar la fecha de arriba a una columna para que pueda estar en un formato de tabla?
202009 YTD | 202009 YTD | 201909 YTD | 201909 YTD | |||||
Región | Tipo | Empresa | Ventas | Comisión de Ventas | Comisión | Ventas | Comisión de Ventas | Comisión |
Este | Suv | Ford | 4,081 | 26 | 0.6% | 5,979 | 55 | 0.9% |
Oeste | Suv | Ford | 4,652 | 54 | 1.2% | 7,703 | 42 | 0.5% |
Norte | Suv | Ford | 7,315 | 25 | 0.3% | 3,058 | 58 | 1.9% |
Sur | Suv | Ford | 6,369 | 42 | 0.7% | 2,188 | 65 | 3.0% |
Este | Suv | Gm | 5,265 | 11 | 0.2% | 2,418 | 51 | 2.1% |
Oeste | Suv | Gm | 3,173 | 23 | 0.7% | 6,691 | 23 | 0.3% |
Norte | Suv | Gm | 6,972 | 64 | 0.9% | 2,721 | 29 | 1.1% |
Sur | Suv | Gm | 8,366 | 78 | 0.9% | 2,666 | 81 | 3.0% |
Gracias
Solved! Go to Solution.
Esta es una manera de hacerlo en el editor de consultas. Para ver cómo funciona, simplemente cree una consulta en blanco, vaya a Editor avanzado y reemplace el texto allí con el código M a continuación.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFNT8MwDIb/SlWpt6iKndZJznyd4MAGCFU7VFoFk9g6reOwf4/tMFptPXCoXTt+8uZNmibPcvP3oUVrY/a+vL0s0irEyeqkyPKVafLn7mPT77hcnvYdp5t+u293J/5btF/dcM4Z97ebYUizY5H9f1DU7trhKJMvrxzv+8OaU2VsADkccbAlFZxqE32UXGsvFkq/dXM01SiTFQcoUWhvvHWyhkrXiX7qD8fPS9wbB6KBScgJ7oytg2wZdMtf8UX/fY2TcRRHJS84GghCkmzpSltcO394VI+oIwDKYmIrUGm9kBJmfCvrDHhxiG7UJUMRxp6bc60w8eXKeak6X64Ie1Q4qmeY86xwYMfyUD5MYdKePmNyvPoB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
TimePeriod = List.Select(List.Distinct(Record.ToList(#"Changed Type"{0})), each _ <>" "),
Custom1 = #"Changed Type",
#"Removed Top Rows" = Table.Skip(Custom1,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Type", type text}, {"Company", type text}, {"Sales", Int64.Type}, {"Sales Commission", Int64.Type}, {"Commission ", Percentage.Type}, {"Sales_1", Int64.Type}, {"Sales Commission_2", Int64.Type}, {"Commission _3", Percentage.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Region", "Type", "Company"}, "Attribute", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
#"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Region", "Type", "Company", "Attribute"}, {{"Max", each Table.FromColumns({[Value], TimePeriod})}}),
#"Expanded Max" = Table.ExpandTableColumn(#"Grouped Rows", "Max", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Max",{{"Column1", "Value"}, {"Column2", "Time Period"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time Period", type text}, {"Value", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
saludos
palmadita
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Esta es una manera de hacerlo en el editor de consultas. Para ver cómo funciona, simplemente cree una consulta en blanco, vaya a Editor avanzado y reemplace el texto allí con el código M a continuación.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFNT8MwDIb/SlWpt6iKndZJznyd4MAGCFU7VFoFk9g6reOwf4/tMFptPXCoXTt+8uZNmibPcvP3oUVrY/a+vL0s0irEyeqkyPKVafLn7mPT77hcnvYdp5t+u293J/5btF/dcM4Z97ebYUizY5H9f1DU7trhKJMvrxzv+8OaU2VsADkccbAlFZxqE32UXGsvFkq/dXM01SiTFQcoUWhvvHWyhkrXiX7qD8fPS9wbB6KBScgJ7oytg2wZdMtf8UX/fY2TcRRHJS84GghCkmzpSltcO394VI+oIwDKYmIrUGm9kBJmfCvrDHhxiG7UJUMRxp6bc60w8eXKeak6X64Ie1Q4qmeY86xwYMfyUD5MYdKePmNyvPoB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
TimePeriod = List.Select(List.Distinct(Record.ToList(#"Changed Type"{0})), each _ <>" "),
Custom1 = #"Changed Type",
#"Removed Top Rows" = Table.Skip(Custom1,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Region", type text}, {"Type", type text}, {"Company", type text}, {"Sales", Int64.Type}, {"Sales Commission", Int64.Type}, {"Commission ", Percentage.Type}, {"Sales_1", Int64.Type}, {"Sales Commission_2", Int64.Type}, {"Commission _3", Percentage.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Region", "Type", "Company"}, "Attribute", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
#"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Region", "Type", "Company", "Attribute"}, {{"Max", each Table.FromColumns({[Value], TimePeriod})}}),
#"Expanded Max" = Table.ExpandTableColumn(#"Grouped Rows", "Max", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Max",{{"Column1", "Value"}, {"Column2", "Time Period"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time Period", type text}, {"Value", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
saludos
palmadita
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
palmadita
¡Gracias por lo que funcionó! El código M está un poco por encima de mi calibur en este momento. ¿Hay una manera más escasa de obtener la misma salida?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.