Different locales (also called regions) have different ways to display data. For example, some regions use a default date format of mm/dd/yyyy, while others use dd/mm/yyyy. The Power Query locale is based on the regional setting of your operating system. For more information see Change the Windows regional settings to modify the appearance of some data types and Change Language & Region General preferences on Mac.
Note There can be up to three locale settings: The operating system, Power Query, or a Change Type setting. If there is a conflict, the order of resolution is (1) the Change Type setting, (2) Power Query, and (3) the operating system.
You can customize the File Origin step of an imported CSV or text file by updating the formula.
-
To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.
-
Import a CSV or text file. For more information, see Import or export text (.txt or .csv) files.
-
In the Query Settings pane, under Applied Steps, select the Edit Settings
-
In the Comma-Separated Values dialog box, select a locale from the drop-down list.
-
Select OK.
When you create a new Excel workbook that contains queries, Power Query uses the current operating system locale as the default locale. This setting determines how Power Query interprets data imported from text, numeric, and date and time values. When you send an Excel workbook that contains Power Query queries to a different user, the Power Query locale setting is kept as the locale specified by the author (or last person who saved the document). This ensures consistent Power Query results regardless of your current operating system locale settings.
To modify the locale setting of the query in Excel:
-
Select Data > Get Data > Query Options.
-
In the Query Options dialog box, under CURRENT WORKBOOK, select Regional Settings.
-
Select a locale from the drop-down list.
In addition to the default Locale setting of a workbook, you can specify a non-default locale setting to use when you apply a specific Change Type operation over one or more columns. This is helpful when importing data with different regional settings across multiple queries, yet you want a specific default setting to be consistent for all queries in the workbook.
With the Change Type operation, you can specify the destination data type as well as the locale setting to use for the conversion.
-
To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.
-
Right click a column header, and then select Change Type > Using Locale.
-
In the Change Type with Locale dialog box, select a data type and locale.
-
Select OK.
