- Power Apps Community
- Get Help with Power Apps (Formerly General)
- Building Power Apps (Formerly General Discussion)
- Common Data Service for Apps
- AI Builder
- Power Apps Governance and Administering (Formerly Administering Power Apps)
- Power Apps component framework, ALM & Pro Dev
- Power Apps Portals
- Power Virtual Agents Forum
- Community Blog & News
- News & Announcements (Formerly Power Apps Community Blog)
- Galleries
- Community App Samples
- Video Gallery
- Canvas Apps Components Samples
- Microsoft Business Applications Presentations
- Ideas
- Power Apps Ideas

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Power Apps Community
- Forums
- Get Help with Power Apps (Formerly General)
- Building Power Apps (Formerly General Discussion)
- Powerapps datatables and collections

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

MaxDonen

Level: Powered On

Powerapps datatables and collections

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2019
09:17 AM

I was wondering if anyone could point me in the direction of anything that may help with the following query.

I recently started an app on Powerapps which was tied to a static excel data source(The excel table is called 'Bubbledata'). The purpose of the app is to calculate a best fit line for a set of X and Y coordinates. Owing to some company resource constraints, I am having to manipulate the data source internally within Powerapps wherever possible. (Ideally, the necessary calcs would be done in Excel of course, but this is sadly not an option here).

I then created a data table (called 'DataTable3'), and used this data table to display the Excel table's contents.

I then used the following formula to add some additional rows, each containing some simple math:

AddColumns(bubbledata,"X-X average",'Cost per m2 '-X_Average,"Y-Y average",'Total Area'-Y_Average,"X-X average*Y-Y average",X_Average*Y_Average)

The data table displays perfectly, but I have found that I cannot interact with any of the data table's rows. So for example, inserting a label with its text field set to Average(Datatable3,X_Average*Y_Average) creates the error: "Invalid Argument Type (Control). Expecting a Number value instead."

Can data tables be interacted with in this way within Powerapps?

As an alternative approach, I have tried to insert the data from the Excel table into a collection, and manipulate the columns/math from there. The Excel has 3 columns: Index, Cost per m2 and Total Area. I used the following formula on a label's 'Onselect' attribute to try to insert the Cost per m2 column:

ClearCollect(Bubblesample,{DataTable3: 'Cost per m2 '})

Unfortunately, this doesn't insert any meaningful data (just a purple symbol that, when clicked on, displays the geometric attributes of the cost per m2 data field).

I am not sure what's wrong with my formula; could something else be the issue?

Any assistance with any of the above would be extremely appreciated-thank you.

4 REPLIES 4

Jeff_Thorpe

Super User

Re: Powerapps datatables and collections

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-10-2019
10:14 AM

Have you tried:

Average(Datatable3, Value(X_Average) * Value(Y_Average))

--------------------------------------------------------------------------------

If this post helps answer your question, please click on

v-xida-msft

Community Support Team

Re: Powerapps datatables and collections

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2019
12:29 AM

Hi @MaxDonen ,

Based on the formula you provided, I think you have some misunderstanding with the Average function in PowerApps.

The Average function in PowerApps is used to calculates the average, or arithmetic mean, of its arguments. The first argument of the Average function should be a Number value or a Table value. But the Datatable3 you specified in your Average function is a control not a Table value.

In addition, there is no way to reference all reocrds in a Data Table. If you want to interact with Data Table within your Average function, I afraid that there is no way to achieve your needs in PowerApps currently.

As an alternative solution, please consider modify your Average formula as below:

Average( bubbledata, X_Average * Y_Average )

or

Average( bubbledata, Value(X_Average) * Value(Y_Average) )

Please consider take a try with above formula, then check if the issue is solved.

Best regards,

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.

If this post

MaxDonen

Level: Powered On

Re: Powerapps datatables and collections

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2019
07:26 AM

@Jeff_Thorpe wrote:Have you tried:

Average(Datatable3, Value(X_Average) * Value(Y_Average))

Thanks but unfortunately this causes the same 'Expected numeric input, not control' error as previously occured.

MaxDonen

Level: Powered On

Re: Powerapps datatables and collections

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-12-2019
07:41 AM

@v-xida-msft wrote:Hi @MaxDonen ,

Based on the formula you provided, I think you have some misunderstanding with the Average function in PowerApps.

The Average function in PowerApps is used to calculates the average, or arithmetic mean, of its arguments. The first argument of the Average function should be a Number value or a Table value. But the Datatable3 you specified in your Average function is a control not a Table value.

In addition, there is no way to reference all reocrds in a Data Table. If you want to interact with Data Table within your Average function, I afraid that there is no way to achieve your needs in PowerApps currently.

As an alternative solution, please consider modify your Average formula as below:

Average( bubbledata, X_Average * Y_Average )or

Average( bubbledata, Value(X_Average) * Value(Y_Average) )Please consider take a try with above formula, then check if the issue is solved.

Best regards,

Thanks, your first formula worked (a number is calculated) but it nonetheless displays an error above the resulting number. It's the same error, referring to a control rather than a numeric value being inputted. Unfortunately I am not sure what is going on here; I fully accept that my understanding of these features could be improved!

Perhaps I should explain what I am trying to do more clearly; I have a set of data with numeric values which is imported into excel as a static sheet. I intend to do operations on this data (as yet not fully specified, but it will be along the lines of calculate column 3 from column 1 * column 2, then find an average of column 3).

I tried to do this in a collection, but this did not work; I am not sure whether collections can be used for this?

I have previusly received suggestions that such operations would be better done in Excel or PowerBI, but unfortunately this is not an option for this project and I need to do the calcs inside Powerapps. Not great, I know, but I want to see if it can be done.

Thanks for the help so far.

Top Kudoed Authors

User | Count |
---|---|

182 | |

103 | |

68 | |

51 | |

50 |

Users Online

Currently online: 348 members 5,949 guests

Recent signins:

Please welcome our newest community members: