Forum Discussion

sonam7's avatar
sonam7
Microsoft Employee
6 years ago

Query Folding on nvarchar dataype column

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

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.