Forum Discussion
Solved
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:
PowerQueryFTW @dataimport Anonymous
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.
2 Replies
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.
Thank you for the help. This works.
