Forum Discussion

New Member

3 years ago

Solved

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 sale...

Show and Tell

v-jingzhang
3 years ago
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.
Merge table with a condition with power query.pbix52 KB

v-jingzhang

Community Support

3 years ago

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.

Merge table with a condition with power query.pbix52 KB