Search code examples
powerbipowerquerypowerbi-paginated-reports

How to create zero-count rows in recordset in Power Query / Power BI when summarizing data

I'm trying to use Power BI, with Power Query, to summarize some data for an SSRS/Paginated Report. What I want to display to the user in the report, is a table with counts of list items. Columns will be grouped by a field called "Office", and rows will be grouped by another field called "AgeGroup", along with a Totals row.

What I want out of PowerBI/Power Query is a recordset with three columns, "Office", "AgeGroup", and "Count". My issue right now, is if there are zero list items that match a given combination (for example, zero items that have both an "Office" field value of "OPQ", and an "AgeGroup" field value of "< 5 years old")) then there will be no line in my result table for that combination ("OZR" and "< 5 years old") when what I want is that line to be present, but with a "Count" value of zero. In other words, if I have 15 different values for "Office", and 3 possible values for "AgeGroup", I want my result recordset to have 45 rows (15*3 = 45). I'm hoping there is a formula or expression that can be used to solve my problem.

I believe using the LookupSet in SSRS/Paginated Report could accomplish this as well, however I thought this would be a good opportunity to learn something new about Power BI / PowerQuery.

EDIT: Some images to explain, I hope.

Assume I have a total of three offices that should be displayed in the final report (OfficeA, OfficeB, & OfficeC).

First image: My "dataset":

enter image description here

(Important - note the absence of "OfficeC" in the dataset.

Second image: What I would like as output from Power Query, to be consumed as a Dataset in SSRS/Paginated Report:

enter image description here

Third image: What I want displayed in my Paginated Report to the end user:

enter image description here

Solution

  • Ah.

    Load a list of offices into PowerQuery and name the query OfficeList with a single column named OfficeList. File close and load, connection only

    Load a list of Age Groups into PowerQuery and name the query AgeGroupList with a single column named AgeGroupList. File close and load, connection only

    Load your source data into power query and use code below, pasted into home ... advanced editor.... check you are using the right Table name in the first row. Mine was Table3

    What it does (a) take source data and group on Office and Age Group, and counts instances (b) create a table with every combination of Office and Age Group (c) merge a into b (d) pivot data sideways to better format

    If you need image 2 instead of image 3, remove the last pivot step

    enter image description here

    // group and count the source data
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Office", type text}, {"Age Group", type text}, {"Tracking #", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Office", "Age Group"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

// set up shell to receive that data
#"Added Custom" = Table.AddColumn(OfficeList, "Custom", each AgreGoupList),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"AgeGroupList"}, {"AgeGroupList"}),

// merge in source data and pivot
#"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"OfficeList", "AgeGroupList"}, #"Grouped Rows", {"Office", "Age Group"}, "Table3", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table3", {"Count"}, {"Count"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Table1", List.Distinct(#"Expanded Table1"[OfficeList]), "OfficeList", "Count", List.Sum)
in  #"Pivoted Column"

    Alternate method

    let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Office", type text}, {"Age Group", type text}, {"Tracking #", Int64.Type}}),
ConvertToZero = Table.TransformColumns(#"Changed Type",{{"Tracking #", each 1, Int64.Type}}),
// create a table to pad real data
#"Added Custom" = Table.AddColumn(OfficeList, "Custom", each AgreGoupList),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"AgeGroupList"}, {"AgeGroupList"}),
// merge the lists and pivot
#"Renamed Columns" = ConvertToZero & Table.RenameColumns(#"Expanded Custom",{{"OfficeList", "Office"}, {"AgeGroupList", "Age Group"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Office]), "Office", "Tracking #", List.Sum)
in  #"Pivoted Column"