Date table - can't create relationship

[lekkerbek]

Hi,

* I have a date table that has every date between 1-1-2000 and 31-12-2025 and added various columns like 2016/Q3, etc.

* I have a different data set where I have around 30 shops and the value of their purchases in a specific quarter, so

column A = shop name

column B = purchases in 2016/Q1

column C = purchases in 2016/Q2

etc

I tried to link the date table with the other table, but can't do that because neither tables have unique values (obviously).

How can I resolve this? 

[CahabaData · Accepted solution]

in seeing your column B & C to be differing quarters - and then 'etc' - I am assuming you have these time periods side by side going further in time; and so there is no way to consider a join relationship to a Date table with this structure.

You need to re-shape your data model so that your data table has fields: ShopName, Amount, TimePeriod

...and then all the data itself in rows beneath

after this, for a join, you would need a reference table for the time period by making a new table from a Distinct list of the time periods.  And then join both tables to it.  however whether or not this is needed depends on what type results you seek ultimately in your reports & visuals as one may be able to achieve the results without this new table & join....

[lekkerbek]

Sorry, forgot to mention that I unpivotted the columns, so the "quarter" will eventually be one column.

I'll try your suggestion of adding another table and connecting both datasets to that table.

Thanks.