Combine 2 tables for variance

[BarryWhitelaw]

Hi

I have a spreadsheet that has 2 tabs.  Each tab has a Month column, formatted as Text. Each tab has a value column formatted as currency.

When I load these 2 tabs into PowerBi Desktop, I can show each as a table summarising the monthly spend.

I need to be able to combine these into 1 table, showing the months but then the spend for each table, ie:

Month       Year 1       Year 2

January    £1000        £1050

February  £1000        £1070

March      £1000        £1090

I am adding a New Table via the Module header, then entering code to combine. Constantly getting the same error:

      An incomatible join column, ("{Month}) was detected. 'NATURALLEFTOUTERJOIN' doesn't support joins by using columns with  different data tytpe or lineage.

I've tried to work out and google why this isn't working and not getting anywhere.

Anyone any ideas?

Have enclosed screenshot to show what I'm doing.

thanks

Barry

(Hopefully I've not broken any forum rules posting this, new to the site (and PowerBi)

[johnt75 · Accepted solution]

If you want a quick and dirty solution, use Power Query to merge the 2 tables on the month column. Expand the merged column to pull in the value from the second table, and untick the "Enable Load" option. That will load one table into your model with the month name and both values.

If you want a cleaner, more extendable method, use Power Query to transform both sheets, appending the appropriate year to both month name columns. Set the type of the column to date if it hasn't been automatically detected.

Append the queries and again untick the "Enable Load"  option on the second table. You will now have 1 table with 2 columns, a date and a value.

Create a date table, marked as a date table, and link it to the combined table.  There's plenty of articles and videos about creating a date table.

Use columns from the date table in your visuals, and you can break the values down however you need to .