Data type conversion

Data type conversion is one of the most common steps in Power Query, yet it is one of the most important ones that can become tricky if not managed well. One cool feature of Power BI, if enabled, is to 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; that is where things may go wrong, as the data types are not detected based on the whole dataset. Instead, the data types are detected based on the part loaded for preview. In most cases, we deal with data type conversion in table values. Either we use 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)

In the Table...