Skip to main content
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

Helper III
Helper III

best practice for Data Model (connecting tables)



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?




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.


Resolver II
Resolver II


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 



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


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

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors