ReplaceValue not working in Excel Query Editor

Question

Im trying to complete a table.ReplaceValue step in my query editor but the values dont seem to be changing with the conditions ive set.

The steps im trying to replicate in code in the editor:

Whilst a filter to only show Requried is applied to column M, Filter Column N to only show FALSE and change this value to Outstanding (copy it down to the remaining rows)
Remove filter from Column N & M
Column M – Apply filter to just show Required
Column N – Apply filter to just show TRUE and change this value to Received (copy it down to the remaining rows).

= Table.ReplaceValue(#"Replaced Value7",each [P&C info received] ,each if [P&C info required] = "Required" and [P&C info received]= "false" then "Outstanding " else if [P&C info required] = "Required" and [P&C info received]= "true" then "Received " else [P&C info received],Replacer.ReplaceText,{"P&C info received"})

M and N are the columns im working with in the spreadsheet table.

Your columns are not called M and N. You use the column headers in M code, not the column letter. — Rory
– Rory, Commented Jul 20, 2023 at 10:26
As an aside, having spaces at the end of "Received " is just storing up trouble for later - really no need for them — CHill60
– CHill60, Commented Jul 20, 2023 at 11:36
What M code changes would I require for my line of code sorry? — mblack204
– mblack204, Commented Jul 20, 2023 at 12:36

davidebacci · Accepted Answer · 2023-07-20 12:55:29Z

1

Try this:

= Table.ReplaceValue(#"Replaced Value7",each [P&C info received] ,each if [P&C info required] = "Required" and [P&C info received]= "false" then "Outstanding " else if [P&C info required] = "Required" and [P&C info received]= "true" then "Received  " else  [P&C info received],Replacer.ReplaceText,{"P&C info received"})

answered Jul 20, 2023 at 12:55

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mblack204 Over a year ago

Thank you for the reply, tried it and it has an invalid indentifier appearing at the first each [P&C info received]

davidebacci Over a year ago

Paste your full code from advanced editor in your question.

horseyride Over a year ago

You need [#"P&C info received"]

davidebacci Over a year ago

D'oh. Thanks @horseyride! I didn't spot the ampersands which require this.

Collectives™ on Stack Overflow

ReplaceValue not working in Excel Query Editor

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related