filter by date without losing query folding

[Anonymous]

Hi all, 

I am using the HANA connector in import-mode to import a table which has a date-column. I want to filter the query based on this date-column, however it's not formatted as dates. I could easily change the data type to date and then apply a filter based on the date range. However, by doing so I lose query folding, and from what I understood, it then imports all the dates before filtering, which is not what I want. 

The dates are stated as yyyymmdd. 

What are my possibilities? For instance I would like to import only the dates within current and last year, how would I go about that?

Thanks in advance!

[Anonymous]

Hi there. I don't know if this was indeed "solved", as the solutions I read here did not work for me, but I have found a way to at least filter the dates without breaking them (for now). I may still have to change the column data type to 'date' at the end in order for my report to work correctly. Still, filtering dates dramatically lowers the amount of data my query must process, so doing it early on is a huge advantage. This filter removes over 80% of the data from the query right off the top.

Pretty simple solution. Using the Filter Rows function, I am filtering down to text that begins with 2021, 2022, and 2023. This eliminates all of the years I did not want in my report while keeping the ones that did. Now, I can make the transformations I want across the rest of the query, and if absolutely necessary I can change the data to 'date' at the end. That way the query folding will not break until there's the least amount of data left possible. I hope this helps!

[ppm1]

If the dates are truly text (and not integer as suggested already), you may be able to dynamically generate the list of text date values and then use a List.Contains in your column filter. You could quickly find out if it folds with a hard-coded list of dates, before spending time on the dynamic part. Your filter step might look like this (change the Previous Step and DateTextColumn to match your query/data).

= Table.SelectRows(#"Previous Step", each List.Contains({"20220930", "20220929"}, [DateTextColumn]))

Pat

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Changing the data type of a column in Power Query Editor will break query folding, so please delete the "Changed type" step. Then you can try @Daryl-Lynch-Bzy 's suggestions. If your date column in the data source is in number data type, filtering will not break query folding. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Daryl-Lynch-Bzy]

Hi @Anonymous - the value "yyyymmdd" is a date format, so do you mean that the data type of the column is not Date type.

If you are lucky, the data type might by INTERGER, so you can simply try INT > 20211230 and INT < 20220928.

If the data type is Text, this might still work TEXT > "20211230" and TEXT < "20220928".   Otherwise you may want try adding a new column which converts to INT.  See if it is still folding.  Then try to filter the new column.

If is Date Type, you may need to consider using Native Query to force folding to SAP Hana.