cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
spoony Regular Visitor
Regular Visitor

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

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Query editor replacing values based on another column

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)
22 REPLIES 22
MarcelBeug Super Contributor
Super Contributor

Re: Query editor replacing values based on another column

Edit: wrong answer:

 

You can't replace values in a column based on values in another column.

 

Instead, create an additional column and replace the existing column with the new column.

 

Adjusted part of the code:

 

    #"Replaced OTH" = Table.ReplaceValue(#"Promote Header"," ","OTH",Replacer.ReplaceValue,{"Gender"}),
    #"Added Custom" = Table.AddColumn(#"Replaced OTH", "Custom", each if Text.Contains([Surname],"Manly") then "Male" else [Gender]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Gender"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Gender"}})
in
    #"Renamed Columns"

 

Specializing in Power Query Formula Language (M)
spoony Regular Visitor
Regular Visitor

Re: Query editor replacing values based on another column

ok thanks that seem to work but i was checking this website and it shows someone got it to work. Is this not M power query?

 

https://social.technet.microsoft.com/Forums/en-US/6b006f20-f4a9-428f-94fa-c19d837dca0f/conditional-r...

MarcelBeug Super Contributor
Super Contributor

Re: Query editor replacing values based on another column

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)
spoony Regular Visitor
Regular Visitor

Re: Query editor replacing values based on another column

ok it works but i got a minor issue after this step. All my column types has changed to any. I dono how this would affect it.

MarcelBeug Super Contributor
Super Contributor

Re: Query editor replacing values based on another column

That seems to be a side-effect of Table.ReplaceValue.

 

You can either use my first solution or - after Table.ReplaceValue - select all columns and choose "Detect Data Type"  on the Transform tab (check if the detected types are correct and adjust the generated code where applicable).

 

Detect Data Type.png

Specializing in Power Query Formula Language (M)
spoony Regular Visitor
Regular Visitor

Re: Query editor replacing values based on another column

ok thanks, i might use the column solution for now.

vaibhav_osc Frequent Visitor
Frequent Visitor

Re: Query editor replacing values based on another column

Is there a way to do the same for multiple columns at once? 

I need to update entire row as #NA if a certain value is found in a column

MarcelBeug Super Contributor
Super Contributor

Re: Query editor replacing values based on another column

The last argument {"Gender"} is the list of columns in which values must be replaced. If you first select the applicable columns, and then choose for Replace Values, the generated code will include the names of the selected columns in the last argument.

Specializing in Power Query Formula Language (M)
mattlancs Frequent Visitor
Frequent Visitor

Re: Query editor replacing values based on another column

I just got this working for multiple columns with the following line:

 

= Table.ReplaceValue(#"Expanded TS opps", null, each if [Sales Stage] = "Closed" then "Closed" else "Absent",Replacer.ReplaceValue,{"SP Status", "TS Status"})

 

This is part of a list of potential projects we might work on. This list is physically repeated in two other places, on our Sharepoint and in our timesheet system, and I wanted to check they line up to some degree. I've merged my queries, now I wanted to check where there's a null value in the Sharepoint or Timesheet system lists, if the project's closed on the master list, consider it closed, otherwise mark it as absent.

 

So what this is doing, blow by blow:

 

= Table.ReplaceValue(  <- we're replace some values here

#"Expanded TS opps",  <- this is just the name of the previous step. Yours will be different.

null,  <- find null values to replace

each if [Sales Stage] = "Closed" then "Closed" else "Absent",  <- check what the master list status is, and respond accordingly ...

Replacer.ReplaceValue,{"SP Status", "TS Status"})  <- ... in these two columns

 

 

I hope that's clear enough.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 287 members 2,866 guests
Please welcome our newest community members: