Create relationship between with two columns

[sergebezborodov]

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?

[imkc1127]

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"......

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

[itchyeyeballs]

@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.

[LarsSchreiber]

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]

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]

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.