Forum Discussion
Concatenate a string and a column value in Power Query (M)
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.
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.
7 Replies
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.
Thank you, Anonymous , TextForm function was fundamental!
Trying to do the same thing in 2023 and this solved my problem. Thanks!
mtrevisiol , Try something like
Text.Replace([ActiveUsers], null, "u" & Number.ToText([OptId]))
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?
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.
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?
