Forum Discussion

Einomi's avatar
Einomi
Icon for Helper V rankHelper V
4 years ago
Solved

Conditional Filter

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

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • BA_Pete's avatar
    BA_Pete
    4 years ago

     

    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

7 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Einomi
      Icon for Helper V rankHelper V
      4 years ago

      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's avatar
        BA_Pete
        Icon for Super User rankSuper User
        4 years ago

         

        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

  • Einomi's avatar
    Einomi
    Icon for Helper V rankHelper V
    4 years ago

    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's avatar
      BA_Pete
      Icon for Super User rankSuper User
      4 years ago

       

      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's avatar
        Einomi
        Icon for Helper V rankHelper V
        4 years ago

        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