Merge 2 tables that both have some null values

[Anonymous]

Hi all, 

I wonder if someone can help me with merge queries. I am using Power Query in Excel and I would like to know the best way to do this task please. I have two similar tables (which I have simplified for this example.) The main difference between the tables are the columns 'Result' and 'Target'. Test file here: https://www.dropbox.com/s/9q64s9s25eq6qlo/merge-test2.xlsx?dl=0

Targets Table: Theme | Outcome | Output | Organisation | Period | Indicator | Result

Targets Table: Theme | Outcome | Output | Organisation | Period | Indicator | Target

I want to merge these two tables so that Result and Target columns are matched by the columns ‘Indicator’ and ‘Period’, since this will be unique. For example, I would want in the same row:

[period] 2018A [indicator] Number of NGOs trained [result] 10 [target] 5

And the new table would look like this: 

Theme | Outcome | Output | Organisation | Period | Indicator | Result | Target

The issue for merging is that for some early periods there are no Targets in the Targets table, and for some periods there are no values for the Results table (since Targets get set before results are reported). I understand that the choice of JoinKind is really important. I want to include:

- Matched pairs of Results table and Targets table (on Indicator and Period)

- Non matches, including values from all other columns but with Target column null

- Non matches, including values from all other columns but with Results column null

So I run 'merge queries as new' and choose the columns 'Indicator' and 'Period' to join on and chose Full Outer (all rows from both). Then I click the double arrows in the header to expand the table, and select only ‘Target’ to expand. This does successfully return matches and Results table ‘Results’ column values as expected. But for Targets table ‘Target’ column values without matches I only get rows with Target values and null values in all the other columns.

I have thought of two ways to resolve this issue, but I doubt these are the best or proper ways:

1) I realise that I can expand all columns and not just Targets. This makes a long table with duplicated columns appended with .1 after the header name. Eg Theme.1, Outcome.1 etc . I realise I can write some codes so that the null values in Theme are filled with the values from Theme.1 . It’s a bit laborious as I have more columns that I presented in this example, but it works. Eg code is lines like this:

#"Replaced Theme nulls" = Table.ReplaceValue(#"Renamed Columns", each [Theme], each if [Theme]=null then [Theme1] else [Theme], Replacer.ReplaceValue,{"Theme"}),

2) The other way I tried is as follows. I did a merge with JoinKind.LeftOuter, so that I get matches plus all rows of Results table. Next, I have made another merge and made Targes table the first source table chose JoinKind.LeftAnti to get only the rows from the Targets table that do not have a match. Then I can do a third step and combine these two steps with Append. A bit ugly but it works.

So these were my experiments, but how is this actually supposed to be done before I do this with a big dataset?

[Stachu · Accepted solution]

I'd suggest creating a helper query, appending all the key columns from Targets and Results (that way you have all the necessary details), and removing the duplicates

then I'd do merge on that table with both of them

Other option is to append the both tables and then do Group By on the dimension columns and summing the values (to make the combination unique again)

[Anonymous]

Hi @Stachu I tried both of your suggestions and both worked perfectly. Thanks a lot. 

For anyone interested in the steps, this is what I did. For the first suggestion, I appended both tables and deleted the 'results' and 'targets' columns, and then removed duplicates. This gave me a table that was like a 'bucket' with all the columns I needed and unique Periods and Indicators. So when I made my merge queries it was like dropping my results and targets into the query. Very easy conceptually. 

For your second one, that was great for just avoiding merging completely, and just appending. Conceptually my brain has to bend only slightly further when making the groups. After appending both tables, I selected all the columns I wanted to keep in the 'advanced' group by list (excluding the columns with results and targets). Then for the new column name I made one with the sum of result (since there is only one unique entry its just the sum of itself) and added another aggregation with the sum for target. 

Both suggestions got the same results as what I tried myself in my test, and I would say both of Stachu's suggestions were better. 

Thanks again!