Dynamic Merging of Tables - With Ranges and IF statements

Hi JamesMcEwan ,

Firstly, +2 for the perfectly executed provision of example data.

Now, I usually advise to do EVERYTHING in Power Query, and to NEVER use DAX calculated columns. However, conditional merges is the one scenario where I break from this. There are a number of ways to do it in Power Query, but not one of them I've found so far comes even close to matching the performance of DAX for this.

Send both your original tables to your data model then, on your Accounts table, create a calculated column like this:

..reportKey = 
CALCULATE(
    VAR __compRow = VALUES(Accounts[CompanyID])
    VAR __locnRow = VALUES(Accounts[LocationID])
    VAR __acctRow = VALUES(Accounts[Account])
    RETURN
    MAXX(
        FILTER(
            Reports,
            Reports[CompanyID] = __compRow
                && Reports[LocationID] = __locnRow
                && Reports[AccountFrom] <= __acctRow
                && Reports[AccountTo] >= __acctRow
        ),
        Reports[ReportKey]
    )
)

You'll want to tweak a bit to handle your "*" location escape, but this should give you a solid starting point.

Give it a go and let me know how you get on.

Pete