Hi,
I am doing the analysis for some numbers within the balance sheet financial statement manually.
I've included the sample files, one includes the query, and the other one is without it.
The current situation is that I am adding an extra column in the source for each year I want to analyze and do the calculation I need.
Each number is divided by TOTAL ASSETS and displayed as a %.
I want to do it automatically in power query.
I've created the query, and unpivot all columns I need, then I split the column, change its type to date, then replace its item inside but do not know how to divide all items for each row in a column by a value within the same column and it is titled TOTAL ASSETS (this item included in the Detailed item in financial statement column).
So how to add a column and add a formula inside each row that divides the value of the cell by the value of TOTAL ASSETS in the same column?
Is it possible to be done?
Thanks
Hi mgbsher,
Here is your query with a new custom step:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"Company", type text}, {"Financial Statement", type text}, {"Main item in financial statement", type text}, {"Detailed item in financial statement", type text}, {"2016", Int64.Type}, {"2017", Int64.Type}, {"2018", Int64.Type}, {"2019", Int64.Type}, {"2020", Int64.Type}, {"2021", Int64.Type}, {"2016 VerticalAnalysis", type number}, {"2017 VerticalAnalysis", type number}, {"2018 VerticalAnalysis", type number}, {"2019 VerticalAnalysis", type number}, {"2020 VerticalAnalysis", type number}, {"2021 VerticalAnalysis", type number}}),
Unpivot = Table.UnpivotOtherColumns(#"Changed Type", {"No.", "Company", "Financial Statement", "Main item in financial statement", "Detailed item in financial statement"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(Unpivot,{{"Attribute", "Year"}, {"Value", "Amount"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Year", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Year.1", "Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Year.1", type date}, {"Year.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"BalanceAmount",Replacer.ReplaceValue,{"Year.2"}),
Renamed = Table.RenameColumns(#"Replaced Value",{{"Year.1", "Year"}, {"Year.2", "BalanceAmount/ VerticalAnalysis"}}),
Custom1 = Table.FromRecords(Table.TransformRows(Renamed, (rec)=>Record.TransformFields(rec,{{"Amount", each if rec[Detailed item in financial statement]<>"Fixed Assets" then _/(Table.SelectRows(Renamed,(x)=> x[#"BalanceAmount/ VerticalAnalysis"]="BalanceAmount" and x[Detailed item in financial statement]="Fixed Assets" and x[Year]=rec[Year])[Amount]{0}) else _}})))
in
Custom1
Thanks a lot and much appreciated your valuable time helping me.
I've created a detailed video for the case so you can have a better idea.
https://www.youtube.com/watch?v=DT2ZwB1XyLE
Thanks a lot.
I hope you may have time to check the video.
Thanks
- later edit -
I've tried the code with several adjustments but can not get it working.
I am sure it is my mistake as I am still learning the DAX and the M Language.
Please accept my apologies if I said something wrong or impolite.
Thanks,
Mohamed
Hi,
The last step in the query I provided wrongly refers to Fixed Assets instead of Total Assets as the denominator.
The step should look like:
= Table.FromRecords(Table.TransformRows(Renamed, (rec)=>Record.TransformFields(rec,{{"Amount", each if rec[Detailed item in financial statement]<>"Total Assets" then _/(Table.SelectRows(Renamed,(x)=> x[#"BalanceAmount/ VerticalAnalysis"]="BalanceAmount" and x[Detailed item in financial statement]="Total Assets" and x[Year]=rec[Year])[Amount]{0}) else _}})))
Note that the column name contains 2 spaces: "Total Assets", therefore the errors you got might be related to this column name.
However, power query is not the right place to make such calculations, you should write measures instead in DAX.
Here is an example YOY%: https://carldesouza.com/power-bi-year-over-year-using-sameperiodlastyear/
Thanks a lot for your valuable time.
I want to follow your advice.
However, power query is not the right place to make such calculations, you should write measures instead in DAX.
If I followed your advice, then I will not go for power query from the beginning!?
Or do you mean to only do everything in the power query except for the calculation to be done in DAX?
Also, does making a measure require adding data to the data model?
I also do not use Power BI yet, and everything is within Excel only.
Power BI has the same tools: Power Query and Power Pivot, however there are some functions in PBI not available in Excel.
The source data should not have totals or manual calculations, I prepared an example of how you should have the source data.
I used PQ only to convert the source data into a tabular structure, from that point I just used DAX to replicate your "Vertical Analysis"
Just another note:
a P&L report is a complex subject. To create reports based on a chart of accounts multilevel hierarchy is a challenge, especially if you're not familiar with data models.
Here is an article that might bring some light on these challenges:
Thanks a lot for your valuable time reading my messages and helping me.
I will follow your advice, my friend.
Thanks 🙂
