cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Creating an excel type database within Powerapps

Hello, I am creating a power app that acts as a portal that lets users input information that is stored in an excel type database. The problem is that I don't know how to create either a portal to input the proper fields or how to set up the database with the required fields to hold that data. Truth is I'm just overwhelmed by all the technical details within App designer and I would appreciate it if someone could point me in the right direction. The screenshot below shows what I have set up but it isn't much. 

 

power.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@salieri 

All PowerApps must have a back end table to save the data to.  PowerApps cannot create a back end table or change the structure of the table once it is created.  If you are using Excel, just create a table with two rows.  The first row should be the column headers (field names). The field names should not contain spaces. Have one field as the ID.  Put some dummy data in the second row.  Create the app as I described. You can add the rest of the data later by cutting and pasting it into your new table once the app is created.  If you are serious about learning how to use powerapps to create apps, I suggest that you review two things.  First, learn about delegation in PowerApps https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview  and second review my series on database fundamentals in PowerApps.  https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An...  

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

Hi @salieri 

It appears that you are trying to use a Model driven app to save data to an Excel file.  Model driven apps will only work with Common Data Service entities and I don't think that you can easily do what you want with a Model Driven app. 

Instead, create a Canvas app.  If you have an Excel file in an Excel On Line account, you can start by creating a Canvas app from it.  Start from the make.powerapps.com page shown here and choose the Excel file.  PowerApps will create an app from your data but there are some requirements to make it work right.  Format your data as a table within Excel. and do not include any calculated columns. Ideally, one of the columns should be an Index or ID column that is a sequence of integers.  Annotation 2020-07-01 080032.png

Once you have got the app created you can examine your data in an Excel like way by using a datatable control. 

 

If you are going to create a Model Driven app, then you have to examine the standard entities to see which one best matches the type of data you have.  Then, match the field names exactly between the entity and the spreadsheet table.   Follow the instructions here https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/build-first-model-driven-app 

Highlighted

Thanks for the reply! When I try to input my excel file, I get the error: "Failed to read Excel workbook stream. Failure reason: Invalid Hyperlink: Malformed URI is embedded as a hyperlink in the document."

 

Do I have to change the hyperlink to non-hyperlinks to get around this? 

Highlighted

@salieri 

I believe that is what it is telling you.  Make a copy of your Table and get rid of the hyperlink column.  Then try reimporting it.  PowerApps can handle Hyperlinks inside the app but probably can't import them in that format.  I haven't done it before but perhaps you can change the Data type from Hyperlink to Text and try again. 

Highlighted

What happens if I choose not to import it and define one from scratch. How would I go about doing that?

Highlighted

@salieri 

Please explain.  Are you trying to do it with a Model driven app or Canvas app?  If an MDA, which entity are you trying to use?

Highlighted

with a canvas app like you suggested.

Highlighted

@salieri 

All PowerApps must have a back end table to save the data to.  PowerApps cannot create a back end table or change the structure of the table once it is created.  If you are using Excel, just create a table with two rows.  The first row should be the column headers (field names). The field names should not contain spaces. Have one field as the ID.  Put some dummy data in the second row.  Create the app as I described. You can add the rest of the data later by cutting and pasting it into your new table once the app is created.  If you are serious about learning how to use powerapps to create apps, I suggest that you review two things.  First, learn about delegation in PowerApps https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview  and second review my series on database fundamentals in PowerApps.  https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An...  

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (14,795)