Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
aabha123
Regular Visitor

Data import into Power BI for complex Excel sheet

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:

aabha123_0-1659363276789.png

@PowerQueryFTW @dataimport @Anonymous 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

vstephenmsft_1-1659606458851.png

 

 

 

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.

 

View solution in original post

2 REPLIES 2
aabha123
Regular Visitor

Thank you for the help. This works.

Anonymous
Not applicable

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.

vstephenmsft_1-1659606458851.png

 

 

 

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.

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors