Forum Discussion
Data modeling financial statements
Hi all,
I have a question on how to extract financial statement. For example, my profit and loss statement is being recorded in excel files therefore I suppose it is not Power BI friendly enough. May I check if there is any way to extract the information into power BI without having the users (accounts executives, etc) to duplicate the info into a proper rows and columns format?
Thanks in advance for the answers.
Regards
Power BI Desktop allows you to import data from Excel files, as well as manipulate the data during the import to make it into a 'rows & columns' format.
When you say 'Get Data' in Power BI Desktop, choose Excel and choose the file. After it connects, it will come up with a Navigator window with a list of sheets to import. Check the sheet(s) you care about, but rather than click 'Load' which will load the data as-is, click 'Edit'. This will bing up the query editor (better known as Power Query) which lots of options to delete rows, pivot/unpivot, replace values, filter the data, add calculated columns and such like. It's a very powerful tool that can replicate most things you would do manually to covert data into a proper rows & columns format.
The main prerequisite is that each file has to be formatted consistently. If each file is completely different, well, that could be a problem.
Hope this helps.
3 Replies
Power BI Desktop allows you to import data from Excel files, as well as manipulate the data during the import to make it into a 'rows & columns' format.
When you say 'Get Data' in Power BI Desktop, choose Excel and choose the file. After it connects, it will come up with a Navigator window with a list of sheets to import. Check the sheet(s) you care about, but rather than click 'Load' which will load the data as-is, click 'Edit'. This will bing up the query editor (better known as Power Query) which lots of options to delete rows, pivot/unpivot, replace values, filter the data, add calculated columns and such like. It's a very powerful tool that can replicate most things you would do manually to covert data into a proper rows & columns format.
The main prerequisite is that each file has to be formatted consistently. If each file is completely different, well, that could be a problem.
Hope this helps.
Solved it. Thanks for your help!
Hi, one option is to use an existing Profit & Loss dashboard template as a starting point rather than building the whole report layout manually. This one may be worth checking out: https://vidi-corp.com/profit-and-loss-dashboards/
