I aplogize beforehand if this questions turns out to be not specific enough. The issue is as following:

I have an excel file in which there are several sheets with lots of calculations (mostly financials). I have access to the same database from where raw excel file was downloaded. Now I want to repreduce the calculations and executive summary using Power BI getting the data from database directly (most likely using Direct Query mode). But I am not sure how should go about it? Should/can I use the existing Excel file to somehow copy the work that has been already done and just change the source to database? Or will I have to do it all over again? One main point when considering the above questions is whether Power BI will be able to do all the complex calculations done in Excel previously?

Via search I came accross to a few videos where they say we can upload Excel file into Power BI and then apply the same tables from database and finally using Advanced Editor change Excel tables sources to database. But thing is that database doesn't have the kind of tables I have in Excel (lots of changes and calculations are applied to the raw data that was downloaded from database). So I am not sure how this method can work.

CodePudding user response：

It sounds like you have a complex set of calculations and executive summary that were created in Excel using raw data from a database, and you now want to reproduce that work in Power BI using Direct Query mode to connect directly to the same database.

It is possible to use the existing Excel file to help you replicate the work that has been done in Power BI, but it may not be the most efficient or straightforward approach. One way to do this would be to upload the Excel file to Power BI and then use the "Import" feature to bring in the tables and calculations from Excel. However, this would require you to set up the data connections in Power BI to point to the database instead of the Excel file, and it may not be possible to do this if the database does not have the same tables as the Excel file.

Another approach would be to create the calculations and executive summary from scratch in Power BI using Direct Query mode to connect to the database. This would allow you to take advantage of the powerful data modeling and calculations capabilities of Power BI, and it would also allow you to create a more flexible and dynamic report that can be easily updated as the data in the database changes.

It's important to consider that Power BI can handle complex calculations, but it might require a different approach than the one you used in Excel. It's also possible that not all the calculations can be done in Power BI and you may have to keep some of the calculations in Excel. Power BI have a lot of formula's and function which can make it easy to perform complex calculations, you can use DAX (Data Analysis Expression) formulas and functions to perform calculations on your data in Power BI.