Looping through tables with lookup values testing/writing "yes" or "no" in a new column

[trondlk]

Hi there,

I have two tables in Power BI, where on is the facts table with at most about 500' transactions (A) in production, and the other being dimension table (B) with supplier contract info about 100 rows in total (each row is a contract).

Table A consist of several columns, like TransactionDate, SupplierNr and more. While table B consist of a few columns, like SupplierNr, DateFrom and DateTo.

In Power BI Query, one column in table B is also showing the aggregated table function, with the SupplierNr aggregated, and DataFrom and DateTo inside a table, ready for looping throught the table with some kind of M code.

Question:

How to add a new column in table A called "Agreement", witch tests and write «yes» or «no» for each matching SupplierNr in the two tables, testing each TransactionDate in table A also being in between DateFrom and DateTo of table B?

Not all SupplierNr are in table B either. For speed its not necessary then to test every TransactionDate in table A being in between DateFrom and DateTo in table B, when SupplierNr only is in table A and not always in table B.

There can also be more than one row for each SupplierNr in table B, as several contracts with same SupplierNr have different dates from and to. This means I need to be able to loop throught each SupplierNr in table B, with regards to possibly several rows in table B with DateFrom and DateTo values.

In the new row "Agreement" in table A either «yes» or «no» then should be written when refreshing table A. Where «yes» means contract in table B exist to the SupplierNr at TransactionDate in table A.

When no identic SupplierNr in table B, the column "Agreement" in table A should just say «no», and continue the testing of SupplierNr (and possibly TransactionDate) in next row of table A.

In Power BI Report then I can filter on «yes» and «no» from table A after updating. Okey, refreshing the report could take some minues with looping, but time is well worth a good result after all.

Only missing this in order to reach my goal with the Power BI report. Also I've tried hard to understand coding in M (Query), but don't make it work. Thanks in advance therefore, for anyone helping me out!

ps. Not possible to upload attachments as example of table inputs at this forum?

Cheers, Trond

[Jimmy801]

Good morning Trond

you can try this solution. I post you the M-code of two tables. One for transaction one for Supplier contracts

Here the code for tblContracts

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYvBCcAwDMR28duY85lCdzHZf40eBExS0EcIdVuaGzIEQUqIwLNleVv9OyrE9OuHpDKSW+Z/Zzm6/vUB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SupplierNr = _t, From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SupplierNr", Int64.Type}, {"From", type date}, {"To", type date}}, "DE-de")
in
    #"Changed Type"

here for the tblTransaction

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzDVNzIwMlLSUTJUitWJVjK0wBQy0DcwhgkZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TransactionDate = _t, SupplierNr = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionDate", type date}, {"SupplierNr", Int64.Type}}, "DE-de"),
    AddColumnValidContract = Table.AddColumn
    (
        #"Changed Type",
        "Availabe valid contract",
        (row)=> if Table.IsEmpty(Table.SelectRows
        (
            tblContracts, (table)=>  table[SupplierNr]= row[SupplierNr] and row[TransactionDate]>table[From] and row[TransactionDate]<table[To]
        )) then "No" else "Yes"
    )
in
    AddColumnValidContract

hope this helps

cheers

Jimmy