Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Identify missing periods and years

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_CodeJob_DescFin_YrQtr$
DEPT1Electrical20171                       9,178.00
DEPT1Landscape20171                       1,788.00
DEPT1Catering20181                           743.00
DEPT1Landscape20181                           683.00
DEPT1Catering20191                           979.00
DEPT1Electrical20191                           532.00
DEPT1Landscape20191                           769.00
DEPT1Electrical20172                       4,649.00
DEPT1Landscape20172                       5,561.00
DEPT1Catering20182                           523.00
DEPT1Landscape20182                           564.00
DEPT1Catering20192                           986.00
DEPT1Electrical20192                           951.00
DEPT1Landscape20192                           752.00
DEPT1Electrical20173                       3,284.00
DEPT1Landscape20173                       5,773.00
DEPT1Catering20183                           653.00
DEPT1Landscape20183                           845.00
DEPT1Catering20193                           693.00
DEPT1Electrical20193                           714.00
DEPT1Landscape20193                           762.00
DEPT1Electrical20174                       1,336.00
DEPT1Landscape20174                       3,034.00
DEPT1Catering20184                           915.00
DEPT1Landscape20184                           945.00
DEPT1Catering20194                           990.00
DEPT1Electrical20194                           767.00
DEPT1Landscape20194                           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_CodeJob_Desc201720182019
DEPT1Catering                 -     2,834.00   3,648.00
DEPT1Electrical   18,447.00                -     2,964.00
DEPT1Landscape   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

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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 @Anonymous . 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:

edhans_0-1629396708940.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
edhans
Super User
Super User

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 @Anonymous . 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:

edhans_0-1629396708940.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
CNENFRNL
Community Champion
Community Champion

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

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

Screenshot 2021-08-19 200157.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.