Excel column names with spaces - What's the deal?

[mhenderlight]

I seem to have a lot of issues when using Excel tables who's column names have spaces in their names.  I thought all I would have to do is surround the names in single quotes and that would work, however it seems that I have to remove the spaces to get things to work correctly.  Surely not.  I use MS Forms as the front-end to acquire data and when Forms data is exported to Excel, column names are determined by the text of the question, which normally has spaces.

What's the deal?  Can you simply not have spaces in column names in Excel if you are using a Power App to use that data?

[timl]

Hi @mhenderlight 

Power Apps should work with Excel tables that contain spaces in the column name.

My test app benath connects to an Excel table with column names that include 'first name' and 'last name' (with spaces).

Can you clarify what type of problem you're seeing?

[mhenderlight]

@timl 

I have issues filtering, updating, lookups, etc.  I always get syntax errors.

For example: if I have LookUp(ConnectionName, 'My Column'=TextField.Text) it doesn't work, but if I change it to: LookUp(ConnectionName, MyColumn=TextField.Text it works.

If I try to filter a DataTable using Filter(MyConnection, 'Column with Spaces' = DropDown1.Selected.Result I get a syntax error.  If I remove the spaces in the column name it works.

Things like that.

[timl]

Hi @mhenderlight 

I'm not sure what the cause of this is. What syntax errors do you usually see when you call functions like Filter and Lookup?

With my test Excel data source, it works ok for me.

When you write a formula that uses LookUp, does the Intellisense suggest the columns that include spaces?

[mhenderlight]

@timl 

Here is my exact scenario.  I have a screen with a dropdown that I want to use to filter data in a datatable.  Values come from a text column in my Excel table and include values such as:

IA/Para (to address work completion)
Admin (to engage engagement strategies)
Counselor (to address SEL/Wellbeing/provide resources)
Attendance supervisor (to address inconsistent attendance)

MyDropdown.Items = Distinct(Tracking, 'Asking for assistance')

DataTable1.Items = Filter(Tracking, 'Asking for assistance'=MyDropdown.Selected.Result)

I can't seem to copy the syntax error message, but it essentially says:

Syntax error at position 10 'Asking for assistance eq 'IA/Para (to address work completion)"

Ticks and quotes are exactly as the error message states.  I also don't ever get any data in my DataTable.

[mhenderlight]

Here is a screenshot:

[timl]

Hi @mhenderlight 

I'm not sure what the answer is to this as I can get this to work with column names and values that closely match the ones that you're using.

The next step would be to click advanced tools > monitor and to start a monitor session. The thing to look for here is the operation that causes the error. Specific bits to look out for are requests that contain the expression 'Assking for assistance eq 'IA/Para (to address work completion)' in the url. The information here might reveal the cause of the problem or if you post it here, I or someone else here might be able to spot what might be wrong.

[mhenderlight]

I used the monitor and this is what it gave me as the response:

"message": "Syntax error at position 10 in 'Asking for assistance eq 'IA/Para (to address work completion)''.\r\n inner exception: Syntax error at position 10 in 'Asking for assistance eq 'IA/Para (to address work completion)''.\r\nclientRequestId: d08d8182-6100-4cbc-8636-edfab84d1c6b"

[timl]

Hi @mhenderlight 

Unfortunately, I'm not sure what the answer to this is.

Can you confirm the connector that you're using? I'm guessing it's "OneDrive for Business" (which is the same as what I'm using). Another possible cause of this could be the region. I'm using a UK based environment.

[mhenderlight]

Yes, I'm using the OneDrive for Business connector in the US region.