PowerQuery - How do I count events by dates spanning multiple years?

I would like to display activity counts by year e.g.

Year Created Approved Resolved
2017 10 5 19

the source records look like this:

Record ID Created Date Approved Date Resolved Date
123456 9/17/2017 1/15/2018 11/1/2019

Using the example record the report for 2017 should look like:

Year Created Approved Resolved
2017 1 0 0

I've linked the source table to a Calendar using the record Created Date. When I try to count any other status but Created, however, I get the count of records based on when they were created, and not when the event occurred:

Year Created Approved Resolved
2017 1 1 1

To count Date Resolved I've used: =calculate(count('source'[Resolved Date]),'calendar'[date]), which I know is incorrect. I want the count to be the number of items occurring during the year specified. Any help would be appreciated!

1 answer

  • answered 2021-09-27 16:21 horseyride

    Sounds like you are looking for BI solution, but in case anyone ever needs a Powerquery/M solution

    right click record_id and unpivot other columns

    right click value column and tranform year...year

    right click remove record_id column

    add column, custom column, with formula =1

    click attribute column and transform...pivot .. using the new custom column as value column

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record ID", Int64.Type}, {"Created Date", type date}, {"Approved Date", type date}, {"Resolved Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Record ID"}, "Attribute", "Value"),
    #"Extracted Year" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Date.Year, Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Year",{"Record ID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Attribute]), "Attribute", "Custom", List.Sum)
    in #"Pivoted Column"
    

    enter image description here

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum