Checking if value in table 1 exists in table 2, and if not inserting that value into table 2

[byr10112]

I have two separate tables, where one table has sales targets of accounts and the other has the sales amounts. Some accounts may have sales targets but no sales, thus they do not appear in the realized sales table. Is there a way for me to check, via power query, whether an account with a target in table 1 exists in table 2? If it doesn't exist, I would like to manually insert this account into table 2 with sales amounts staying as blank. Below are examples of my two tables:

Table 1 (Target)

Client ID	Client Name	Target
1001	ABC Inc	200000
1002	BCG Inc	150000
1003	MNH Inc	180000

Table 2 (Sales)

Client ID	Client Name	Target	Sales
1001	ABC Inc	200000	180000
1002	BCG Inc	150000	175000
1004	XYZ Inc	160788	200000
1005	POT Inc	1008256	150344

As you can see, Client 1003 doesn't exist in Table 2. I would like the end table to look like this:

Client ID	Client Name	Target	Sales
1001	ABC Inc	200000	180000
1002	BCG Inc	150000	175000
1003	MNH Inc	180000
1004	XYZ Inc	160788	200000
1005	POT Inc	1008256	150344

Is this possible via power query?

[tackytechtom]

Hi @byr10112 ,

Here another wayu of doing it via a FULL OUTER JOIN:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough). Note: My table is called Table33a and Table33b in the example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUXJ0clbwzEsGsowMQADIMLQAM2J1wIqMgCJOzu5QRYamMEXmpkiKTIAiEZFRMEVmBuYWFggjoYpMgSIB/iEwRQYGFkamZhAzjU1MlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client ID" = _t, #"Client Name" = _t, Target = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client ID", Int64.Type}, {"Client Name", type text}, {"Target", Int64.Type}, {"Sales", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Client ID"}, Table33a, {"Client ID"}, "Table33a", JoinKind.FullOuter),
    #"Expanded Table33a" = Table.ExpandTableColumn(#"Merged Queries", "Table33a", {"Client ID", "Client Name", "Target"}, {"Table33a.Client ID", "Table33a.Client Name", "Table33a.Target"}),
    #"ISNULL on Client ID Col" = Table.ReplaceValue(#"Expanded Table33a",null, each _[Table33a.Client ID], Replacer.ReplaceValue,{"Client ID"}),
    #"ISNULL on Client Name Col" = Table.ReplaceValue(#"ISNULL on Client ID Col",null, each _[Table33a.Client Name], Replacer.ReplaceValue,{"Client Name"}),
    #"ISNULL on Target Col" = Table.ReplaceValue(#"ISNULL on Client Name Col",null, each _[Table33a.Target], Replacer.ReplaceValue,{"Target"}),
    #"Removed Columns" = Table.RemoveColumns(#"ISNULL on Target Col",{"Table33a.Client ID", "Table33a.Client Name", "Table33a.Target"})
in
    #"Removed Columns"

Let me know if this solves your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[artemus · Accepted solution]

This should be as simple as doing a merge with table1 and table 2 using a left join. After you do the join, you will want to only retain columns in table 2 that are not in table 1.

[tackytechtom]

Hi @byr10112 ,

How about this:

Take the Sales table and click on append:

Append the target table:

Remove duplicates:

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/