Compare Date from the Primary Query with the Reference Query

[Amos_Lim]

I'd like to make a Power BI dashboard.

I've got the main query that will be utilized to create the Power BI Dashboard.

The reference query was used to capture a product's new implementation date.

A new "Product" column was added to the main query to compare the date from the main query with the date from the reference query, and if the date is => in the reference query, a "AR" will be appended to the data in the "M Product" column to construct the new value in the "Product" column. Otherwise, "Product" value equals "M Product".

Note:-

1) The M Product can have or not have a T, for example, 7 or T7.

2) If the M product has a "T," the "T" must be removed.

3) The expected result in the "Product" column is two digits, i.e. if 7 is 07 and T7 is 07

4) If date => reference query, AR will be added. for example, T7 will be 07 AR, while 7 will be 07 AR.

[Anonymous · Accepted solution]

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

[Ahmedx · Accepted solution]

pls try

[Amos_Lim · Accepted solution]

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.

[Ahmedx · Accepted solution]

pls try againe

[Amos_Lim]

Both code works. thanks AlexBr & Ahmedx.

[Anonymous · Accepted solution]

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

[Amos_Lim]

I'm getting the following error, which is marked in yellow.

Any suggestions on where I went wrong?

[Amos_Lim · Accepted solution]

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.

[Ahmedx · Accepted solution]

pls try againe

[Ahmedx · Accepted solution]

pls try

[Anonymous · Accepted solution]

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

[Anonymous]

Looking at the thread with Ahmedx, if you do use code I've pasted above, you would just need to add one step to replace null value after the merge.