Forum Discussion
How to join a string to a column value in PowerQuery
Okay,
So I have a column that has a null value in some cells. In another column though the second half of the data is present but missing the prefix.
Core Column Other column
ITxxxxxx xxxxxx
I want to replace any null values in the core column with "IT" & [Other column]. So far though I'm just not quite getting it. I can get the [Other column] in place using
each _[Other column]
because this is a function though it resists joining with the string. I've tried using Text.Combine with a list and other things but still have not worked my way to fixing this. I am sure when someone explains I'm going to feel pretty stupid but the deadline I have is closer than my ability to read through the book I bought.
Can anyone point me in the right direction?
I think the expression you need is:
each "IT" & [Other column]
The "&" character does string concatenation.
The whole Step would be something like:
= Table.ReplaceValue(#"Changed Type",null,each "IT" & [Other column],Replacer.ReplaceValue,{"Core Column"})
6 Replies
I don't have a PC in front of me to test but I'm pretty sure you can do this in PowerQuery using the TRIM button and/or parse. I can test later but it might lead you in the right direction. Split Column button might also help with this.
Hope this helps
I think the expression you need is:
each "IT" & [Other column]
The "&" character does string concatenation.
The whole Step would be something like:
= Table.ReplaceValue(#"Changed Type",null,each "IT" & [Other column],Replacer.ReplaceValue,{"Core Column"})l said I'd feel stupid didn't l? Can't believe I didn't think to try that combination. I Cleary don't have a grasp on what each does yet.
Many thanks for the help
trevb - I wouldnt beat yourself up too much. It is a fairly new technology/language without a lot of doco and examples.
