cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kamm
Level: Powered On

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

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:

powerapp_collect_function.jpgCollect function

 

 

This error is occurring:

powerapp_incompatible_error.jpg

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @Kamm,

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Super User
Super User

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

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. Exceltable.PNG

 

  

Kamm
Level: Powered On

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

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. 

Super User
Super User

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

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.
Community Support Team
Community Support Team

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

Hi @Kamm,

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Kamm
Level: Powered On

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

Great solution. 

Date: DateTimeValue(DateTime.Text)

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 199 members 5,717 guests
Please welcome our newest community members: