Conditional Filter

[Einomi]

Hello,

I have a structured table in Excel recording sales, and naturally I have a column date

I have another table called DateMin

I have another table called DateMax

I have three queries in PQ

My sales tables loaded as a table

My tables DateMin and DateMax loaded as a connection

I would like to filter my sales tables between my two dates the start date should be taken from the table DateMin and the end date from the table DateMax

= Table.SelectRows(Custom, each [Date] >= DateMin and [Date] <= DateMax )

So far so good 😁

What I would like is, if the table DateMin is empty I would like to take the oldest date till the date containted in DateMax

and if DateMax is empty I would like to see the rows from the starting date in DateMin till the most recent dates ?

I hope it makes sense 🙂

Thanks for your time and your help

[Einomi]

Hi @BA_Pete 

Thanks I feel we are close, however I tried the code left DateMax null when I refreshed I got this error message

Expression.Error: A cyclic reference was encountered during evaluation.

I am attaching pictures of my three M codes

First One is my Sales Table

Second One is my DateMin

Third One is my DateMax

Sales Table

[BA_Pete · Accepted solution]

Ah, yes, that makes sense. My bad.

This is always going to be a cyclic reference as you're applying this as a step in your fact table.

Revert your DateMin/Max queries back to how they were originally, then try this as your final fact table step:

Table.SelectRows(
    #"Personnalisee ajoutee",
    each [Date] >= (DateMin ?? #date(1900,01,01))
        and [Date] <= (DateMax ?? #date(2999,12,31))
)

Pete

[Einomi]

Works Perfect 😁

Many thanks

PS : if you have a few min to send me links about the ?? or to explain it to me you are more than welcome

always want to learn and share 

[BA_Pete]

In Power Query, ?? is the coalesce operator i.e. it converts null values to a chosen value.

In the case of (X ?? Y) this translates to "if X is null then Y".

Pete

[BA_Pete]

Hi @Einomi ,

I'd make the edit in your DateMin/Max queries to make usage in the fact table easier.

How are your DateMin/Max queries currently calculated?

Pete

[Einomi]

Hi @BA_Pete 

Thanks

let
    Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
    StartDate = ChangedType{0}[StartDate]
in
    StartDate

this is how my two queries DateMin and DateMax are calculated.

I have one query for DateMin (above) and one for DateMax

Once I have loaded the table to PQ I have justt drilled down the value

[BA_Pete]

Ok. So DateMin and DateMax are essentially manually entered in the workbook. No problem.

Try this in your DateMin/Max queries:

let
    Source = Excel.CurrentWorkbook(){[Name="Date"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
    varDate = ChangedType{0}[StartDate],
    StartDate = if varDate = null then List.Min(yourFactTable[DateColumn]) else varDate
in
    StartDate

In your EndDate query, just change StartDate for EndDate in the varDate variable, and List.Min for List.Max in the StartDate step.

Pete