Forum Discussion

trondlk's avatar
trondlk
Regular Visitor
4 years ago

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...
Need Help
Jimmy801's avatar
Jimmy801
Community Champion
4 years ago

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