Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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?