power query expression.syntax error invalid identifier with parentheses

[NAOS]

Hi all, I'm trying to reference a column in a query step as follows:

ModifySiteArea = Table.ReplaceValue(RenameColumns,each [Site Area (m2)], each if [UnitOfMeasurement] = "ft²"  then Value.Divide([Site Area (m2)],10.764) else [Site Area (m2)],Replacer.ReplaceValue,{"Site Area (m2)"})

This causes the Expression.Syntax error. Invalid Indetifier to show up. If I rename the column "Site Area (m2)" to "Site Area" the query works with no problems. The double quotes on the column name don't solve the problem either.   Could someone explain me why is this?  

[MontanaBlack]

Hi everyone, 

I have the same issue but actually don't understand what's wrong with my code. I get the error message: missing identifier". Hope someone can help. The code is:

let
//Find the current date and time when this query runs
CurrentDateTime = DateTimeZone.FixedUtcNow(),
//Find yesterday's date
PreviousDay = Date.AddDays(DateTime.Date(CurrentDateTime),–1),
//Put the current date and time in a new column in the table
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "UTC", each CurrentDateTime),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"UTC", type datetimezone}}),
//Add the filter required for incremental refresh
//Only return rows in this table if:
//a) The RangeStart parameter equals yesterday's date, and
//b) RangeEnd is not null (which should never be true)
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each DateTime.Date(RangeStart)=PreviousDay and RangeEnd<>null)
in
#"Filtered Rows"

[Rudz]

"–1" is the problem. This is "en dash" 1 not "minus one". Retype the minus.

[vernc]

wow, this is critical, I had an unresolvable issue with the invalid syntax identifier present within [ID: Item Cat] and I had tried with putting ["ID: Item Cat"] or changing it to {ID: Item Cat} but that would mean that we don't consider the headers but values themselves and then after it worked when I replaced the syntax with just this [Item Cat]

[Rudz]

I had a similar problem with a column with a question mark in the column name. Here's the syntax that worked there. I'm guessing it would work for yours too.

[#"Valid?"]

[KERZ]

worked like a charm

[Anonymous]

Thanks @Rudz, this worked for me!

I was getting the error due to my column names beginning with "(ref)." and this saved me from having to add multiple unnecessary steps

Before:

		if [(ref).v_cust_po_date] = null
		then [(ref).so_header_create_date]
		else [(ref).v_cust_po_date]),

⚠️ Invalid identifier.

--

After:

		if [#"(ref).v_cust_po_date"] = null
		then [#"(ref).so_header_create_date"]
		else [#"(ref).v_cust_po_date"]),

✔️ No syntax errors have been detected.

 

[thomasfmeier]

Thank you for this. Had the same issue with a field [Total Price Ex Tax ($)] and your solution solved this 🙂

[apralou]

Thank God for you is all I can say.

[Anonymous · Accepted solution]

HI @NAOS,

Actually, current you can't use specific characters in [] operator. Maybe you can consider to change name before do this operation and restore fields name after finished replace operations.

Regards,

Xiaoxin Sheng

[NAOS]

Hi Xiaoxin,

That's what I ended up doing. Thanks for your answer!