Forum Discussion
Replace a column value(null) based on other column's value
Hi, I have user table with 2 columns, 'User ID' and 'User Name'. 'User Name' is null for some User ID's. I'm trying to add names to 'User Name' column based on user id's in power query.
Case1: When "User Name" has some text, then names is added. Here 'xyz' user ID has name 'XYZ', i'm replacing this by "Name1" and this works with below code.
= Table.ReplaceValue(#"Replaced Value2",each [User Name],each if [User ID]="xyz" then "Name1" else [User Name],Replacer.ReplaceText,{"User Name"})
Case2: When "User Name" is null, the name is not adding. Below is the query I'm using:-
here for user id= abc name is null and below formula does work.
= Table.ReplaceValue(#"Replaced Value2",each [User Name],each if [User ID]="abc" then "Name" else [User Name],Replacer.ReplaceText,{"User Name"})
Please let me know what is the issue here. Because If i try replacing null with space and again try above formula it works absolutely fine. But not when cell value is 'null'
Hello abhilash4244
you have to to use Replacer.ReplaceValue instead of ReplaceText, because null is not a text. You can also combine both code into one. I ask you also if you need to replace a lot of names depeding on your User ID? Because if tis like this you probably would need a more dynamic solution, otherwise you will end up with a endless nested ifs. Here some code to show you how to combine both steps into one
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpMSlaK1YlWAjIrKqvAzJLU4hIgN78kI7VIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User name" = _t, #"User ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User name", type text}, {"User ID", type text}}), PutNullToReproduceYourTable = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"User name"}), #"Replaced Value" = Table.ReplaceValue(PutNullToReproduceYourTable,each [User name],each if [User ID]="abc" then "Name1" else if [User ID]="xyz" then "Name2" else [User name] ,Replacer.ReplaceValue,{"User name"}) in #"Replaced Value"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
2 Replies
Jimmy801Community Champion
Hello abhilash4244
you have to to use Replacer.ReplaceValue instead of ReplaceText, because null is not a text. You can also combine both code into one. I ask you also if you need to replace a lot of names depeding on your User ID? Because if tis like this you probably would need a more dynamic solution, otherwise you will end up with a endless nested ifs. Here some code to show you how to combine both steps into one
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpMSlaK1YlWAjIrKqvAzJLU4hIgN78kI7VIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User name" = _t, #"User ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User name", type text}, {"User ID", type text}}), PutNullToReproduceYourTable = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"User name"}), #"Replaced Value" = Table.ReplaceValue(PutNullToReproduceYourTable,each [User name],each if [User ID]="abc" then "Name1" else if [User ID]="xyz" then "Name2" else [User name] ,Replacer.ReplaceValue,{"User name"}) in #"Replaced Value"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi abhilash4244
Can you provide some sample data of your column [User Name] and [User ID]? You want to replace some values in [User Name] based on [User ID], right? Can you use Table.TransformCoumns? What is the condition of null?
Custom1 = Table.TransformColumns( #"Replaced Value2", { "User Name", each if [User ID] = "abc" then "Name" else if [User ID] = "xyz" then "Name1" else [User Name] } )
