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.
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))
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?
@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
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...
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"})
Thank you both. I now have it working with both solutions. Just so you know, I was working off one of Phil's Blogs as an example.
Good on both of you.
Alan
