PowerApp LookUp doesn't seem to work for Column Names with spaces in Excel(One Drive Business)

Like (0) Share

Report

Posted on 28 Aug 2023 09:50:09 by tropicsky

Hi All,

I'm trying to populate the Item Property of an Edit Form Control via LookUp but I can't seem to make it work.

Here's my code:

LookUp(Customers, 'Customer ID' = "123")

But this code results in a Runtime Error:

"Error when trying to retrieve data from the network: Syntax error at position 11 in 'Customer ID eq '123''.

inner exception: Syntax error at position 11 in 'Customer ID eq '123''. clientRequestId: 03a87ad6-4aec-4246-9921-e675d787a79d"

I also tried replacing the spaces with x0020 as noted in the LookUp function guidelines,

LookUp(Customers, "Customer_x0020_ID" = "123")

This didn't show any error but it also doesn't return any data from the Excel file even though there's a record for 123 Column ID.

Note: I already know that using spaces in Column names isn't advisable but I would like to make this work in case I encountered

an already existing data source that has this specific flaw.

Kindly help me with this. Thank you.

Categories:

Using Apps Using Formulas

Connecting to Data

General Questions

All responses (3)

Answers (0)

tropicsky 4 on 28 Aug 2023 at 16:37:17

Like (0)

Report

Re: PowerApp LookUp doesn't seem to work for Column Names with spaces in Excel(One Drive Business)

After trying again and again, I found a way to make this work.
If you're using an excel file, the Data connector should be OneDrive for Business instead of Excel Online(Business).
DataSource from the OneDrive doesn't throw a syntax error when using the 'Costumer ID' while when the Powerapp is connected to Excel Online, the error keeps on appearing.

I don't know why this issue occurs. I thought that both Data Connectors will work since they both support Excel files.
After learning this now, I would like to ask if it's possible to change the title of my post since it is misleading,
it should be "PowerApp LookUp doesn't seem to work for Column Names with spaces in Excel Online(Business)" ?
tropicsky 4 on 28 Aug 2023 at 13:39:30

Like (0)

Report

Re: PowerApp LookUp doesn't seem to work for Column Names with spaces in Excel(One Drive Business)

Hi Jason,

It's a text column, but I also tried removing the quotation marks and it states that "Incompatible types for comparison. These types can't be compared: Text, Number"

Could you please try replicating the issue? Maybe it will work on your machine.
It would give me hope if LookUp works on your side.
JasonZF 570 on 28 Aug 2023 at 13:30:57

Like (0)

Report
Re: PowerApp LookUp doesn't seem to work for Column Names with spaces in Excel(One Drive Business)

Is the Customer ID column a text column or number column? If it's a number column, you must omit the quotation marks so it looks like this:
LookUp(Customers, "Customer_x0020_ID" = 123)
If it's a text column, then I'm afraid I'm not sure.