Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I would like to know how can I do a data model in Power Query with a particular Excel file. The Excel contains multiple sheets and it has dynamic tables that call different sheets making calculations from multiple dynamic tables. So for example: one table has a cell that contains a sum of 6 more cells from a different sheet in the same Excel file. And those 6 more cells contain more calculations that call other cells in different sheets.
I need to develop a model that can contain hierarchical agrupation table of those multiple calls to different cells to perform a granular analytical approach.
Can you help me? Thank you in advance
Solved! Go to Solution.
It's most likely possible to transfer the whole business logic of your Excel file to PowerBI, but you will have to translate all the "dynamic tables" transformations (M in Query Editor should work well) and calculations to (DAX) from scratch.
So it's less about rebuilding the Excel file, it's more about creating a new tabular model with appropiate measures, joins, etc. that meets your business requirements
It's most likely possible to transfer the whole business logic of your Excel file to PowerBI, but you will have to translate all the "dynamic tables" transformations (M in Query Editor should work well) and calculations to (DAX) from scratch.
So it's less about rebuilding the Excel file, it's more about creating a new tabular model with appropiate measures, joins, etc. that meets your business requirements
Thank you very much for your reply. It's a bit daunting to code M from scratch but I have no other option.