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

Question

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

(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:

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

Please provide an example of your source data, your desired output, and why you are having trouble getting there. I have trouble following your description — horseyride
– horseyride, Commented Mar 15, 2022 at 14:13
@horseyride I've added screenshots to represent my existing data, and what I would like as output. Thanks! — Chloraphil
– Chloraphil, Commented Mar 15, 2022 at 15:13

horseyride · Accepted Answer · 2022-03-16 20:18:34Z

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

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related