Relate tables based on date in column header

[Jamie_Macca]

I have 2 tables one showing Budgets for different spend types for each month and an 8 month total forcast. e.g.

The other table shows actual spend that has a Spend type (same as the budget spend types), amount spent, name of spend and a Date feild showing when a spend was made I want to be able to show Actual spend against the budget per monthly target.

The issue I have is that I cannot relate the Date of the actual spend to the Month Date in the header of the budget table so I can filter on for example Oct-23 target and bring back all acutal spends from the Acutal spend table to plot against the budget.

 Do I need to go into the data and add Date feild for each spend type in the budget? similar to the below to allow me to filter on Month? or is ther an easier way?

[Jamie_Macca]

@Adescrit Thank you so much, I've never used the Unpivot Function.

I thought there must be an easy/repeatable way of doing it. 

Unsure what the date dimension table is but can look that up.

Thanks Again!

[Adescrit · Accepted solution]

Hi @Jamie_Macca 

You would need to transform your table so that the dates are in rows, similar to the example you have provided at the end of your post. Fortunately you don't need to do that in the raw data, you can do that in Power Query.

1. Open Power Query by clicking on Transform Data.
2. Select the Date columns from the budget table, e.g. Oct-23, Nov-23. You can use ctrl or shift to select multiple columns.
3. Then in the Menu ribbon go to Transform - Unpivot Columns.

Best practice would then be to have a date dimension table that filters both the budget table and the spend table, rather than relating the budget table directly to the actual spend table.