Creating a Distinct Column Count of a Column Based on Another Column Data

[Asmilinich25]

Hi All, 

I have been struggling to find the solution for this. I have an imported data set where column one has a unique identifier that may be repeated for multiple rows. The other column I have is the date which is translated into a week of month column. See below:

Unique Identifier	Date	Week of Month	Distinct Count Week 1	Distinct Count Week 2	Distinct Count Week 3	Distinct Count Week 4
1XY100000	1/3	1	2	Null	Null	Null
1XY100000	1/5	1	2	Null	Null	Null
1XY100001	1/7	1	2	Null	Null	Null
1XY100001	1/7	1	2	Null	Null	Null
1XY100001	1/7	1	2	Null	Null	Null
1XY100002	1/10	2	Null	2	Null	Null
1XY100003	1/11	2	Null	2	Null	Null
1XY100003	1/11	2	Null	2	Null	Null
1XY100004	1/17	3	Null	Null	1	Null
1XY100004	1/18	3	Null	Null	1	Null
1XY100005	1/25	4	Null	Null	Null	2
1XY100006	1/25	4	Null	Null	Null	2

What I would like to accomplish is to create a column which shows the distinct count in the "Unique Identifier" for the rows that include "Week of Month"=1. 

Currently I have it to where if the "Week of Month"=1 then it will report the distinct count of the entire unique identifier row only next to the "Week of Month" rows that =1. This is what Im currently working with:

= if [Week of Month]=1 then List.NonNullCount(List.Distinct(#"Promoted Headers"[Unique Identifier])) else Null

This give me what you see in "Distinct Count Week 1" column with the exception that the number displayed is "7" which is the distinct count for the entire "Unique Identifier" row. 

How do I get this to display a distinct count "Unique Identifier" of only the rows where "Week of Month"=1? 

*Distinct count columns are the outputs I am looking to get from my table data using Power Query.

Any insight on the solution is appreciated. Thanks!

[Asmilinich25]

KT, 

Thank you for the reply. When I input this in and adjust it to my sheet I am not quite getting the right result. 

For me this reported the row count for each individual "Unique Identifier" in it's respecitve row. In other words it reported the following: 

Unique Identifier	Date	Week of Month	Distinct Count Week 1	Distinct Count Week 2	Distinct Count Week 3	Distinct Count Week 4
1XY100000	1/3	1	2	Null	Null	Null
1XY100000	1/5	1	2	Null	Null	Null
1XY100001	1/7	1	3	Null	Null	Null
1XY100001	1/7	1	3	Null	Null	Null
1XY100001	1/7	1	3	Null	Null	Null
1XY100002	1/10	2	Null	1	Null	Null
1XY100003	1/11	2	Null	2	Null	Null
1XY100003	1/11	2	Null	2	Null	Null
1XY100004	1/17	3	Null	Null	2	Null
1XY100004	1/18	3	Null	Null	2	Null
1XY100005	1/25	4	Null	Null	Null	1
1XY100006	1/25	4	Null	Null	Null	1

What I was looking for was it to search in "Week of Month" column and if Week of Month=1 report back the distinct number of Unique identifer numbers in the Unique Identifier column into the created column. So for Week 1 there are two unique identifiers in column 1 (1XY100000 & 1XY100001). So I would want the output in the created column to be "2"  for all rows being that distinctly 2 unique identifiers were found in rows where Week of Month=1.

Thank you in advance for anything else you may suggest. 

[KT_Bsmart2gethe]

Hi @Asmilinich25 ,

Add a custom column and add the code below:

*replace the #"Changed Type" with your previous step name.

Table.SelectRows( 
   Table.Group(
      #"Changed Type", 
      {"Unique Identifier"}, 
      {
        {"Count", 
         each Table.RowCount(Table.Distinct(_)), 
         Int64.Type}
      }
   ), 
   (x)=> [Unique Identifier] = x[Unique Identifier] 
)[Count]{0}

Regards

KT