Forum Discussion
Date table - can't create relationship
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....
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....
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.