Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
Trying to do the same thing in 2023 and this solved my problem. Thanks!
Thank you, @Anonymous , TextForm function was fundamental!
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?
@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?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
27 |
User | Count |
---|---|
92 | |
50 | |
44 | |
40 | |
35 |