Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
spoony
Helper I
Helper I

Query editor replacing values based on another column

I can't seem to get this to work in query editor:

 

let
Source = Excel.Workbook

#promote header = ....

#"Replaced OTH" = Table.ReplaceValue(#"promote header"," ","OTH",Replacer.ReplaceValue,{"Gender"}),
#"Replaced Gender" = Table.ReplaceValue(#"Replaced OTH",each if [Surname] = "Manly" then "Male" else [Gender],[Gender],Replacer.ReplaceValue,{"Gender"})

 

in
#"Replaced Gender"

 

Im trying to change the gender column to "Male" if Surname column contains "Manly". It gives me an error:

 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

1 ACCEPTED SOLUTION

You are right. I was confusing Table.ReplaceValues with Table.TransformColumns. Smiley Embarassed

My solution works though, but the code you are looking for:

 

    #"Replaced Value" = Table.ReplaceValue(#"Replaced OTH",each [Gender],each if [Surname] = "Manly" then "Male" else [Gender],Replacer.ReplaceValue,{"Gender"})

 

Edit: it seems you switched "old"  and "new"  in your cide.

Specializing in Power Query Formula Language (M)

View solution in original post

34 REPLIES 34

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.