cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KMI-Keith
Advocate IV
Advocate IV

Creating Table with Distinct rows based off of a unique Part ID and displaying the Total Qty per Part ID

I have a SQL Inventory that has multiple lines of the same Part but with different Purchase Order ID's related to the Part due to differing costs when the part was bought. I would like create a Data Table to show each part in a row and the total Qty of that PartID in a Data Table.

 

My data looks similar to this:

PartNamePartIDPOIDQty
57X60SC-CS320180100150
52X40CE-977F79100260
57X60SC-CS320180100350
64X180AO-649X102100412
52X40CE-977F79100560

 

I would Like the Data to look like this:

PartNamePartIDQty
57X60SC-CS320180100
52X40CE-977F79120
64X180AO-649X10212

 

I believe I have to use Distinct() and AddColumns() but Im just lost on how to put this together right now so any help would be appreciated!


Regards,

Keith

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Super User
Super User

@KMI-Keith 
If you put this code in the Items property of the gallery it should do the trick!

 

RenameColumns(
    DropColumns(
        AddColumns(
            GroupBy(SQL_Table_Name,"PartName", "PartID" ,"GroupedItems"),
        "Total Qty", Sum(GroupedItems, Qty)
        ),
    "GroupedItems"
    ),
    "Total Qty",
    "Qty"
);

 

 

You are probably wondering: why are we dropping and renaming columns?  GROUPBY creates an extra column that is unneeded for the data table.

 


The solution I gave is a slightly modified version of this GROUP BY and SUM example:

https://matthewdevaney.com/powerapps-collections-cookbook/group-by-and-find-the-sum/


---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

3 REPLIES 3
eka24
Super User
Super User

On a Gallery items put;

AddColumns(GroupBy(Datasource, "PartName","SumGrp"),"SumColumn", Sum(SumColumn, Qty))

 

One of the label the Gallery should be;

ThisItem.SumColumn

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

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

mdevaney
Super User
Super User

@KMI-Keith 
If you put this code in the Items property of the gallery it should do the trick!

 

RenameColumns(
    DropColumns(
        AddColumns(
            GroupBy(SQL_Table_Name,"PartName", "PartID" ,"GroupedItems"),
        "Total Qty", Sum(GroupedItems, Qty)
        ),
    "GroupedItems"
    ),
    "Total Qty",
    "Qty"
);

 

 

You are probably wondering: why are we dropping and renaming columns?  GROUPBY creates an extra column that is unneeded for the data table.

 


The solution I gave is a slightly modified version of this GROUP BY and SUM example:

https://matthewdevaney.com/powerapps-collections-cookbook/group-by-and-find-the-sum/


---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

@mdevaney Thank you!

This was basically where I was at yesterday but I didnt drop the column. The other thing was I added a filtered (from 1,000 rows down to 125) collection and used that in your formula and it worked as at first it was only showing 1 row with a total of all the parts in the Qty label and nothing in the other 2. Again it could have been because of the size of the SQL DB even though I had set the row up to the 2000 max limit.

 

IDK and right now I dont care as I can stop banging my head against the wall trying things!

 

I appreciate the help!

Keith

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,269)