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

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

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

@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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (57,604)