Forum Discussion

GaëlleFromGers's avatar
GaëlleFromGers
Regular Visitor
3 years ago
Solved

Nested Join with default key if null values

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...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

     

v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

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's avatar
GaëlleFromGers
Regular Visitor
3 years ago

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.