Merge table with a condition with power query

[Islem]

Hi guys,

I have two table, the 1st contains customers with sales and date and the second contains sales responsible with assigned customer and date

how can I merge both tables to get the right sales person who dealed with customers in a specific period 

1st table 

2nd table 

 

Result should be the following 

 

 

[bhelou]

Hello , 

try to clean the data you want to combine to : meaninng to combine 2 tables or more they should have the same column name and sometimes same discription to be merged , 

clean the second table , split the columns then do the merge . 

if you can provide a sample with the data , ill try to solve it to you . 

[Islem]

Hi,

Can you check the post again please

[v-jingzhang · Accepted solution]

Hi @Islem 

You can first merge Table 2 to Table 1 by Customer column. This will add a [Table 2] column in Table 1. 

Then add a custom column with below code. This will get the responsible sales person in corresponding period. 

let _salesDate = [Date] in Table.Last(Table.Sort(Table.SelectRows([Table 2], each [Date] <= _salesDate), {"Date", Order.Ascending}))[Sales responsible]

After that, remove the [Table 2] column from Table 1. PBIX file has been attached at bottom. 

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

[Islem]

great solution, many thanks dear