Concatenate a string and a column value in Power Query (M)

[mtrevisiol]

Hi everyone.

I'm trying to modify some data using M in Power Query.

This is my table:

I'd like to modify the "ActiveUsers" column by replacing null values with the corresponding "OpId" value, so this is the formula I wrote:

However, my intent is to replace null values mantaining the same structure of the other values in ActiveUsers, so I would like to put a "u" at the beginning and at the end of the numbers (i.e. 41 becomes "u41u", 82 becomes "u82" and so on).

How can I do it? Many thanks.

[Anonymous · Accepted solution]

Hi @mtrevisiol ,

Please have a try.

= Table.ReplaceValue(#"Changed Type",null, each "u"&Text.From([Opld])&"u",Replacer.ReplaceValue,{"ActiveUsers"})

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[carinalou]

Trying to do the same thing in 2023 and this solved my problem. Thanks!

[mtrevisiol]

Thank you, @Anonymous , TextForm function was fundamental!

[Mikelytics]

Hi @mtrevisiol ,

Please try the following

if [ActiveUsers] = null then "u" & Number.ToText([OpId]) else [ActiveUsers]

The NUmber.ToText is only needed because you want to concat a number and a string which is not possible so the number has to be converted into a string. you can also convert the numner into a string upfront then you do not need the Number.ToText formula

________________________

If this post helps, then please Accept it as the solution to help other community members find it more quickly

Click on the Thumbs-Up icon if you like this reply.

[mtrevisiol]

@Mikelytics 

I replaced the line of code I wrote with:

 #"Change" = if [ActiveUsers] = null then "u" & Number.ToText([OpId]) else [ActiveUsers]

but I got

Expression.Error: An unknown identifier is present. Was the abbreviated [field] syntax used for _[field] outside an 'each' expression?

[amitchandak]

@mtrevisiol , Try something like

Text.Replace([ActiveUsers], null, "u" & Number.ToText([OptId]))

[mtrevisiol]

@amitchandak I replaced the line of code I wrote with:

#"Change" = Text.Replace([ActiveUsers], null, "u" & Number.ToText([OpId]))

but I got

Expression.Error: An unknown identifier is present. Was the abbreviated [field] syntax used for _[field] outside an 'each' expression?