When we start learning Power BI, we might face common data type issues when importing data from other regions. Specifically, Power BI relies on the regional settings of your PC to validate and set data types for columns.
For demonstration purposes, let's use data that originated in the UK, with Power BI set to the US region. Use ChatGPT to generate some random dates in the UK format and load them into a blank table.
After the data has been loaded, Power BI sets the data type for this column to String. This happens because, even though the values are dates, they are not aligned with the US format (MM/DD/YYYY).
Let's try to convert the data type of this column. Power BI will throw an error because the dates are not in the expected format (i.e., MM/DD/YYYY).
So, to overcome this error, Power Query Editor has the option to change the locale (regional settings for the column) in the data type change dropdown called "Using Locale." To use this, follow the steps below:
Step 1: Clear the error by reverting the data type to String or by deleting the last applied step in the Applied Steps section of the Query Settings window.
Step 2: Go to the data type change dropdown and select "Using Locale..."
Step 3: In the "Change Data Type with Locale" window, select the data type as Date and the locale value as English (United Kingdom) from the dropdown, then click OK. Power BI will then convert the data type of the column to Date.
In this way, we can resolve issues not only with date values but also with other data type changes between countries.
I hope you have understood how to change the data type of a column using the Locale option in Power BI's Power Query Editor. If you have any Questions plz put them in the Comments section.
No comments:
Post a Comment