LookUp Excel function Error

[TimDemma]

Hello PowerApp wizards,

I am building a screen that will in theory display a table from an Excel file housed on a SharePoint site, and what I am hoping to do is have a rectangle Fill color change based on the text found in one of the columns of said Excel File. I have connected the table via "Excel Online (Business)", have the Table Viewer displaying my data, however, while trying to troubleshoot the code to find the data in the column that will provide the condition for the Fill color change, I am getting an error that says "Error when trying to retrieve data from the Network." Heeeeeeeelp!

Table 1 is the Table in the Excel document, "Date" is field of the Column 1 I am Looking Up through and "Detail Status" is the second column and the data that will provide the conditional formula.

*** In the Excel file, the "Detail Status" column uses Data Validation for the user to use a drop-down menu to select a specific value... Not sure if that is causing the major malfunction.

As always, any assistance/feedback is welcomed. 

Thanks in advance!

[WarrenBelz]

Hi @TimDemma ,

Firstly, you are best to confine your Excel data to simple Text or Numeric fields as all you are using it for is data storage for Power Apps. Apart from this, what do you mean by Column1 = "Date:" - are you looking for a record where your column name is Column1 and it contains the Text Date: and then you want to show the content of another column in that record 'Detail Status' ?

[TimDemma]

 Hey @WarrenBelz,

Sorry it took me so long to reply. I'll start with answering the second part of your question: Yes, that is exactly what I want to do. So whatever text is in say Column2 of the Row that matches "Date:" in Column1, I'd like to have that text evaluated. For the first part of your response, is there a way for PowerApps to pull that data as a simple Text field even though the Excel Document used Data Validation to fill in the field?

Thanks!

[WarrenBelz · Accepted solution]

Hi @TimDemma ,

Firstly, the syntax below will look in Table1 for the first record where it finds the text value Date: in Column1 and display whatever value is in the column 'Detail Status' for that record.

LookUp(
   Table1,
   Column1="Date:"
).'Detail Status'

I assume it is not working as a result of your cell format and as I mentioned in my first post, Excel data sources are not designed to be "dual purpose" - if you use them for Power Apps, then the area needs to be a simple Text table solely for the use of Power Apps. You will also find that the file cannot be concurrently open in Power Apps and anywhere else.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[TimDemma]

@WarrenBelz 

So, I changed the formula to lookup a plain text formatted column and still am getting the same error. (I also tried removing, renaming, and adding in the Table again *Table1 now is "EventDetails")

My code:

My Excel file formatting: Both 'Column 1' and 'Source' for line #2 are formatted as "Text" rather than General.

That is what you are instructing I do, right? Both columns that PowerApps is evaluating are simple text formats. Is there a way to use the DataTable within PowerApps to evaluate the values for a column based on the "Date:" row?

Thanks again,

[WarrenBelz]

Hi @TimDemma ,

I just duplicated your Excel file and Table exactly as below

The Table is named EventDetails the same as yours also and here is a Label with the Text and resulting output

What are you doing different?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[TimDemma]

@WarrenBelz 

Darn it! Well, I'm glad that you were able to get it to work! LOL Maybe I linked the Excel sheet incorrectly? I linked it via Excel Online (Business).

The Excel Document is housed on a SharePoint Site document library "sites/ERBC/Shared%20Documents/Forms/AllItems.aspx". I then linked the Table name "Event Details".

All of my formulas are the same as yours but yet I am still getting the "Error when trying to retrieve data from the network." error. 

I'm not sure what else I am doing wrong... must be some kind of restriction or connection issue on my SP to PowerApps connection. Maybe a network firewall issue or something...

[WarrenBelz]

Hi @TimDemma ,

That was my next question - you need to store the file in your OneDrive, share it appropriately and then link it with the OneDrive for Business connection to select the file and then the table.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[TimDemma]

@WarrenBelz 

Thanks for all the help. Unfortunately, my situation calls for the document to live on that SharePoint site that I have it currently linked too. I'll have to play around with permissions and what not to get it to work in the way you mentioned. It's odd that PowerApps is having such a struggle connecting to the Excel data source even though the formula is correct. 

Until my next crazy question!

[aacm]

Then is there a way to use lookup for excel on sharepoint?