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

Like (0) Share

Report

Posted on 5 Aug 2020 16:40:40 by KMI-Keith

115

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:

PartName	PartID	POID	Qty
57X60SC-CS320	180	1001	50
52X40CE-977F	79	1002	60
57X60SC-CS320	180	1003	50
64X180AO-649X	102	1004	12
52X40CE-977F	79	1005	60

I would Like the Data to look like this:

PartName	PartID	Qty
57X60SC-CS320	180	100
52X40CE-977F	79	120
64X180AO-649X	102	12

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

All responses (3)

Answers (1)

KMI-Keith 115 on 05 Aug 2020 at 18:34:09

Like (1)

Report

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

@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
Verified answer

mdevaney 29,987 Super User 2025 Season 1 on 05 Aug 2020 at 17:05:03

Like (1)

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

@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."
eka24 20,921 on 05 Aug 2020 at 17:04:37

Like (1)

Report

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

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.