Join table based on the first hit and unknown value if not found

[amien]

product	value
1	155
2	160
3	170
4	100

product	code
1	A
1	B
2	C
3	D

I want to add the code field to the first table. As you can see, where product =1 there are 2 results, and where product = 4, there is  not result. I would like toi have the following result:

product	value	code
1	155	A
2	160	C
3	170	D
4	100	Unknown

[Anonymous · Accepted solution]

If I've understood this correctly, you can do this with Power BI Desktop quite easily.  It's the Power Query (Queries) part of Power BI Desktop you need.

If you have the two tables as you have outlined, you can do a Merge using a 'Left Outer' join based on the Product column.  You can then simply replace nulls with 'Unknown' and remove the duplicates (but based on the Product and Value column, not the code column).

I managed to acheive this just using the GUI but here's the resulting 'M' code:

let
    Source = Excel.Workbook(File.Contents("C:\@OneDrive\Desktop\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"product", Int64.Type}, {"value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"product"},Sheet2,{"product"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"code"}, {"code"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded NewColumn",null,"Unknown",Replacer.ReplaceValue,{"code"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Duplicates", {"product", "value"})
in
    #"Removed Duplicates"

Regards

Sacha

[amien]

Thanks both for the feedback

[leonardmurphy]

A couple of questions that I think would help answer this...

1. What is the source for your data (Excel, SQL, etc) and how are you bringing it into Power BI? Is it possible to edit the query bringing the data into your model so that it only brings in a single product in the 2nd table?

2. Is there some kind of implicit ordering to the 2nd table that means product 1 is always code A? Is it alphabetically the first code for example? Can a column be added to reflect this ordering? 

DAX works on the basis that your data has no order. You can order your tables in any way you like and DAX will work the same. Which is exactly what you want in most cases, but in a case like this, it means you have to provide some kind of ordering mechanism or filter your table before it gets to the data model. 

[amien]

Hi leonardmurphy,

1. Source is Excel, SQL, can be anything. Yes, i could do a Group by, with Max/Maxstring or something like that. 

2. There is no specific order. First hit is ok

I come from QlikView, and there i have this statement : ApplyMap

https://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Perhaps i'm looking for the LookUpValue statement?

[leonardmurphy]

I haven't tried it, but you might run into problems with LOOKUPVALUE, because there are 2 different codes for the same product that you're trying to return. 

If you can eliminate the duplicate products before or as you bring them into your data model, that's definitely the best option.

The alternative is a relatively complicated DAX formula that would mimic the GROUP BY and MAX functionality as the measure is calculated. LOOKUPVALUE in conjunction with a SUMMARIZE statement. It would be slow because it would have to calculate it in the model (vs. being pre-calculated).

I'm not familiar with Qlik, but Power BI has 2 distinct phases to it:

1. Bringing the data in (the ETL part known as Power Query or Query Editor)

2. Modeling the data (where you define relationships and measures, etc.)

Power BI has a ton of functions that help you transform data as it is loaded into your data model (which include eliminating duplicates, pivoting, unpivoting, etc.) so that modeling the data is as straight-forward as possible.

In the Query Editor, there's a button on the Home ribbon called 'Remove Duplicates'. If you edit the query as you load your data, click on the Product column of the 2nd table and say 'Remove Duplicates', it will get rid of your 2nd product 1. (Or if you do your GROUP BY in your SQL statement, that would work too).

Then a regular relationship between table 1 and table 2 would suffice without having to use LOOKUPVALUE or write any complicated formulas at all.