Forum Discussion

aatish178's avatar
aatish178
Icon for Helper IV rankHelper IV
2 years ago

comparing 2 date columns from 2 different tables with each other

Hi All, I am new to power bi , and in the start itself I have got a complex requirement I have 2 tables consisting of date field in each as shown below: Table 1: DateToBeChecked 2024-05-05 ...
Need Help
Tips and Tricks
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

@Hi aatish178, you asked in POWER QUERY forum so here you have PQ solution:

 

Result:

 

let
    DateToBeChecked = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MAUipVgdGNdE1wiZa6xraIzCNUDmGukaGirFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    DateToBeCompared = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE1MNU1NlSK1YFxTXSNLJC4xrpGBihcQ2Suka6hqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    ComparedDates = List.Accumulate(
        DateToBeCompared[Date],
        DateToBeChecked,
        (s,c)=> Table.AddColumn(s, c, each if Date.From([Date]) <= Date.From(c) then c else null , type text)
),
    RenamedColumns = Table.RenameColumns(ComparedDates,{{"Date", "▼Checked | Compared ►"}})
in
    RenamedColumns