Unanswered

Addcolumns with GroupBy

(0) Share

Report

Posted on by struebigm

I've got this GroupBy in the item properties of a Gallery:

GroupBy(Filter(SharepointCombinedCurrentData,Analysis_x0020_Code=SelectCode.Selected.AnalNo),"Account_x0020_Code","Accdesc","GBAccount").

The Account Cod and Accdesc comes through fine.

Now I want to add another column to my data. It will be from the same data (filtered SharePoint) as above. And it needs to be Sum of Amount. I've followed many Addcolumns examples here and on other postings but cannot get it to work.

I'm new to PowerApps moving from VBA and struggling. Can anyone guide me?

Many thanks

Martyn

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Brian Dang 3,976 on at

Like (3)

Report
Hi @struebigm,
It sounds like you want to add columns, but you're not sure how to because the Amount column doesn't appear.

The thing about GroupBy is that it reduces the table to the columns you picked. The resulting columns shows unique values for that column--it's kind of like Distinct(). In your case, you'll get a different row for unique combinations of "Account_x0020_Code" and "Accdesc."

In the third argument of GroupBy, you had written the name of a table, GBAcount, that includes all records that match each unique combination of "Account_x0020_Code" and "Accdesc." That table will include all of the other columns you want.

So the big idea here is that if you wanted to add a column to the overall expression that interacted with the other columns, you'll need to reference GBAcount, the table that resulted from GroupBy().

AddColumns( GroupBy( Filter(SharepointCombinedCurrentData, Analysis_x0020_Code=SelectCode.Selected.AnalNo ), "Account_x0020_Code","Accdesc","GBAccount" ), "name_of_new_column", Sum(GBAccount,Amount) )
This means,
Filter the SharepointCombinedCurrentData table where the Analysis code matches the selected analysis number.
Group those filtered results for unique combinations of the Account code and Accdesc.
Place records that match each of those unique combinations into separate tables called "GBAccount."
Then add a column that sums each of those tables for the Amount column.
This results in a table with 4 columns:
Account code (string)
Accdesc (string)
GBAccount (table with all the other columns)
Sum of the Amount in each GBAccount table (value)
Let me know if that helps with a thumbs up.

Mr. Dang

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @struebigm,

Do you want to add a column to the grouped data (Using GroupBy function) and its value is the Sum of the Amount column?

Could you please share a bit more about your SP list?

I agree with @mr-dang's thought almost. I suppose that the Account Code, Accdesc and Amount are all columns in your SP list, is it true?

I have made a test on my side, please take a try with the following formula:

AddColumns(
GroupBy(
Filter(SharepointCombinedCurrentData,Analysis_x0020_Code=SelectCode.Selected.AnalNo),
"Account_x0020_Code","Accdesc",
"GBAccount"
),
"Sum of Amount",
Sum(GBAccount,Amount)
)

Or

DropColumns(
AddColumns(
GroupBy(Filter(SharepointCombinedCurrentData,Analysis_x0020_Code=SelectCode.Selected.AnalNo),"Account_x0020_Code","Accdesc","GBAccount"),
"Sum of Amount",
Sum(GBAccount,Amount)
),
"GBAccount"
)

More details about the GroupBy function, AddColumns function and DropColumns function in PowerApps, please check the following article:

GroupBy function, AddColumns function and DropColumns function.

Best regards,

Kris

Was this reply helpful? Yes No
struebigm 7 on at

Like (0)

Report

Thanks to both yourself and Mr Dang. I can see where you are coming from and I am trying to do what you suggest.

Here is my code which still doesn't work

groupBy(Filter(SharepointCombinedCurrentData,Analysis_x0020_Code=SelectCode.Selected.AnalNo),"Account_x0020_Code","Accdesc","GBAccount");AddColumns( GroupBy( Filter(SharepointCombinedCurrentData, Analysis_x0020_Code=SelectCode.Selected.AnalNo ), "Account_x0020_Code","Accdesc","GBAccount" ), "name_of_new_column", Sum(GBAccount,Amount) )

Is it the ; I'm using to separate the two statements incorrect. I've tried ;; etc or am I doing it all wrong.

Martyn

Was this reply helpful? Yes No
Brian Dang 3,976 on at

Like (0)

Report

@struebigm wrote:
Thanks to both yourself and Mr Dang. I can see where you are coming from and I am trying to do what you suggest.

Here is my code which still doesn't work

groupBy(Filter(SharepointCombinedCurrentData,Analysis_x0020_Code=SelectCode.Selected.AnalNo),"Account_x0020_Code","Accdesc","GBAccount");AddColumns( GroupBy( Filter(SharepointCombinedCurrentData, Analysis_x0020_Code=SelectCode.Selected.AnalNo ), "Account_x0020_Code","Accdesc","GBAccount" ), "name_of_new_column", Sum(GBAccount,Amount) )

Is it the ; I'm using to separate the two statements incorrect. I've tried ;; etc or am I doing it all wrong.

Martyn

Ohh, I see the misunderstanding here. If you want all the data from the original table + that summed up column, you only need the part in red. You do not need the part in blue.

@v-xida-msft and I were giving you a formula to replace yours with, not add onto, since our formulas provide the columns you want + the added column.

If we are still misunderstanding your needs, please clarify. (Ex: do you want two different tables and galleries? Do you just want to show another label with data from the added column?)

Mr. Dang

Was this reply helpful? Yes No
struebigm 7 on at

Like (0)

Report

I'm getting the principles wrong somewhere.

I'm putting the blue in the Items of the Gallery which works fine to bring through the fields I want 'group'. The filter is driven by a combobox. Without the blue the data is not selected.

Where does the red go?

Martyn

Was this reply helpful? Yes No
Brian Dang 3,976 on at

Like (0)

Report

@struebigm wrote:
Where does the red go?
Can you remove the blue part and the semicolon and only use the red part in the Items property of your gallery?

Mr. Dang

Was this reply helpful? Yes No