Forum Discussion
Helper IIIPQ Replacer.ReplaceText with if condition based on another table
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?
Hi Divous
So what is the logic? Say if Table1[uuid] found in Table2[uuid], then change the value, otherwise remain the same?
4 Replies
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
DivousHi Anonymous ,
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..Hi Divous
So what is the logic? Say if Table1[uuid] found in Table2[uuid], then change the value, otherwise remain the same?