Compare Date from the Primary Query with the Reference Query

Hi,

Please copy these two queries and you can then amend to your existing file as required.

Reference table:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc7LDQAhCATQXjg7CeBv7WNvxv7bWAb3IJnwRNlbTIqYwhzOOOO8U07Z4hFdoQvO2Ck9pXJmQQ1e8xIpKq2xMaDtt5pW0/iETuhzbZFWyqA8iEXuVH4W9ZwP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"S/No" = _t, Date = _t, #"Mair Car" = _t, #"Workshop Car" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"S/No", Int64.Type}, {"Date", type date}, {"Mair Car", Int64.Type}, {"Workshop Car", type text}})
in
#"Changed Type"

Primary table:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XckxCoAwDAXQu2Tuh+S3qDmHbqGDg5uC959sdSluD16EmCTRBUawc92v+zwaZqkphE1UqIMcd/s6N5pDDcxjU98unRO0/Nql1gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"S/No" = _t, Date = _t, Description = _t, #"M Product" = _t]),
changeType = Table.TransformColumnTypes(Source,{{"S/No", Int64.Type}, {"Date", type date}, {"Description", type text}, {"M Product", type text}}),
mergeRefTable = Table.NestedJoin(changeType, {"S/No"}, Ref, {"S/No"}, "Ref", JoinKind.LeftOuter),
expandRefDate = Table.ExpandTableColumn(mergeRefTable, "Ref", {"Date"}, {"Ref.Date"}),
addProductTemp = Table.DuplicateColumn(expandRefDate, "M Product", "Product Temp"),
blankTvalues = Table.ReplaceValue(addProductTemp,"T","",Replacer.ReplaceText,{"Product Temp"}),
leadingZero = Table.AddColumn(blankTvalues, "Leading Zero", each if Text.Length([Product Temp]) = 1 then Text.Combine({"0", [Product Temp]}) else [Product Temp]),
TrailingAR = Table.AddColumn(leadingZero, "Product", each if ( [Date] >= [Ref.Date] ) then Text.Combine({[Leading Zero], " AR"}) else [Leading Zero]),
selectColumns = Table.SelectColumns(TrailingAR,{"S/No", "Date", "M Product", "Product", "Description"})
in
selectColumns

Result:

If this solves your problem, please mark as solution.

Thanks

Alex

pls try

Thank you, Ahmedx.
I have discovered an error in the Date.1

I have M Products 1 through 49 and T1 through T49.
Those who are not included in the reference query will see an error in the Date.1 (Null).

If the M Product wasn't found in the reference query, how can I make the date.1 to be same as date? so that the result will not give an error.

pls try againe

full script after change

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XckxCoAwDAXQu2Tuh+S3qDmHbqGDg5uC959sdSluD16EmCTRBUawc92v+zwaZqkphE1UqIMcd/s6N5pDDcxjU98unRO0/Nql1gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"S/No" = _t, Date = _t, Description = _t, #"M Product" = _t]),
changeType = Table.TransformColumnTypes(Source,{{"S/No", Int64.Type}, {"Date", type date}, {"Description", type text}, {"M Product", type text}}),
mergeRefTable = Table.NestedJoin(changeType, {"S/No"}, Ref, {"S/No"}, "Ref", JoinKind.LeftOuter),
expandRefDate = Table.ExpandTableColumn(mergeRefTable, "Ref", {"Date"}, {"Ref.Date"}),
#"Added Custom" = Table.AddColumn(expandRefDate, "cRef.Date", each if ( [Ref.Date] = null ) then [Date] else [Ref.Date]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"S/No", "Date", "Description", "M Product", "cRef.Date"}),
addProductTemp = Table.DuplicateColumn(#"Removed Other Columns", "M Product", "Product Temp"),
blankTvalues = Table.ReplaceValue(addProductTemp,"T","",Replacer.ReplaceText,{"Product Temp"}),
leadingZero = Table.AddColumn(blankTvalues, "Leading Zero", each if Text.Length([Product Temp]) = 1 then Text.Combine({"0", [Product Temp]}) else [Product Temp]),
TrailingAR = Table.AddColumn(leadingZero, "Product", each if ( [Date] >= [cRef.Date] ) then Text.Combine({[Leading Zero], " AR"}) else [Leading Zero]),
selectColumns = Table.SelectColumns(TrailingAR,{"S/No", "Date", "M Product", "Product", "Description"})
in
selectColumns