Identify missing periods and years

[ccarpent]

Hi

Identify missing periods and years from a report, what I mean is I want to still show in my report any missing data but display these results as a zero amount, even if that Fin Year or Fin Period is not present in my source data.   I did look at using a table which would act as a list or lookup for every combination; all the depts, all the job_desc's along with all the quarters and years.   I then merged this lookup table with my data,  using a 'Left Anti' , but the lookup table in the real model would be very large.  Just wondering if there is an easier solution, through M Langauge, to active the same result?  

This is my sample Input data:

dept_Code	Job_Desc	Fin_Yr	Qtr	$
DEPT1	Electrical	2017	1	9,178.00
DEPT1	Landscape	2017	1	1,788.00
DEPT1	Catering	2018	1	743.00
DEPT1	Landscape	2018	1	683.00
DEPT1	Catering	2019	1	979.00
DEPT1	Electrical	2019	1	532.00
DEPT1	Landscape	2019	1	769.00
DEPT1	Electrical	2017	2	4,649.00
DEPT1	Landscape	2017	2	5,561.00
DEPT1	Catering	2018	2	523.00
DEPT1	Landscape	2018	2	564.00
DEPT1	Catering	2019	2	986.00
DEPT1	Electrical	2019	2	951.00
DEPT1	Landscape	2019	2	752.00
DEPT1	Electrical	2017	3	3,284.00
DEPT1	Landscape	2017	3	5,773.00
DEPT1	Catering	2018	3	653.00
DEPT1	Landscape	2018	3	845.00
DEPT1	Catering	2019	3	693.00
DEPT1	Electrical	2019	3	714.00
DEPT1	Landscape	2019	3	762.00
DEPT1	Electrical	2017	4	1,336.00
DEPT1	Landscape	2017	4	3,034.00
DEPT1	Catering	2018	4	915.00
DEPT1	Landscape	2018	4	945.00
DEPT1	Catering	2019	4	990.00
DEPT1	Electrical	2019	4	767.00
DEPT1	Landscape	2019	4	500.00

The result is to show a zero for any missing combinations, as they do not have a budegt or no ependiture.

This is my output, note the '-' or zero amounts are for any items not present in my orginal dataset.

dept_Code	Job_Desc	2017	2018	2019
DEPT1	Catering	-	2,834.00	3,648.00
DEPT1	Electrical	18,447.00	-	2,964.00
DEPT1	Landscape	16,156.00	3,037.00	2,783.00

Any thoughts or suggstions, or am I approaching this from the wrong angle maybe?

Many thanks

Chris

[edhans · Accepted solution]

It is probably a bit more involved than you want it to be, but you should check out this article. How to return 0 instead of BLANK in DAX - SQLBI

You can do this in Power Query too @ccarpent . See code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdW9asMwEMDxVwmaRdHX6U5zmq1Dh27Bg3FMCYRQ0jxQn6VPVoeCQyLBSSc8GHv4cUh/y/u9et29f1il1e40T9fLcRpPy4MzFpfb7f3vT/21SdoivRijBn2X38bz4Xsav+YO2GqkDN6O1/lyPH/+uyRwVx+DZ8bu4iNl/OPwqUdPmJ71593s8sE7ZnG6eIzs+LdmXGszQceQyYUYm2HQEC0fY6t7911NjHI+Bj5GsZ4o1sQo9yFb+kKMYh4ha70Uo29txmtH2bIXYmyGQSMyhwsJ3NWPUBOjmKcAfIzy4VM2fClGsY+W29M+PlbFGNr/pt5nX2khxmbYa+OZw4UE7uonm9VSiFHO18Qo15OpiVHsY8SKGMU8mGV8NQx/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dept_Code = _t, Job_Desc = _t, Fin_Yr = _t, Qtr = _t, #"$" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"$", Currency.Type}, {"Qtr", Int64.Type}, {"Fin_Yr", Int64.Type}}),
    JobDesc = Table.Distinct(Table.SelectColumns(#"Changed Type", "Job_Desc")),
    Years = Table.Distinct(Table.SelectColumns(#"Changed Type", "Fin_Yr")),
    Qtr = Table.Distinct(Table.SelectColumns(#"Changed Type", "Qtr")),
    DeptCodes = Table.Distinct(Table.SelectColumns(#"Changed Type", "dept_Code")),
    #"Added Custom" = Table.AddColumn(DeptCodes, "Job_Desc", each JobDesc),
    #"Expanded Job_Desc" = Table.ExpandTableColumn(#"Added Custom", "Job_Desc", {"Job_Desc"}, {"Job_Desc"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Job_Desc", "Fin_Yr", each Years),
    #"Expanded Fin_Yr" = Table.ExpandTableColumn(#"Added Custom1", "Fin_Yr", {"Fin_Yr"}, {"Fin_Yr"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Fin_Yr", "Qtr", each Qtr),
    #"Expanded Qtr" = Table.ExpandTableColumn(#"Added Custom2", "Qtr", {"Qtr"}, {"Qtr"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Qtr", "$", each 0),
    #"Appended Query" = Table.Combine({#"Added Custom3", #"Changed Type"}),
    #"Grouped Rows" = Table.Group(#"Appended Query", {"dept_Code", "Job_Desc", "Fin_Yr", "Qtr"}, {{"Amount", each List.Sum([#"$"]), type number}})
in
    #"Grouped Rows"

You get this:

It does this by creating a table of all possible combinations of department, job code, year, and quarter with $0, then appends that with the original data. I then group it all to get rid of duplicate combinations and have a final total.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[CNENFRNL]

It involves an intrinsic AUTO-EXIST mechanism, I think.

https://www.sqlbi.com/articles/understanding-dax-auto-exist/