Query Folding on nvarchar dataype column

[sonam7]

Hi,

I have a .pbix file with mixed storage i.e both import as well as direct query tables. Few of my tables in import mode are very huge containing millions of records. Loading all the data in powerbi from these tables affects the speed of .pbix file. I want to enable query folding to fetch only past 3 months data in the .pbix file. However, the caviat is that the datetime columns are of data type nvarchar. 

If I change the data type to datetime in query editor it disables the query folding feature. I am looking for suggestions to enable query folding and fetch only past 3 months data using nvarchar type datetime column. Please let me know if it is possible and how?

Thanks,

Sonakshi

[v-juanli-msft]

Hi @sonam7 

Please change the datetime columns on the SQL Server database (or other source) to datetime type instead of nvarchar.

It doesn't support to change the column type in Power BI Desktop side when query folding.

You could also pass parameter in the connnection to limit data imported into Power BI.

Please share datetime columns format with me so i can have a test.

https://www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.