Hello,
There will be a simple answer, I know, but I'm having trouble using Table.ReplaceValue with a conditional term.
In brief, I have a club guest list containing hosts, who may hold a club office, and guests, who may have a role in a non-club organisation. My desired output is a commbined list that shows name, whether a host or guest, and role, comprising their office if a host or role if a guest. (see attached).
I tried what I thought should work, but it fails when the role is blank or null. However, if I insert a step to replace the null or blank with, say, "Empty", it then works fine.
So ... I can get my desired result, but I'd be very grateful if someone could explain why I had to include that extra step.
Very many thanks.
Pieter
Hi Pieter,
I would merge the Office and GuestRole columns; select them both then right click one of the column headers and click on Merge Columns
You could also add a Custom Column with the following code
Here I am checking for null and choosing the output based on whether null is found or not.
The first method is quicker and neater. The Custom Column approach requires to add a separate step to delete the columns you don't need.
See queries in attached file.
Regards
Phil
Hi Phil,
Thanks for a very prompt and helpful reply, as always.
I had actually used both those options earlier and I agree that they are great for providing the desired result. I was actually trying to learn a bit more about Table.ReplaceValue as a way of updating an existing column with possibly several conditional cases, preferably in a single line of code, so I was trying to find out why nulls and blanks throw a spanner in the works. I'd be very grateful for any suggestions!
Pieter
Hi Pieter,
I misunderstood. You can use this to replace null values but you end up with xxx instead which still needs to be dealt with. Not sure why you'd do this but maybe I'm still misunderstanding?
= Table.ReplaceValue(MyTable,null,"xxx",Replacer.ReplaceValue,Table.ColumnNames(MyTable))
Regards
Phil
Hi Phil,
I think that's the approach I took in my "Modified" query which, as you say, is untidy.
My real question is why this line:
= Table.ReplaceValue(#"Changed Type2",each [Role],each if [HostGuest]="Guest" then [GuestRole] else [Role],Replacer.ReplaceText,{"Role"})
... doesn't work. My logic is that if Hostguest is Guest then it inserts that record's GuestRole field, else it retsains the original Role. It looks as though it's fine for hosts (ie if condition is not true) but not when if is true when the original Role is blank or null. As we see, it works if we previously replace a blank or null with some random text, but shouldn't it just work without that?
I don't understand why it won't simply insert GuestRole in place of a blank or null in Role.
Thanks again,
Pieter
Hi Pieter,
It's because you're using Replacer.ReplaceText. So PQ is only looking to replace text, null is not text.
If you use Replace.ReplaceValue it will work
= Table.ReplaceValue(#"Changed Type2", each [Role], each if [HostGuest]="Guest" then [GuestRole] else [Role],Replacer.ReplaceValue,{"Role"})
Replacer.ReplaceValue - PowerQuery M | Microsoft Learn
regards
Phil
Hi Phil,
Thanks very much - I should have spotted that myself but it often takes a second pair of eyes!
Greatly appreciated.
Pieter
No worries 🙂
