Consolidate by Multiple Criteria

[Anonymous]

Hello All, 

I apologize if I have asked too many questions of the community but I think if I could get help with this data set I am working on it would solve most, if not all my problems. Right now I have a data set that evaluates our company's PO's and the line numbers associated with them. However, the problem is that when we get an upload it could show multiple lines received for one line number (picture to follow). What I am trying to figure out is if I can consolidate the line numbers by having it take the last date received (ItemArrivalJournalDate), keep the dynamic OrderedQuantity and then sum the ReceivedQuantity. Thus, giving accurate data about how well our third-party manufacturers are getting us our product on time. See the table below for reference. 

*Any help on this would be massively appreciated! Thank you!

[Anonymous]

I honestly think that either of these options you both have provided will work!! The only trouble now is trying to get my company to give me access to the Power Query Editor for the table I want to work on. @v-lid-msft , @MFelix 

[MFelix]

Hi @Anonymous ,

Are you getting the information only on a PBIX file or on the web version?

If yes you can do a table visualization with the maximum and sum on the columns no need to change the query.

[v-lid-msft]

Hi @Anonymous ,

As additional supplement of @MFelix 's wonderful solution, we can group by like following screenshot :

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared？

Best regards,

[MFelix · Accepted solution]

Hi @Anonymous ,

On the query editor you must do the Group by option, then you need to select all the columns that you want to keep like Vendor, Item Number, Po Line and then on the aggregation select Maximum for data and sum for Received quantity on the other columns keep the maximum or minimum since they are equal.