Data type conversion
Data type conversion is one of the most common steps we take in Power Query, yet it is one of the most important ones that can become tricky when it's not managed well. One cool feature of Power BI, if enabled, is that it can detect data types automatically. While this is a handy feature in many cases, it can be the root cause of some issues down the road. The critical point to note is how Power BI automatically detects data types. Power BI automatically detects column data types based on the first few hundred rows.
This is when things can go wrong, as the data types are not detected based on the whole dataset. Instead, the data types are detected based on part of it. In most cases, we deal with data type conversion in table values. Either we use the Power Query Editor UI or manually write the expressions; here, we use the following function:
Table.TransformColumnTypes(Table as table, TypeTransformations as list, optional Culture as nullable text)...