Best approach to combining data with some matching rows and different columns

[seamie82]

Hi, 

What is the best approach to combining data from two tables (Actuals and Forecast in the example below), so both the Actual and Forecast field are available in the resulting table when some rows match and some don't.

Merging using a full outer join results in the following:

Would you then just create a new name field something like this:  

Taking the value from Name if it's not null or from Forecasts.Name if Name is null.

Resulting in this:

Which can obviosuly then be tidied up:

Is there a better way of achieving the same thing?

[AntonioM]

Hi @seamie82 ,

The way I would approach this is to unpivot both the Actual and Forecast columns, append the two tables together and then rename 'Attribute' to Type.

That would give you this table:

Then in your measures you can use

CALCULATE ( [measure] , Table[Type] = "Actual" )

or

CALCULATE ( [measure] , Table[Type] = "Forecast" )

to use the two different sets of values.

[amitchandak]

@seamie82 ,  Power query append.

Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Or common tables https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

[seamie82]

Thanks, I read this already: https://radacad.com/append-vs-merge-in-power-bi-and-power-query

If you used append though would you do something like this?

Adding a descriptive column to each source and then appending: