Forum Discussion
Merge table with a condition with power query
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
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.
4 Replies
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 .Hi,
Can you check the post again please
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.
