Nested Join with default key if null values

[GaëlleFromGers]

Hi all,

I have a referential that I would like to merge in a table table using multiple columns.

In order to avoid to duplicated lines in my referential, I would like my nested join to take into account null vlaue in my key column from referential for any case in not specify.

In this simplified example below, the nested join function is based on columns Fruit and Color.

The price column from referential is null in the result table because their no null color in the data table.

I would like the price to be by default the value corresponding to null value in referentiel if it is different than green.

See desired result table.

Is anyone knows if it is possible? 

Thanks in advance to the community for your help,

Gaëlle.

[GaëlleFromGers]

One last question on the subject, what if I have 2 conditions with the same specificity?

For instance Color and Region?

Thanks in advacne for your help,

[v-jingzhang]

Hi @GaëlleFromGers 

You just need to modify the Merged Queries step to base the nested join operation on Fruit and Region.  

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[GaëlleFromGers]

Actually, Region shoudl work like tyhe color: given region ou default value if not mentionned in the referential. 

I thought it would be another parameter in the custom step?

[v-jingzhang]

Hi @GaëlleFromGers 

I'm sorry. You're correct. You can modify the custom column step similar to below one:

= let pColor = [Color], pRegion = [Region] in Table.First(Table.Sort(Table.SelectRows([Referential], each ([Color] = pColor or [Color] = "") and ([Region] = pRegion or [Region] = "")), {{"Color", Order.Descending}, {"Region", Order.Descending}}))[Price]

BR,

Jing

[v-jingzhang · Accepted solution]

Hi @GaëlleFromGers 

When merging queries, only base it on Fruit column. 

Then add a custom column with below code to get the prices. 

= let pColor = [Color] in Table.First(Table.Sort(Table.SelectRows([Referential], each [Color] = pColor or [Color] = ""), {"Color", Order.Descending}))[Price]

Remove column "Referential". 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[GaëlleFromGers]

Jing,

Thank you so much for your quick support.

I just had to modify the custom column as follow and in works perfectly:

= Table.AddColumn(Source, "Personnalisé", each let pColor = [Color] in Table.First(Table.Sort(Table.SelectRows([Referential], each [Color] = pColor or [Color] = null), {"Color", Order.Descending}))[Price])

Next time, I will attach my file to help 😉

Thanks again and have a nice day,

Gaëlle.