Joining tables - data missing

[LouiseB1409]

Hello,

I am trying to join two data tables - actuals and forecast, with the actuals table being the source (or master or whatever), with the common columns being account, parent account, grandparent account, cost centre, period. When i create a table to show actuals vs forecast data from the forecast column is misisng becasue there isn't anyhting for actuals. Is there a way to pull through forecast data without actuals data being present?

Example below, there is no data in actuals for grandparent account 3 but there is in forecast. i am getting the first table but need the second.

Grandparent [Actuals Table]	Actuals [Actuals Table]	Forecast [Forecast table]
1	1255	654
2	1253	236
4	57862	1456

Grandparent [Actuals Table]	Actuals [Actuals Table]	Forecast [Forecast table]
1	1255	654
2	1253	236
3	0	54883
4	57862	1456

[Ghhousuddin]

Yes, you can use a LEFT JOIN to include the forecast data even when there is no corresponding actuals data. Here's how you can modify your join to achieve this:

1. Open Power Query Editor and select the "Actuals" table.
2. Select the "Merge Queries" option from the "Home" tab in the ribbon.
3. In the Merge dialog box, select the "Forecast" table as the second table to merge.
4. Select the columns that you want to join on (account, parent account, grandparent account, cost centre, period).
5. In the Join Kind section, select "Left Outer (all from first)" as the type of join to perform.
6. Click OK to create the merged table.

This will create a new table that includes all the rows from the "Actuals" table, as well as any matching rows from the "Forecast" table. For any rows where there is no corresponding data in the "Actuals" table, the values in the "Actuals" columns will be shown as blank, but the values in the "Forecast" columns will still be included.

To replace the blank values in the "Actuals" columns with 0, you can use the following steps:

1. Select the columns in the merged table that contain the "Actuals" data.
2. Click on the "Replace Values" option in the "Transform" tab in the ribbon.
3. In the Replace Values dialog box, enter a blank value in the "Value to Find" field, and enter 0 in the "Replace With" field.
4. Click OK to replace the blank values with 0.

Once you have completed these steps, you should have a merged table that includes both actuals and forecast data, with 0 values in the "Actuals" columns for any rows where there is no corresponding actuals data.