topic Getting records in Power Query/SQL for missing data in DAX Commands and Tips

Getting records in Power Query/SQL for missing data

avidthinker — Sat, 10 Feb 2024 08:08:43 GMT

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

Re: Getting records in Power Query/SQL for missing data

lbendlin — Sat, 10 Feb 2024 21:34:34 GMT

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

Re: Getting records in Power Query/SQL for missing data

talespin — Sun, 11 Feb 2024 14:28:21 GMT

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")

Re: Getting records in Power Query/SQL for missing data

avidthinker — Tue, 20 Feb 2024 09:14:40 GMT

Hi,

Thanks for the explanation and demo.

As I got big dimension tables would you know a SQL equivalent?

Re: Getting records in Power Query/SQL for missing data

talespin — Tue, 20 Feb 2024 10:13:27 GMT

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