Forum Discussion

akhaliq7's avatar
akhaliq7
Post Prodigy
4 years ago
Solved

id column type text convert to number (query folding)

I have pulled in a table from a database which makes the id column a text data type automatically. This makes my data load slower than my other tables that have a number as their id column. What woul...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi akhaliq7 ,

     

    Did you use Power BI Desktop to connect data? Try to select 'Detect column types and headers for unstructured sources according to each file's setting' in the options.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi BA_Pete 

Could you please explain why we shouldn't change it to a Whole Number type ?

Thanks

BA_Pete's avatar
BA_Pete
Super User
3 years ago

 

Because Int64.Type isn't recognised by SQL Server for query folding unless you use the [UnsafeTypeConversions = true] argument. As this suggests, it's potentially unsafe and, therefore, not a recommendation I'd generally make to users on here.

 

If it's something you really want/need to do, then I'd suggest reading Chris Webb's methods here, and understand the risks:

https://blog.crossjoin.co.uk/2021/06/29/data-type-conversions-for-sql-server-sources-and-query-folding-in-power-query/ 

 

Pete