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
Estoy intentando conectar un archivo de Excel complejo a Power BI:
La primera tabla es la del archivo de Excel, es la base de datos. La segunda tabla es la que creo que funcionaría en Power BI, pero no sé cómo controlar la fecha para transformarla en esa tabla.
Quiero transformar us en Power BI, así que no tengo que volver a trabajar cada vez que actualizo ese archivo de Excel (fechas nuevas). También no sé es esto incluso posible, no sé por dónde empezar. Cualquier ayuda será útil.
Link to file: Excel File
¡Gracias!
Solved! Go to Solution.
Hola
Este código M funciona
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Ticker", type text}, {"Mar-12", type any}, {"Jun-12", type any}, {"Sep-12", type any}, {"Dec-12", type any}, {"Mar-13", type any}, {"Jun-13", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "Legacy ITS Peers")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Category", each if [Ticker]="Ticker" or [Ticker]=null then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Company", each if [Ticker]<>"Ticker" and [Ticker]<>null then [Column1] else null),
#"Filled Up" = Table.FillUp(#"Added Custom1",{"Company"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1", "Ticker"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Category", "Company"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Date"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Company", "Date", "Value", "Category"}),
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Custom", each try Number.IsNaN([Value]) otherwise null),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each ([Custom] = false)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Value", Percentage.Type}})
in
#"Changed Type2"
Espero que esto ayude.
Hola
Comparte el enlace desde donde puedo descargar tu archivo MS Excel.
Hola
Este código M funciona
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Ticker", type text}, {"Mar-12", type any}, {"Jun-12", type any}, {"Sep-12", type any}, {"Dec-12", type any}, {"Mar-13", type any}, {"Jun-13", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "Legacy ITS Peers")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Category", each if [Ticker]="Ticker" or [Ticker]=null then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Company", each if [Ticker]<>"Ticker" and [Ticker]<>null then [Column1] else null),
#"Filled Up" = Table.FillUp(#"Added Custom1",{"Company"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1", "Ticker"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Category", "Company"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Date"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Company", "Date", "Value", "Category"}),
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Custom", each try Number.IsNaN([Value]) otherwise null),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each ([Custom] = false)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Value", Percentage.Type}})
in
#"Changed Type2"
Espero que esto ayude.
@Oscartv97 , Mejor que power bi identifique cada tabla una nueva tabla. otra cosa se necesita una transformación compleja en M.
@ImkeF , ¿puede ayudar
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 |