Add fields by matching >=, greater than or equal to, in POWER QUERY

[STP_Brazil]

You have a model with 2 tables

TB_Codes

TD_Data

Considering that

CODE A Data = CODE A

COMPANY Data = COMPANY

SEASON Data >= SEASON

DATE Data >= DATE

Add in the table TD_Data the values corresponding to

PROCESS

DESTINATION

I need this solution in Power Query, not in DAX

[MasonMA · Accepted solution]

@STP_Brazil 

Hi there, 

Here's a Power Query transformation process with some dummy data based on your scenario, hopefully it would give you some ideas:) The data transformations are done mostly through UI and only with M where UI is not possible. 

Assuming 'TB_Codes' looks like

and 'TB_Data' below

You would need to perform a Cross-outer join as in below picture with the help of an additional column.

and filter out the rows with below M 

Table.SelectRows(#"Expanded TB_Codes", each 
    [CODE] = [CODE.1] and
    [COMPANY] = [COMPANY.1] and
    [SEASON] <= [SEASON.1] and
    [DATE] <= [DATE.1])

In the end you would have below after deleting unused columns.

FYI, this is just a demo of one of the solutions. And if your tables are large multiple matches are possible, you may need to Group by original row ID or combination of 'CODE/COMPANY/SEASON/DATE' and use Max of Date to keep the most recent matching row. 

You can find this demo file Here

[STP_Brazil]

Muchas gracias