Hi there, I am using a PowerQuery to concatenate the values in columns 1, 2 and 3 using Text.Combine({[Column1], [Column2], [Column3]}, "|") to return "[Column1]|[Column2]|Column3]" and I would like the result of this to overwrite the value column 3 so I can ultimately drop columns 1 & 2. Note that if column 1 is null I want "[column2]|column3]". Thankfully, this is already the exact behaviour of Text.Combine
Anyway as I would like to do this in a single step I am using: Table.ReplaceValue(#"Previous Step", each [Column3], each Text.Combine({[Column1], [Column2], [Column3]}, "|"), Replacer.ReplaceText,{"Column3"}), which works perfectly if there are values in columns 1, 2, and 3, and also if there are nulls in either columns 1 or 2, but not if column3 is null. In this last scenario column3 does not update and stays null.
Now I could create a new custom column, dropping the old column3 and relabelling the custom column as the new column 3 but then I'd also have to reorder the columns as well to get back to where I was, so there's 2 extra steps I'd like to avoid if I can just understand why the single statement isn't reliable is all cases.
Any thoughts on why Table.ReplaceValue is apparently behaving in this way?
You could use Replace.ReplaceValue instead of Replace.ReplaceText. It will have the side effect of converting the column type to Any rather than Text though.
My column is already Any so that's not a problem. Unfortunately though I'm already using Table.ReplaceValue as I wrote in my OP, and I'm not aware of a Replace.ReplaceValue function. To avoid creating a new column I'm using a workaround:
#"Step 1" = Table.ReplaceValue(#"Previous Step", each [Column3], each Text.Combine({[Column1], [Column2], [Column3]}, "|"), Replacer.ReplaceText, {"Column3"}),
#"Step 2" = Table.ReplaceValue(#"Step 1", null, each Text.Combine({[Column1],[Column2]},"|"), Replacer.ReplaceValue, {"Column3"})
This gives me the results I'm looking for albeit in two steps.
What I was hoping for was that you or someone had an insight into why the statement in Step 1 doesn't replace values in column 3 if they are null. Not an issue if the answer is "that's just how it works", but it's not obvious to me why that would be.
I didn't say Table.ReplaceValue I said Replacer.ReplaceValue instead of Replacer.ReplaceText, which you are using now 😉
#"Step 1" = Table.ReplaceValue(#"Previous Step", each [Column3], each Text.Combine({[Column1], [Column2], [Column3]}, "|"), Replacer.ReplaceValue, {"Column3"}),
Yes you did, my apologies, I missed that!
That did the trick, thank you.
