Data import into Power BI for complex Excel sheet

[aabha123]

Hello All,

I am working on a business case to calculate the "Return on investment" for the different cost type categories. Here, I am using a simple calculation for ROI formula: Benefits minus Invesments (Please refer the attached sample data sheet).

I am struggling with the data import part, as the excel sheet has yearwise data distributed over columns. Can anyone help me with the data import part, which steps do i need to follow to make the ROI calculation yearwise possible. 

The data is about ROI, including the planned/initial values and the current/updated values per year.

https://drive.google.com/drive/folders/1Fmz5P_l9bbU4e6Cl93h94jENMJAi6SF6?usp=sharing 

Thanks in advance for the inputs.

Sample output:

@PowerQueryFTW @dataimport @Anonymous 

[aabha123]

Thank you for the help. This works.

[Anonymous · Accepted solution]

Hi @aabha123 ,

Try to use the unpivot columns.

Here's the M codes:

let
    Source = Excel.Workbook(File.Contents("\\filepath\testdata_ROI.xlsx"), null, true),
    #"New (3)_Sheet" = Source{[Item="New (3)",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"New (3)_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Initial", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Current", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Scenario", type text}, {"Cost_Type", type text}, {"Cost_Category", type text}, {"Cost_Detail", type text}, {"1/1/2022", Int64.Type}, {"1/1/2023", type number}, {"1/1/2024", type number}, {"1/1/2025", type number}, {"1/1/2026", type number}, {"1/1/2027", type number}, {"1/1/2028", type number}, {"1/1/2029", type number}, {"1/1/2030", type number}, {"1/1/2031", type number}, {"1/1/2022_1", Int64.Type}, {"1/1/2023_2", type number}, {"1/1/2024_3", type number}, {"1/1/2025_4", type number}, {"1/1/2026_5", type number}, {"1/1/2027_6", type number}, {"1/1/2028_7", type number}, {"1/1/2029_8", type number}, {"1/1/2030_9", type number}, {"1/1/2031_10", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Cost_Detail", "Cost_Category", "Cost_Type", "Scenario"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Attribute", type date}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type2", {{"Attribute", #date(9999, 1, 1)}}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Errors", "Custom", each if [Attribute] = #date(9999, 1, 1) then "Current" else "Initial"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Attribute - Copy", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute - Copy.1", "Attribute - Copy.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute - Copy.1", type date}, {"Attribute - Copy.2", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"Attribute - Copy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Attribute - Copy.1", "Date"}, {"Custom", "Initial/Current"}})
in
    #"Renamed Columns"

After the steps, you got the date column and initial/current column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.