Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
YBZ
Helper III
Helper III

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 3
YBZ
Helper III
Helper III

ok thanks -

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

 

CharbelArmaleh
Resolver II
Resolver II

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
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.