id column type text convert to number (query folding)

[akhaliq7]

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 would anyone suggest I do leave it as it is, break my query folding as the last step in power query and change the data type to a number or is there anything else I can do?

FYI: My queries are also using query folding.

[Anonymous · Accepted solution]

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]

Instead of using the default type change, try using Table.TransformColumns instead of Table.TransformColumnsTypes.

=Table.TransformColumns(#"Replace Value", {{"Id", Number.From}})

You could also try Int64.From instead of Number.From.

--Nate

[akhaliq7]

I tried this and it still breaks the query folding. 

[akhaliq7]

When i convert my text column to a decimal number it still breaks my query folding

When i right click on the changed type step the view native query is greyed out

[BA_Pete]

Hi @akhaliq7 ,

No reason why changing this field to a number should break query folding, just make sure to change it to Decimal type, NOT Whole Number type.

Pete

[Mkeva]

Hi @BA_Pete 

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

Thanks

[BA_Pete]

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

Pete