Forum Discussion

greenmonsta's avatar
greenmonsta
Frequent Visitor
4 years ago
Solved

count data across mutiple ranges

Hi All, I am trying to solve the following in Power Query. In the data table I have Ticket Number, the date the ticket was created and the date the ticket was resolved. If the resolved cell is blan...
Need Help
  • ImkeF's avatar
    ImkeF
    4 years ago

    Hi greenmonsta ,
    didn't get that requirement before.
    You can adjust like this:

    let
  Source = Sprints, 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Days", 
    each {Number.From([Start]) .. Number.From([End])}
  ), 
  #"Expanded Days" = Table.ExpandListColumn(#"Added Custom", "Days"), 
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Days", {{"Days", type date}}), 
  Merge = Table.NestedJoin(
    #"Changed Type", 
    {"Days"}, 
    Tickets_Exanded, 
    {"Dates"}, 
    "Starts", 
    JoinKind.LeftOuter
  ),
    Expanded = Table.ExpandTableColumn(Merge, "Starts", {"Ticket Num", "Opened Date", "Closed Date"}, {"Ticket Num", "Opened Date", "Closed Date"}),
    AddOpened = Table.AddColumn(Expanded, "Opened", each if [Days] = [Opened Date] then [Opened Date] else null),
    AddClosed = Table.AddColumn(AddOpened, "Closed", each if [Days] = [Closed Date] then [Closed Date]  else null),
    AddIgnoreForOpen = Table.AddColumn(AddClosed, "IgnoreForOpen", each  [Opened Date] >= [Start] and [Closed Date] <= [End]),
    #"Grouped Rows" = Table.Group(
    AddIgnoreForOpen, 
    {"Sprint", "Start", "End"}, 
    {
      {
        "Tickets Created", 
        each List.Count(List.Distinct(List.Select(_[Opened], each _ <> null))), 
        Int64.Type
      }, 
      {
        "Tickets Closed", 
        each List.Count(List.Distinct(List.Select(_[Closed], each _ <> null))), 
        Int64.Type
      }, 
      {"Tickets Open", each List.Count(List.Distinct(Table.SelectRows(_, (x) => not x[IgnoreForOpen])[Ticket Num])), Int64.Type}
    }
  )
in
  #"Grouped Rows"


    Also check enclosed file.

  • greenmonsta's avatar
    greenmonsta
    Frequent Visitor
    4 years ago

    Hi ImkeF , Appreciate the feed back. Unless I am missing something(and I very well could be) this is still a Dax solution. I'm not using Powe BI. I am using Power Query to prepare the data for output into an Excel spreadsheet. I think I have a solution though I have not fully varified my results yet. This is executed while in the Sprint date table in Power Query. The big change in thought for me was comapring the Created date to the Sprint end date as oppsed to usint the Sprint start date for comarisions. Greg_Deckler  I will update this thread once I have had more time to verify my reults.

    = List.Count(
    Table.SelectRows(
    Add_Sprint_End,
    (IT) =>
    IT[Resolved] > [End Date] and
    IT[Created] <= [End Date]
    ) [Merged]
    )