Creating an excel type database within Powerapps

[salieri]

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. 

[Drrickryp]

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.  

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 

[salieri]

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? 

[Drrickryp]

@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. 

[salieri]

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

[Drrickryp]

@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?

[salieri]

with a canvas app like you suggested.

[Drrickryp · Accepted solution]

@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...