cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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

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

1 REPLY 1
Syndicate_Admin
Administrator
Administrator

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]

hope this helps

 

cheers

 

Jimmy

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (3,113)