create a summary table in power pivot or power query

Question

System: Excel 2013

A master file has been created with multiple columns including 1 ID column and 2 date columns (creation date and release date).

ID      CreateDate  Release Date
124520  04/09/2015  05/09/2015
228551  05/09/2015  07/09/2015
228552  06/09/2015  08/09/2015

How can I create a summary table that looks like below using PowerPivot or PowerQuery?

Date        NumOfIDsCreated     NumOfIDsReleased
04/09/2015  1                   0
05/09/2015  1                   1
06/09/2015  1                   0
07/09/2015  0                   1
08/09/2015  0                   1

Currently, I create this table in Excel with two powerpivot tables (with date and count) and a separate table with all Dates to run lookup from both powerpivot tables. Is there a better way of doing this?

Answer 1

Here is an alternative way to convert your table

The key point is - use calculatable record in group_by_date.

let
    src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    change_types = Table.TransformColumnTypes(src,{{"CreateDate", type date}, {"ReleaseDate", type date}}),
    unpivot = Table.UnpivotOtherColumns(change_types, {"ID"}, "attr", "value"),
    group_by_date = Table.Group(unpivot, {"value"}, {{"rec", each 
    [
        NumOfIDsCreated=List.Count(List.Select([attr], each _="CreateDate")),
        NumOfIDsReleased=List.Count(List.Select([attr], each _="ReleaseDate"))
    ]}}),
    expand = Table.ExpandRecordColumn(group_by_date, "rec", {"NumOfIDsCreated", "NumOfIDsReleased"}, {"NumOfIDsCreated", "NumOfIDsReleased"}),
    sort = Table.Sort(expand,{{"value", Order.Ascending}})
in
    sort

Answer 2

If you want to create this as a table in the Power Pivot model, then Power Query is the way to go for versions of Power Pivot before 2016, and there is another answer showing that.

If you're using 2016+ (this feature should appear in the GA version of Excel from what I've heard) or the Power BI Desktop app, then you can create calculated tables in your model using DAX:

Assuming an active relationship between FactTable[CreateDate] and DimDate[Date], and an inactive relationship between FactTable[ReleaseDate] and DimDate[Date].

CreateDateCount:= // Note this is a measure
COUNTROWS(FactTable)

ReleaseDateCount:= // also a measure
CALCULATE(
    COUNTROWS(FactTable)
    ,USERELATIONSHIP(FactTable[ReleaseDate], DimDate[Date])
)

SummarizedFact= // note this is a calculated table
FILTER(
    ADDCOLUMNS(
        DimDate
        ,"IDsCreated"
        ,[CreateDateCount]
        ,"IDsReleased"
        ,[ReleaseDateCount]
    )
    ,[IDsCreated] || [IDsReleased]
)

Finally, I wonder why this needs to exist as a table in your model, as it seems like a reporting need, and typically in a Power Pivot model you'd do this work in a pivot table, not in the PP model. If that is the use case you actually need, then the measures I've written above will work for that as well.