How to countif and remove duplicates with Power Query

[phillipc1]

Hello,

I am still new to power query and working on what I think is pretty simple, but I am struggling. Essentially I have a column of values that I want to transform into a table that will tell me how many times that value shows up. Below I have the sample data set as well as what I would like to see come out after using power query.

Weekly Overdue for Check-in.xlsx
	F
1	Group Name
2	1234
3	4321
4	1234
5	6478
6	4321
7	4321
8	5843
9	1546
10	8315
11	8315
12	3516
13	4321
14	5843
15	46
16	1685
17	1234
18	1546
19	5843
Sheet1

Weekly Overdue for Check-in.xlsx
	K	L
1	Group Name	Count of Groups
2	1234	3
3	4321	4
4	6478	1
5	5843	3
6	1546	2
7	8315	2
8	3516	1
9	46	1
10	1685	1
Sheet1

Cell Formulas
Range	Formula
L2:L10	L2	=COUNTIF($F$2:$F$19,K2)

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Group Name"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[phillipc1]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Group Name"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Hi Alansidman,

Thank you for the response. I can see the logic surrounding this, but I realized I am not doing this all within the current workbook. I have another workbook that I am using a "Get Data" call from this workbook and transforming the data I pull in. What change to the formula would I need to make to accommodate this? I assume changing the "Source =" section, however I am unsure how I would write that. I have listed below the data source and target workbook names.

Data Source: "Weekly Overdue for Check-in.xlsx"
Data Target: "Open Items Report"

 

[alansidman]

Just load the data in your current workbook from the other then apply the GroupBy line to the new query. It should still start with the let and Source lines.

 

[phillipc1]

Thank you so much! I got it working.