Forum Discussion
Disable auto detect column types, Is it safe? and incremental refresh
I fetch Data from Odata Api with the Web option and i need to handle with cells that contains letters&numbers(e.g #2345, T888), The only solution that i found is to disable the 'Auto detect types', Is it safe or it can make problems with other tables or other calculation in the future?
There is a way to disable only on specific Table and not in all of the tables?
And about the incremental refresh, If tha data that come from the Api is in format of Date/Time/Timezone, what is the best option to use it with the incremental refresh?
Thank you.
Anonymous Disabling the option only impacts future queries. What option you have ticked when you click publish does not matter at all. What matters is what you see in the 'Applied Steps' of Power Query. Whatever is in there is what will happen on every refresh of your data.
Having the 'detect column types and headers for unstructured sources' box ticked means that Power BI will automatically add the 'Changed Type' step for you in the image above. When you untick that box, Power BI won't add the step automatically and you'll need to do it yourself.
Here is the box for reference for others:
And yes, if you make the function to convert the date and use that function in your data set, Power BI will follow those function steps on every incremental refresh.
4 Replies
- amitchandak
Super User
Anonymous , It can be done only at the global level.
I think in incremental refresh we expect the same data type to continue and the system should not change the same. So if development is done. This is a good option to opt out.
- AllisonKennedy
Community Champion
Anonymous Disable auto detect column types on unstructured will only mean that you need to set the data type for each column rather than Power BI trying to guess it. This can be turned off after you have loaded some tables already, so you could load some sources with Auto-detect on, then turn it off and load other sources with it off.
Alternatively, I just delete the 'Changed Type' step in Power Query whenever I don't want Power BI to detect the column type for that data source. Make sure you set the type though for columns according to what you know they should be (especially if they should be numeric or date).
Re incremental refresh the RangeStart and RangeEnd parameters must be Date/Time data type, but you can create a function to then convert these to Date/TIme/Timezone and then filter on that. Similar to how this article describes but with Integer instead of Timezone: https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure#convert-datetime-to-integer
Thank you AllisonKennedy , If i will disable this option -> load the tables and turn the option back on, what will happend when i will make 'Incremental refresh on those tables, Is it can cause problems?
And if I will make function that convert the date, what will happend when i will make Incremental refresh, the data will automaticlly convert to the function format?
- AllisonKennedy
Anonymous Disabling the option only impacts future queries. What option you have ticked when you click publish does not matter at all. What matters is what you see in the 'Applied Steps' of Power Query. Whatever is in there is what will happen on every refresh of your data.
Having the 'detect column types and headers for unstructured sources' box ticked means that Power BI will automatically add the 'Changed Type' step for you in the image above. When you untick that box, Power BI won't add the step automatically and you'll need to do it yourself.
Here is the box for reference for others:
And yes, if you make the function to convert the date and use that function in your data set, Power BI will follow those function steps on every incremental refresh.
