best practice for Data Model (connecting tables)

[YBZ]

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

[YBZ]

ok thanks -

so basically I can create a dummy sap code and merge all sap codes in one table and remove the duplicates.

[CharbelArmaleh]

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 

[Idrissshatila]

Hello @YBZ ,

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 👍

Follow me on Linkedin