Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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 @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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt 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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt involves an intrinsic AUTO-EXIST mechanism, I think.
https://www.sqlbi.com/articles/understanding-dax-auto-exist/
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! |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
45 | |
28 | |
14 | |
14 | |
13 |