Using Power Query to Get Distinct Count

[seaottr]

Hi all,

I'm struggling to figure this one out...I've searched online and have tried suggestions for other similar requests, but to no avail...

In my data set, I have Survey IDs (unique for each survey), but trying to add a column (Survey #) that indicates what survey # it is for that particular individual. I want to keep my data in the original format but just with the extra column added.

Here are two different views I have of the data, and I can use either depending on which is more efficient (and/or easier to produce the solution):

The "Survey #" column is the one I want to add.

Data Format 1:

Date​	Name​	Survey ID​	Survey #​
1-10-2023​	John Smith​	123456​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-18-2023​	John Smith​	345678​	#2​
1-15-2023​	Peter Griffin​	456789​	#1​
1-16-2023​	Peter Griffin​	567890​	#2​
1-21-2023​	John Smith​	678901​	#3​

Data Format 2:

Date​	Name​	Survey ID​	Survey #​
1-10-2023​	John Smith​	123456​	#1​
1-10-2023​	John Smith​	123456​	#1​
1-10-2023​	John Smith​	123456​	#1​
1-10-2023​	John Smith​	123456​	#1​
1-10-2023​	John Smith​	123456​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-16-2023​	Lisa Simpson​	234567​	#1​
1-18-2023​	John Smith​	345678​	#2​
1-18-2023​	John Smith​	345678​	#2​
1-18-2023​	John Smith​	345678​	#2​
1-18-2023​	John Smith​	345678​	#2​
1-18-2023​	John Smith​	345678​	#2​
1-18-2023​	John Smith​	345678​	#2​
1-18-2023​	John Smith​	345678​	#2​
1-15-2023​	Peter Griffin​	456789​	#1​
1-15-2023​	Peter Griffin​	456789​	#1​
1-15-2023​	Peter Griffin​	456789​	#1​
1-15-2023​	Peter Griffin​	456789​	#1​
1-15-2023​	Peter Griffin​	456789​	#1​
1-15-2023​	Peter Griffin​	456789​	#1​
1-16-2023​	Peter Griffin​	567890​	#2​
1-16-2023​	Peter Griffin​	567890​	#2​
1-16-2023​	Peter Griffin​	567890​	#2​
1-16-2023​	Peter Griffin​	567890​	#2​
1-16-2023​	Peter Griffin​	567890​	#2​
1-16-2023​	Peter Griffin​	567890​	#2​
1-16-2023​	Peter Griffin​	567890​	#2​
1-21-2023​	John Smith​	678901​	#3​
1-21-2023​	John Smith​	678901​	#3​
1-21-2023​	John Smith​	678901​	#3​
1-21-2023​	John Smith​	678901​	#3​

Any help would be greatly appreciated!

 

[alansidman]

Using your first example here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Data", each _, type table [Date=text, Name=text, Survey ID=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date", "Survey ID", "Index"}, {"Date", "Survey ID", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"})
in
    #"Removed Columns"

Name	Date	Survey ID	Index
John Smith	1-10-2023	123456	1
John Smith	1-18-2023	345678	2
John Smith	1-21-2023	678901	3
Lisa Simpson	1-16-2023	234567	1
Peter Griffin	1-15-2023	456789	1
Peter Griffin	1-16-2023	567890	2

 

[seaottr]

Thanks alansidman...However, I seem to be getting the same result as I did after trying other similar approaches. The Index column is still just listing results as 1, 2, 3, 4, 5, 6. Which data set from my original post were you using for your code? Also, I'm curious about the "[Date=text, Name=text, Survey ID=text]" portion. Why is the date field listed as "text"?

 

[alansidman]

Used the first example. As to the date, when I copied and pasted from your example, it would not let me make it date. Obviously, you can change. I did not retype your data. In the future, use XL2BB so that formatting is what you want. When you post as you did, all data copied comes over as text.

Here is a link to my file of your data and the step by step solution

AMS PQ Index.xlsx | Powered by Box

app.box.com

 

[seaottr]

Thank you so much alansidman! Really appreciate the follow-up! After a couple of tweaks based on some additional fields I have in my actual data set and applying your code with the 1st data set format, I was able to get it to work!

Thank so much for the quick response(s)!