How to join a string to a column value in PowerQuery

[trevb]

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?

[mike_honey · Accepted solution]

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"})

[trevb]

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

[mike_honey]

@trevb - I wouldnt beat yourself up too much. It is a fairly new technology/language without a lot of doco and examples.

[MarkDGaal]

@mike_honey You're helping me all over the place mike.... however, any reason all of my elements data types were lost after this method of value replacing? I only had 1 intended column in the formula....

= Table.ReplaceValue(#"Added Custom", each ", " & [DocumentIDValue],"",Replacer.ReplaceValue,{"UORC Link"__li__ng_escape____li__ng_escape__})

 PS no idea what "_li__ng_escape____li__ng_escape__" is at the end of my formula when posting here, that is not acutally in PowerBi.

[mike_honey]

No I havent seen it do that. 

[GTR]

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