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...
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

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