Transform complex Excel table into a usable table in Power BI (using Power BI)

[Oscartv97]

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!

[Ashish_Mathur]

Hi,

Share the link from where i can download your MS Excel file.

[Oscartv97]

Here you go, the link for the file, sorry i fogor about it.

Excel File

[Ashish_Mathur · Accepted 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.

[amitchandak]

@Oscartv97 , Better that power bi identifies each table a new table. else you need a complex transformation in M.

@ImkeF , can you help

[Oscartv97]

@amitchandak How can i do that?