PQ Replacer.ReplaceText with if condition based on another table

[Divous]

Hi community,

I need replace text (in value column in Table 1) based on equal uuid with another table. I have:

Table 1

id	uuid	value
1	41c1dc07-4631-485a-8476-dbbe9d3d50d0	NEW
2	7210a5d3-4884-4ac7-a6da-3b66e5c78bb8	NEW
3	632ec0b3-f823-419f-ba72-da4be9ce865a	OLD
4	dbd129a1-114a-48ae-985a-44029f6d6205	NEW

Table 2

id	uuid
1	41c1dc07-4631-485a-8476-dbbe9d3d50d0
2	7210a5d3-4884-4ac7-a6da-3b66e5c78bb8
3	8dab2cd0-99ee-4438-b307-5af38eb1a734
4	f3fd6f60-6302-44e8-8e7d-fb0d4812004e

Expected results:

Table 1

id	uuid	value
1	41c1dc07-4631-485a-8476-dbbe9d3d50d0	OLD
2	7210a5d3-4884-4ac7-a6da-3b66e5c78bb8	OLD
3	632ec0b3-f823-419f-ba72-da4be9ce865a	OLD
4	dbd129a1-114a-48ae-985a-44029f6d6205	NEW

I have this:

#"Replace NEW->OLD" = Table.ReplaceValue(#"Some_step_before",each [value], each if [uuid] = [Table 2.uuid] then "OLD" else [value],Replacer.ReplaceText,{"value"})

it do something.. 🙂 but I think its wrong.

Can you help me, please?

[Vera_33]

Hi @Divous 

If join Table2, then it works, so ID is the primary key?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY4xDgIxDAT/kvosxY7jJD10JygpTlfYce7/T8AgIdFNsaPZ40iYtsQ40WduwFIQuFeFzk3Azdbw4jV7jtnj/krndiQKboRZq5dYdwbW2UDFFYqJrDpbN+t/SgmWQmtmK3B1Cg/HBaaNwJUjM1eXqjF77revwsFujjQUAZE1UrpgfN4xZxqXuFCuv8r5Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, uuid = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"uuid", type text}, {"value", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"id"}, Table2, {"id"}, "Table2", JoinKind.LeftOuter),
    Custom = Table.ReplaceValue(#"Merged Queries",each [value], each if [uuid] = [Table2][uuid]{0} then "OLD" else [value],Replacer.ReplaceText,{"value"})
in
    Custom

[Divous]

Hi @Vera_33 ,

thanks for your reply. ID is not primary key - sorry for consufing. Tables are from different sources, but uuid can be same in both, and if so, I need make change NEW -> OLD in Table 1

In Table 1 there is much more rows, but only few can be affected by Table 2.
I was thinking about merging them, but I tought (probably wrong) that this ReplaceText function should be more elegant and preciese than create another column..

[Vera_33 · Accepted solution]

Hi @Divous 

So what is the logic? Say if Table1[uuid] found in Table2[uuid], then change the value, otherwise remain the same?

[Divous]

Hi@Vera_33 

yes, yes, exactly. Sorry for my bad english and explanation.