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

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
sergebezborodov
New Member

Create relationship between with two columns

Dear all!

 

I have diffrent tables with data, format something like

ID | MsId | Date | [stat values]

 

Date and MsId is not uniq each other, but unique both.

How can I link my tables by two columns MsId and Date?

5 REPLIES 5
imkc1127
New Member

I just come up with the same problem of joining composite keys and looking around for a solution. 

 

Sad to see that the answer is "doing some workarounds"...... Cat Sad

 

Even the old-aged Microsoft Query is doing better on this..... I'd better go for the MS Query...... Smiley LOL

 

@imkc1127 its not a workaround its by design.

 

Schema design for data warehousing is different to that of traditional databases and is intended to keep things easy to understand for users creating reports and also faster during processing. A database schema will typically be more complicated because it will need to handle data entry and integrity.

 

Merging columns to form unique primary keys should just be part of the process that imports data if using a warehouse. If I'm reporting direct from a traditional db with multiple joins required I either set up views in the database to pre-process or use power query like @LarsSchreiber suggests. It basically shifts the need to have a deep understanding of the database away from the end user creating reports.

Before using MS Query for joining the tables together, really give Power Query a chance. MS Query and PQ are not even from the same Universe, especially when it comes to transformation in later jobs 😉

 

 

LarsSchreiber
Responsive Resident
Responsive Resident

hi @joserq,

 

as @aparodi suggested, concatenation of these two columns can be a solution. If you don't need both tables in the data model, you can do that job using Power Query and just import the resulting table into the data model. When you click merge  you can click the key columns while holding the control key on you key board. This way you are using more than one column per table when you merge your tables. 

 

Maybe this is helpful to you.

 

Regards,

Lars

aparodi
Frequent Visitor

Hi,

 

You can not use composite key.

 

I suggest you concatenate Date (parse to string or something) and MsId attributes, and use this as PK.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

October NL Carousel

Fabric Community Update - October 2024

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