Forum Discussion
best practice for Data Model (connecting tables)
Hi,
Last months I was creating my first PowerBI report which is working, but I would like to improve the data model/flow for which I need some advice.
The report is basically showing an overview of all projects (excel) , actual cost (sap) and the forecasted cost (excel).
I have basically 2 main tables
- One table with all transactions and actuals (connection with SAP)
- One table with a list of all projects and their latest forecast (excel file)
The tables are connected with a SAP code.
Now the problem is that it could happen that a project is not not created yet in SAP and that there is no SAP code. Hence, I can not create a connection with SAP and EXCEL (one-to-many).
Is there a way to solve this? Or what is the work-around?
Regards
3 Replies
Hello Anonymous ,
You need to have a code in the sap data that reference that this transaction for this project so you could match the data with each project, or else how would you know that this data is for this project. so you need to have a foreign key to do a relationship.
And like generally, the best for data modeling is the star schema model https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Hello
what you can do is create a table using powerQuery that have the union of the SAP code of both tables
next remove the duplicates
on your model connect this table to the other 2 tables using 1 to many relation
the new table will e able to filter both table
