Union and if with value present in other table Power BI

Question

I have 3 tables.

The first one is a product table

The second one is also a product table, but some product are also present in the first one.

And finally, I have a sales table.

I would like to union the two product tables into a new one and if one product is in 'Sales', then add a new column with the name of the client.

This kind of results.

I want to do it in Power BI, but I can't find a solution to do it in Query Editor.

Any idea?

Thanks.

Answer 1

Here I assume ID is the primary key across the tables.

First, you can append (i.e. union) the two Product table into one:

Then, remove duplicates on the ID column:

Now, you can merge (i.e. join) the query with Sales table on ID column:

Expand the Sales table and select only the ClientName column:

And finally, replace the null value with Unsold:

(Yes, it's weird that the string null means NULL but it works this way)

Final result:

Full M code for your reference:

let
    Source = Table.Combine({Product, Product2}),
    #"Removed Duplicates" = Table.Distinct(Source, {"ID"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Duplicates",{"ID"},Sales,{"ID"},"Sales",JoinKind.LeftOuter),
    #"Expanded Sales" = Table.ExpandTableColumn(#"Merged Queries", "Sales", {"ClientName"}, {"ClientName"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Sales",null,"Unsold",Replacer.ReplaceValue,{"ClientName"})
in
    #"Replaced Value"