Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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?
User | Count |
---|---|
94 | |
86 | |
78 | |
69 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |