Forum Discussion

Oscartv97's avatar
Oscartv97
Frequent Visitor
6 years ago
Solved

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

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's avatar
    Ashish_Mathur
    6 years ago

    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.

    Untitled.png

5 Replies

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        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.

        Untitled.png