Powerapps datatables and collections

[Anonymous]

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.

[Jeff_Thorpe]

Have you tried:

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

[v-xida-msft]

Hi @Anonymous ,

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,

[Anonymous]

---

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

[Anonymous]

---

@v-xida-msft wrote:

Hi @Anonymous ,

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.