Forum Discussion
Getting records in Power Query/SQL for missing data
I have data from a table in SQL that reports telemetry
Product Reporting Date
A 05/02/2024
A 04/02/2024
I want to against a date table return
Product A 09/02/2024 No Report
Product A 08/02/2024 No Report
Product A 07/02/2024 No Report
Product A 06/02/2024 No Report
Product A 11/12/2023 No Report
How do I go about this?
I can get data for dates not found if checking on date but I do not get my product
hi avidthinker ,
I hope this is what you are looking for. In my setup, I have a Date table and a Fact table. I have created a column in my Date table "IsValid", I set it to 1 if date exists in Fact Table else 0. Data is on the right side table visual in screenshot below.
Calculated Column
IsValid =VAR _SelDate = [Date]VAR _IsValid = CALCULATE( MAX(QtyTbl[Inventory date]), QtyTbl[Inventory date] = _SelDate)RETURN IF( ISBLANK(_IsValid), 0 , 1)MeasureTestMeasure =VAR _SelDt = SELECTEDVALUE('CALENDAR'[Date])VAR _IsValid = CALCULATE( VALUES('CALENDAR'[IsValid]), 'CALENDAR'[Date] = _SelDt)RETURN IF(_IsValid = 1, "Report Exists", "No Report")
4 Replies
- lbendlin
Super User
to report on things that are not there you need to use disconnected tables and cross joins.
- talespin
Solution Sage
hi avidthinker ,
I hope this is what you are looking for. In my setup, I have a Date table and a Fact table. I have created a column in my Date table "IsValid", I set it to 1 if date exists in Fact Table else 0. Data is on the right side table visual in screenshot below.
Calculated Column
IsValid =VAR _SelDate = [Date]VAR _IsValid = CALCULATE( MAX(QtyTbl[Inventory date]), QtyTbl[Inventory date] = _SelDate)RETURN IF( ISBLANK(_IsValid), 0 , 1)MeasureTestMeasure =VAR _SelDt = SELECTEDVALUE('CALENDAR'[Date])VAR _IsValid = CALCULATE( VALUES('CALENDAR'[IsValid]), 'CALENDAR'[Date] = _SelDt)RETURN IF(_IsValid = 1, "Report Exists", "No Report")Hi,
Thanks for the explanation and demo.
As I got big dimension tables would you know a SQL equivalent?
- talespin
Hi avidthinker
It will be like this, let me know if you have any issues
Date table with all the dates.
Product Table with some records.Logic : You are doing a left outer join between Dates and Product table, which will return all dates from Dates table and matching rows from Product table, whereever there is no matching row, the date column from Product table will be blank, you just need to check if its blank or non-blank using CASE or IIF(For SQL Server).
SELECT Dates.Date, Use CASE or IIF and check if ProductTable.Dates is not blank then "Report Exists" else "No Report"
from Datetable
LEFT OUTER JOIN ProductTable
on Dates.Date = ProductTable.Dates
