Forum Discussion
Getting records in Power Query/SQL for missing data
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")
Solution Sagehi 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
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