Forum Discussion

BoatAnalytics's avatar
BoatAnalytics
Frequent Visitor
1 year ago
Solved

Distinct Count ticket IDs based off Yesterdays entries

Hi, 

 

I wanted to add a Custom Column in Power Query because I have 2 Million rows or tickets. Doing this in a calcuated column slows down Power Bi desktop too much. 

 

I wanted to write a Custom Column or some sort of M that will return a Distinct count of Ticket IDs based on a Dates Column. 

 

What is the best way to do this?

 

Thanks for any help! 

Need Help
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    I created a 1 miljon record test file (on the left) with this format:

    This code produces the result on the right in a matter of seconds (in Excel)

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Issue Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Issue Date"}, {{"Count", each List.Count(List.Distinct([Ticket ID]))}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Issue Date", Order.Ascending}})
in
    #"Sorted Rows"

     

     I threw in a sort for good measure, but it is not needed....

    Actually, only the #"Grouped Rows" line is "needed".

3 Replies

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    I created a 1 miljon record test file (on the left) with this format:

    This code produces the result on the right in a matter of seconds (in Excel)

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Issue Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Issue Date"}, {{"Count", each List.Count(List.Distinct([Ticket ID]))}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Issue Date", Order.Ascending}})
in
    #"Sorted Rows"

     

     I threw in a sort for good measure, but it is not needed....

    Actually, only the #"Grouped Rows" line is "needed".

  • BoatAnalytics's avatar
    BoatAnalytics
    Frequent Visitor
    1 year ago

    Thats Great thank you!

     

    Can I add a filter on it to say Count the number date entries based off only yesterdays date? Today() - 1 for yesterdays count total?

     

    Or/and a Filter that says count that dates not including another column being blank()?

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    Sure

    I added the #"Filtered Rows" and sorted desc so you can see today is left out

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Issue Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Issue Date] < Date.From(DateTime.FixedLocalNow())),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Issue Date"}, {{"Count", each List.Count(List.Distinct([Ticket ID]))}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Issue Date", Order.Descending}})
in
    #"Sorted Rows"