How to build a hierarchical data model from an Excel file with multiple interrelated sheets

[Anonymous]

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

[Stachu · Accepted 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

[Anonymous]

Thank you very much for your reply. It's a bit daunting to code M from scratch but I have no other option.