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

Powerapps datatables and collections

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
Super User
Super User

Re: Powerapps datatables and collections

Have you tried:

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



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
Community Support Team
Community Support Team

Re: Powerapps datatables and collections

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.
MaxDonen
Level: Powered On

Re: Powerapps datatables and collections


@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


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

 

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: 348 members 5,949 guests
Please welcome our newest community members: