Max Value of a count per group in Power Query

[CMDATA]

Hello,

I'm trying to get the max value of a count of items per group. Ideally this would be done in power query so I can have a table and map to it. I have another table that I can map to the group and I'd like to pull the most prevalent item per group into that table. 

Thank you in advance!!

Data Sample CSV file

Table from report to show counts per item: 

Job	Group	Item ID	Count of Item ID
210001.500	210001-01CC	00001	5
210001.500	210001-01W	00001	1
210001.500	210001-03A	00002	1
210001.500	210001-26A	00001	3
210001.500	210001-26A	00003	3
210001.500	210001-26A	00026	14
210001.500	210001-26A	00033	22
210001.500	210001-28B	00028	2
210001.500	210001-31A	00002	6
210001.500	210001-31A	00026	3
210001.500	210001-31A	00031	90
210001.500	210001-31A	00032	2
210001.500	210001-31A	00033	28
210001.500	210001-31A1	00033	126
210001.500	210001-31B	00001	3
210001.500	210001-31B	00002	1
210001.500	210001-31B	00031	35
210001.500	210001-31B	00033	7
210001.500	210001-31B1	00033	123
210001.500	210001-31C	00031	7
210001.500	210001-31E	00001	1
210001.500	210001-33A	00002	2
210001.500	210001-33A	00003	2
210001.500	210001-33A	00009	3
210001.500	210001-33A	00026	1
210001.500	210001-33A	00033	33
210001.500	210001-99A	00001	5
210001.500	210001-99A	00002	1
210001.500	210001-99A	00005	1
210001.500	210001-99A	00026	3
210001.500	210001-99A	00028	2
210001.500	210001-99A	00032	48

Desired Result: 

Job	Group	Item ID	Count of Item ID (Shown for reference, not needed in the final table)
210001.500	210001-01CC	00001	5
210001.500	210001-01W	00001	1
210001.500	210001-03A	00002	1
210001.500	210001-26A	00033	22
210001.500	210001-28B	00028	2
210001.500	210001-31A	00031	90
210001.500	210001-31A1	00033	126
210001.500	210001-31B	00031	35
210001.500	210001-31B1	00033	123
210001.500	210001-31C	00031	7
210001.500	210001-31E	00001	1
210001.500	210001-33A	00033	33
210001.500	210001-99A	00032	48

[CMDATA · Accepted solution]

Hello, 

After I posted this I found a site that showed how to use multiple in group bys and then pull the data out. So I did:

1. Group by to get the columns I wanted with the counts of the groups

2. Group by to get the Max Count of each group along with the All Rows option. 

3. Custom Column using Table.Max to get the records associated with the Max count per group. 

4. Expanded the record column to get the value in the detail record that I wanted (the Item ID). 

Thank you all for your replies! 

[v-robertq-msft]

Hi, @CMDATA 

Glad to hear that you have found a solution by yourself and thank you for sharing your experience. Would you like to mark your own reply as a solution so that others can learn from it too?

Thanks in advance!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft]

Hi, @CMDATA 

According to your description and picture, you want to get a table that contains the most count of ID group by [group], I think you can also achieve this using a calculated table, which can also be mapped. You can take a look at my method and find if it’s useful:

1. Create a measure:

Rank =

RANKX(FILTER(ALL('Table'),[Group]=MAX([Group])),CALCULATE(MAX('Table'[Count of Item ID])),,DESC,Dense)

2. Create a calculated table:

Table 2 =

FILTER('Table',[Rank]=1)

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@CMDATA,

You can use the Group By wizard in Power Query. Right-click the column [Count of Item ID] and select Group By. Configure as follows:

Result: