Comparison between String and Column Value not working

[Walt1010]

Hi, I am trying to do a very simple comparison between all the values in a column (Job Titlle) in another table (FMS Carers) and the string literal "Agency worker", but it seems to fail the test each time, although some of the filds do contain "Agency worker".

Here is the code:

#"Agency" = Table.AddColumn(#"Replaced Value","Agency", each if #"FMS Carers"[Job Title] = "Agency worker" then "Agency" else "PT Staff")

This always returns "PT Staff" although the column contains instances of "Agency worker". I tried TextContains but hit an error message about comparing a list and text, so I've reverted to "=". Any ideas?

[HotChilli · Accepted solution]

Easiest way will probably be to Merge the 2 tables on Date to return the fields you need

[v-junyant-msft · Accepted solution]

Hi @Walt1010 ,

@HotChilli Thank you for your concern about this case! I agree with you.
Have you solved your problem? If not, what @HotChilli said is as below, you can follow these steps:
Here is my sample data:

First, merge queries:

The output is as below:

Expand the table:

Then add custom columns:

Best Regards,
Dino Tao
If this post helps, then please consider Accept both of the answers as the solution to help the other members find it more quickly.

[HotChilli · Accepted solution]

Easiest way will probably be to Merge the 2 tables on Date to return the fields you need

[Walt1010]

There are no defined links between the 2 tables. They each contain a Date field. I am trying to identify, for each date in the FMS Carers table, if there exists a matching date in the Holidays table. If such a match exists I'd like to put a flag to that effect in the FMS Cares table.

[HotChilli]

"a very simple comparison between all the values in a column (Job Titlle) in another table (FMS Carers) and the string literal "Agency worker"" - what's the link between 'this' table and the FMS Carers table?  There are no defined relationships in Power Query.

Can you be a bit clearer in what you are trying to do?