cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Error trying to connect to an Excel table

Hello everyone!

 

I keep receiving this error "Unable to open excel file. File may be corrupted. Please use excel to recover the file and try again." I've tried to copy the contents of the workbook to a different excel file with no success - which seemed to be the main suggestion from here. One thing that I noticed is that the power apps ID column isn't generating. Is there a way to manually trigger it?

4 REPLIES 4
Super User II
Super User II

Hi @Hunter22 

I have some suggestions.  Make sure that you don't have any calculated columns in the spreadsheet and set the column properties to either a date, text or number.  In PowerApps, you can generate an ID for a new record by using the formula

First(Sort(Table1, ID, Descending)).ID+1 .  This is so you don't run into a delegation problem if your table exceeds 2000 items. 

Hey @Drrickryp! Thanks for the quick reply. So I've tried to make everything into text and that didn't resolve it. I've copied and pasted the table below.Table Example.jpg

 

I'm trying to create a drop down menu in Power Apps and connect it to this table. As you can see the table doesn't have the "PowerApps_Id_" column to the right. No matter how many times I connect and reconnect it won't generate a new "PowerApps_Id" column. I believe this is the issue because at one point when I was working on this project, I had a table with the "PowerApps_Id" column and it worked fine and another table that didn't have the column and it didn't work. Mind you, these tables were in the same workbook. An interesting thing though, I'm able to see the table and column still in the drop down whenever I pick the column values I want to be displayed. However no values in the actual drop down menu show up.

 

One other thing I noticed was whenever I manually put stuff into the table the "PowerApps_Id" column didn't autogenerate a new Id for the new row I created. This also lead to PowerApps having an issue connecting to it.

Hi @Hunter22 

PowerApps doesn't generate an autonumber in an Excel table unlike SharePoint, SQL or CDS.  It must be manually created.  The ID column that is assigned to your table is for internal use and is hidden.  My suggestion is to add an ID column formatted as a number to your list and number it 1-11.  If you add additional items to the list, you can use the formula I referred to above to add the next number in the sequence.  I created a table like yours in Excel and had  PowerApps build it automatically.  In Excel, I had to change the format type of the ID column from General to Number or it didn't work right.  For demonstration purposes, I put the Edit form below the gallery.  I unlocked the ID card and put the formula in the formula bar into the Default property of the TextInput box inside the card.  This will generate the next number in the series automatically in the Excel table._Screenshot 2020-12-03 094619.png

This is the table in Excel after it has been connected to PowerApps.  The third column is not accessible from within PowerApps._Screenshot 2020-12-03 102020.png

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (36,571)