Search code examples
powerbipowerquerypowerbi-desktopm

Power BI Advanced Editor - How to run query if current date is NOT within a list of values?

I want to run a query in Advanced Editor, only if the current date is NOT one of the following - 1, 5, 10, 15, 20, 25. Otherwise I dont want the query to run.

I have written the below but I am not getting anywhere very fast:

let
    Output = if Date.Day (DateTime.LocalNow()) in (1,5,10,15,20,25) then null else 
    let
    Source = PBILifts
in
    #"PBILifts"

What am I doing wrong? Cheers for all help

Edit: So based on suggestion by @Aleksei I tried below but it doesnt work:

let
    output= if List.Contains({1,5,10,15,20,25}, Date.Day(DateTime.LocalNow())) then #table({},{}) else 
    Source = PBILifts
in
    #"PBILifts"

I get the below error:

An error occurred in the ‘’ query. Expression.Error: The name 'Source' wasn't recognized.  Make sure it's spelled correctly

Any further help please?

Solution

  • It's PQ, not SQL, so in (1,5,...) syntax is incorrect. Try something like this (assuming, your query's output is table):

    = if List.Contains({1,5,10,15,20,25}, Date.Day(DateTime.LocalNow())) then #table({},{}) else YourQuery

    or:

    let
    output = if List.Contains({1,5,10,15,20,25}, Date.Day(DateTime.LocalNow())) then #table({},{}) else PBILifts
in
    output