CODEWITHSUNDEEP
excelpowerquerym
lljc00
lljc00

Reputation: 39

Power Query Replace Value not working. Using #" " around the Column Name?

(VERY new to Power Query) I am trying to replace a value in a column based on two criteria. I created a helper column, and the IF statement for the two criteria works, but when I try to copy+paste it into the ReplaceValue statement, it doesn't work.

I noticed in this video https://www.youtube.com/watch?v=XpZTPEBHQOY approximately 4:10 that he types in the Columns as just [Smoking_Allowed] (vs [#"Smoking_Allowed" with the # and quotes).

When I create my helper column using Add Column > Custom Column, it puts my column names with the # and quotes, and as mentioned, it works. I tried my ReplaceValue statement both with and without the # and quotes, but neither way works.

Any ideas? Not sure I can post my file without some major clean up as its for work. Here's the code with a couple changes (using the version with the # and quotes as PQ will give me syntax error without to obscure work related info.

= Table.ReplaceValue(#"Added Custom for Sales Clean up", each [Department], each if [#"Account#"] = 12345 and [#"Memo/Description"] = "Example Description" then "Sales" else [Department], Replacer.ReplaceText,{"Department"})

EDIT: Anonymized the account/description.

Upvotes: 0

Views: 1808

Answers (2)

lljc00
lljc00

Reputation: 39

The clouds open up and the angles sing...

Account# is actually Account # (with a space). SHM.

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

#"..." is most often used for names that contain spaces like the reference to the previous step you have in your example: #"Added Custom for Sales Clean up". This lets the code know that this is all a single object.

When you're referring to a column, since it's contained in square brackets, [ ], this isn't usually necessary since the brackets group it all together. However, if you have certain characters in a column name, you'll need to 'escape' them with #"..." again. Since you have # and / in your column names, you need to use that syntax.

Upvotes: 0

Related Questions