Log In
Register
Home
Trusted Members
October 18, 2018
Offline
Receiving the following message: Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
This is happening with this line of code: = Text.Replace([Address], [States], " ")
After finding the State in the Address field and placing it in the States Field, I wish to remove it from the Address Field.
File is attached for review.
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Instead of:
= Table.AddColumn(#"Changed Type", "States", each List.Accumulate(States1, "",
(state,current) =>
if List.Contains(Text.Split([Address]," "),current)
then state & " " & current
else state))
Try:
= Table.AddColumn(#"Changed Type", "States", (x)=> List.Accumulate(States1, "",
(state,current) =>
if List.Contains(Text.Split(x[Address]," "),current)
then state & " " & current
else state))
Trusted Members
Moderators
November 1, 2018
Online
Shouldn't the last step read something like:
= Table.AddColumn(#"Added Custom", "Address2", each Text.Replace([Address], [States], " "))
rather than just the Text.Replace function?
Answers Post
Trusted Members
October 18, 2018
Offline
@Catalin. Tried your suggestion but it did not compute. PQ did not accept the line of Mcode. Did not throw an error, but did post some gibberish in the main window.
@Velouria. Your suggestion worked, but it then required that I delete the column with the original Address as it duplicated part of it. I can live with that.
Thank you both for your time on this issue.
Alan
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Alan,
That step with the List.Accumulate function will not work without the changes indicated, it will return exactly the same error you mentioned on first post.
Not sure what you are trying to do, but without fixing this step, you really can't fix the next step with Text.Replace...
Trusted Members
Moderators
November 1, 2018
Online
The prior step works fine for me (after creating an appropriate source file for the state list).
For a direct in-place replacement, you could try:
= Table.ReplaceValue(#"Added Custom", each [States], "", Replacer.ReplaceText, {"Address"})
1 Guest(s)
