Getting records in Power Query/SQL for missing data

[avidthinker]

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

[talespin · Accepted solution]

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)

Measure 

TestMeasure =

VAR _SelDt = SELECTEDVALUE('CALENDAR'[Date])

VAR _IsValid = CALCULATE( VALUES('CALENDAR'[IsValid]), 'CALENDAR'[Date] = _SelDt)

RETURN IF(_IsValid = 1, "Report Exists", "No Report")

[avidthinker]

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

[lbendlin]

to report on things that are not there you need to use disconnected tables and cross joins.