Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @Data
Solved! Go to 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.
Thank you for the help. This works.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.