cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
glayne Occasional Visitor
Occasional Visitor

Group By's "All Rows" does not quote columns identifiers, throwing "Syntax Error:Invalid identifier"

As the title says, when performing a Group By operation using the All Rows aggregate, Power Query does not quote column identifiers which contain invalid characters other than spaces (in my case it was parentheses).

 

This results in an Expression.SyntaxError error due to invalid identifiers being referenced by the automatically generated query step.

 

Here is an excerpt from the Group By step. You can see that the two columns were not escaped automatically, leading to a Syntax Error.

= Table.Group(#"Changed Type", {"FIN.TRANSACTION REFERENCE NUMBER"}, {{"Count", each Table.RowCount(_), type number}, {"Data", each _, type table [MCH.MERCHANT CATEGORY CODE (MCC)=text, ACC.ACCOUNT GROUP NAME(S)=text, /* other columns, removed for tidyness */]}})

Here is my adjusted code, which works:

= Table.Group(#"Changed Type", {"FIN.TRANSACTION REFERENCE NUMBER"}, {{"Count", each Table.RowCount(_), type number}, {"Data", each _, type table [#"MCH.MERCHANT CATEGORY CODE (MCC)"=text, #"ACC.ACCOUNT GROUP NAME(S)"=text, /* other columns, removed for tidyness */]}})

I don't know what is the parsing logic for automatically escaping column names, but it is evidently missing this perticular case.

 

Thanks!

GL

1 REPLY 1
Super User
Super User

Re: Group By's "All Rows" does not quote columns identifiers, throwing "Syntax Error:

Hi @glayne ,

 

I'm on the May 2019 version, and try to make a small table with the same name as you refer on your post and the grouping got done without any errors, making automatically the expression similar to your second expression.

 

What is the version you are using and how did you make the group by? Did you write the code or did you use the grouping option?

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 43 members 1,038 guests
Please welcome our newest community members: