Custom Column to Lookup Multiple Fields in Other Table and Return Match within Date Range

[ChelseaCL]

Hi guys, thanks for your help.

I have a record of transactions: people accessing software and the date on which they accessed it.  On a separate table, I have "contracts."  This table includes the company name, software name, and the start and end date of the product's contract.  I need to be able to match these individual transactions to which contract they belong to so I can do other transformations.  Basically, I'm looking to create a custom column in the transaction table that gives the index number of the contract.  This is, in effect, to account for the fact that the transaction records have no link to the contracts. x.x

In order to find out which contract the transaction belongs to, I need to look at the transaction's company name, software name, and the date of the transaction.  I want to look up the contracts table and return the index number of the contract that has a matching company name, software name, and... fits within the contract period.  That's been tricky.

So for example, John Smith from Cool Company used Software A on 12/4/2022.  I'd like to lookup the contracts, see which contract matches "Cool Company," Software A, and the right start and end date (say it was the contract from 3/1/2022-3/1/2023), and return that index number.  

At first, I thought I might have to merge these tables, but the records are completely different, and as far as I know, I can't merge them properly since I don't think I can merge them into the correct contract period.  Like, it would just go into any random record, not the one with the correct contract start and end date.  I don't see an option to merge with some kind of formula.

I didn't realize at first that there's both DAX and Power Query going on in Power BI, so I had posted a request for help with the DAX.  Someone responded with the below, so maybe there's a way to transform this into M query?  I haven't had much luck so far.  

Thanks!

Calc column on Transations:

Contract Index =

VAR _startdate =

    LOOKUPVALUE (

        Contracts[Start Date],

        Contracts[Company], Transactions[Company],

        Contracts[Software], Transactions[Software]

    )

VAR _enddate =

    LOOKUPVALUE (

        Contracts[End Date],

        Contracts[Company], Transactions[Company],

        Contracts[Software], Transactions[Software]

    )

VAR _indates = [Accessed] >= _startdate

    && [Accessed] <= _enddate

VAR _indexnumber =

    LOOKUPVALUE (

        Contracts[Index Number],

        Contracts[Company], Transactions[Company],

        Contracts[Software], Transactions[Software],

        Contracts[Start Date], _startdate,

        Contracts[End Date], _enddate

    )

RETURN

    IF ( _indates, _indexnumber, BLANK () )

[ChelseaCL]

Thanks, didn't realize chat gpt was so sophisticated!  Still working out various kinks, but this was a great starting point.

[rubayatyasmin · Accepted solution]

Hi, @ChelseaCL 

a quick conversion from gpt

go to add a custom column then paste this code below. Please adjust the Previous step name first. Or it will show error. 

= Table.AddColumn(#"PreviousStepName", "Contract Index", each 
    let
        _startdate = List.First(
            Table.Column(
                Table.SelectRows(Contracts, 
                    each [Company] = [Company] and [Software] = [Software]
                ), "Start Date"
            )
        ),
        _enddate = List.First(
            Table.Column(
                Table.SelectRows(Contracts, 
                    each [Company] = [Company] and [Software] = [Software]
                ), "End Date"
            )
        ),
        _indates = [Accessed] >= _startdate and [Accessed] <= _enddate,
        _indexnumber = List.First(
            Table.Column(
                Table.SelectRows(Contracts, 
                    each [Company] = [Company] and [Software] = [Software]
                ), "Index Number"
            )
        )
    in
        if _indates then _indexnumber else null
)