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 Custo...
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".

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".