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.
Hello
I'm trying to connect a complex Excel file to Power BI:
The first table is the one in the Excel file, it's the database. The second table is what I think would work in Power BI, but I don't know how to control the date to transform it into that table.
I want to transform us into Power BI, so I don't have to go back to work every time I update that Excel file (new dates). I also don't know this is even possible, I don't know where to start. Any help will be helpful.
Link to file: Excel File
Thank you!
Solved! Go to Solution.
Hello
This M code works
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"
I hope this helps.
Hi,
Share the link from where i can download your MS Excel file.
Hello
This M code works
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"
I hope this helps.
@Oscartv97 , Better that power bi identifies each table a new table. else you need a complex transformation in M.
@ImkeF , can you help
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |