Look up in power query based off conditions

[Anonymous]

Hello,

Is it possible to do a look up in power query based off a condition?

for example I have a table I want to join in power query joining on [customer_id] but I want to add a condition: 

Join on [customer_id] when [islatest] = 1

Is this possible?

Thank you!

[Bizualisation]

Hiya - might help if you post some sample data?

Just from what you've posted, you could try filtering the second table to only show [islatest] to 1, then merging them?

If you still needed the second table with all items, you could duplicate the table, then filter it and merge on that, to preserve the original, unfiltered table in your data model.

[Anonymous]

I do still need the second table not to be filtered. i have thought of duplicating the table and merging as well but I wanted to make sure there was not another way 

[Bizualisation]

In PowerBI there are usually multiple ways to any solution but I think duplicating and merging would probably be the easiest approach - is there a reason you would like to avoid using that technique?

[Anonymous]

@Bizualisation no reason other then I wanted to keep it as clean as possible, but I will use that method then 

[amitchandak]

@Anonymous , I have not tried but see if table.join can help  https://docs.microsoft.com/en-us/powerquery-m/table-join

Bit of code here too https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query

[Anonymous]

@amitchandak  thank you!

[Bizualisation · Accepted solution]

If you duplicate the second table then merge it into the first (not creating a new table) and then disable the load (right click on the query in the editor and uncheck enable load) then your model will end with two tables as you wanted and won't load any extra data in, which might help you keep your model nice and clean!

Hope that has helped you!