Distinct Count # of unique dates based on column with duplicates

[romoguy15]

Hello Everyone,

I have a two column table, one with ticket IDs and another with dates. The first column of ticket IDs could have uniqe or duplicate ticket IDs. The other date column could have also have unique or the same dates. What I am trying to accomplish in query editor is to transform the ticket ID column and count the total of unique dates. I tried to use the Group By feature to group all the ticket IDs but it does not let me do a unique count of the closed date column. I tried to post a snip below from excel to illustate what the data looks like ontop, and then below what I would like to transform the data into in query editor. Essentially I am trying to show only the unique ticket IDs in one column and in a new column the total distinct count of closed dates. 

Also, I have some sample data if it helps.

https://1drv.ms/u/s!AqID1H0nHPOzhAx5J1S-cMv2vPn7?e=YN5zDz

Thank you in advance

[C2]

what if I have a visual with a Signout  Date on X axis &  count distinct fo Loads on Y axis, how do I do to be able to add a line to shows the distinct count of loads scheduled for  for that same date of signt out, being that if I  put the Loads column on the Line Y field it will show the count of the Loads that were signed out,  please note that scheduled date is one column & signout its another.

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[romoguy15]

Is there any other additional information I could provide to help with the solution? 

[Ashish_Mathur]

Hi,

What problem are you facing when using Group By in the Query Editor?

[romoguy15]

Hi @Ashish_Mathur , when I use Group By, it does succesfully groups all ticket IDs to only show me the unique ID(kind of like removing duplicates) but it is not showing me the count of each unique date. It only does a count of the dates.  Below is a snip

For example, if ticket ID148475 had four dates, it would count four total dates even though that ID had two dates that were different and two dates that were the same.

[Ashish_Mathur · Accepted solution]

Hi,

This should typically be done directly in the visual but if you must do it in the Query Editor, then this M code works

let
    Source = Excel.Workbook(File.Contents("C:\Users\mathu\Desktop\try.xlsx"), null, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ticket Id", type text}, {"Ticket Closed Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Ticket Id", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Ticket Id"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type nullable date}})
in
    #"Grouped Rows"

[romoguy15]

@Ashish_Mathur this is exactly what I needed, thankyou. I have to do it in query editor specifically because I am merging it to another query within editor and then eliminating everything that does not match. I really apprecaite the solution. Though, I am curious. Did you have another method of doing this in adding a new column with dax?

[Ashish_Mathur]

You are welcome.

[CNENFRNL]

# Distinct Dates = DISTINCTCOUNT( 'Table'[Ticket Closed Date] )

Literally, filter context in DAX already plays the role to group and filter dataset; unnecessary to explicitly write "group by" in most cases.

[romoguy15]

@CNENFRNL  yes but that does not take into account the duplicate ticket IDs. Yes I can do a  distinct count of just the dates but it's not factoring the ticket ID.