2 issues sending data to Excel: Incompatible type | Reformats Excel columns

[Anonymous]

Hello,

I have two, possibly related issues I created in one PowerApp:

Issue One:

I have a PowerApp that reads from and writes to an Excel spreadsheet on O365. A button triggers a Collect function with the following code:

Collect function

This error is occurring:

The Excel columns are formatted as:

Name (Text), Confidence (Number), Activity (Text), Date (Date, formatted 10/4/2018 1:30 PM)

Issue Two:

When I format the Excel columns as above, the PowerApp writes an entry correctly to the spreadsheet. However, after I do that, the Excel spreadsheet automatically reformats the Confidence (Number cell format) column to Confidence (General cell format). Then the PowerApp cannot write more entries until I go in and reformat the column to Number and re-save the spreadsheet.

What do you suggest?

Thank you!

Eric

[Drrickryp]

Hi Eric,

I'm sure one of the other advisors could pick through your data and help you get this sorted out.  In the meantime,  I would take a different approach and let Powerapps do all the work for you. I tried this on my side and it worked well.  Save your Excel file in OneDrive for Business or Dropbox.   Mine was saved in Dropbox and looked like this.  The ID, EmployeeID, CategoryID, and CaseID columns are all Number, Details is General and the two date columns are Date.  From the Web.Powerapps.Com website, I clicked Data then Connectors and made sure I had the Dropdown connector. If your Excel file is in OneDrive, make sure you have that connector. Then, go back to Apps and click New app at the top left of the screen.   Under Create a new app with data, click on the connector and choose the Excel file.  Then choose the Table in the file with your data.  Powerapps will make an app for you that has three screens.  The Edit screen will automatically add data to your table.  The column formats will be correct and they shouldn't change when you Submit your form with new data back to Excel.  While this may not specifically answer your questions, it will solve both of your problems and may be a quick way to achieve what you want. 

[Anonymous]

Thanks for the great suggestion, Drrickryp. I'm going to grind on cleaning up the existing app for now, as I have a lot of functionality built on top of it already. But I think the idea of starting over from a fresh spreadsheet is a solid strategy, too. 

[Drrickryp]

start with your current spreadsheet and see how powerapps deals with your issues when it creates a new app. You may get some hints by looking at the edit form it creates.

[v-xida-msft · Accepted solution]

Hi @Anonymous,

Do you save your Excel table data source as a Collection within your app?

For your first question, the error message seems to tell that there is something wrong with the value that you provided for the Date column.

The Date column within your Collection (Completions) is required to provided a DateTime value, but the DateTime.Text formula returns a Text string value.

As an fixed solution, you could consider take a try to format the DateTime String value into a DateTime value using DateTimeValue function. Please modify the formula within the OnSelect property of the Button to following:

Collect(
   Completions,
   {
    Name: Name.Text,
    Confidence: Slider.Value,
    Activity: DataCardValue1.Text,
    Date: DateTimeValue(DateTime.Text)
   }
)

In addition, I also agree with @Drrickryp's thought almost. You could consider take a try to generate an app from your Excel table. More details about generating an app from an Excel table, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/get-started-create-from-data

Best regards,

Kris

[Anonymous]

Great solution. 

Date: DateTimeValue(DateTime.Text)

Changing this to DateTimeValue solves the issue. Thank you for your help!