Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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.

  1. 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.

  2. Import a CSV or text file. For more information, see Import or export text (.txt or .csv) files.

  3. In the Query Settings pane, under Applied Steps, select the Edit Settings   icon next to the Source step.

  4. In the Comma-Separated Values dialog box, select a locale from the drop-down list.

  5. 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:

  1. Select Data > Get DataQuery Options.

  2. In the Query Options dialog box, under CURRENT WORKBOOK, select Regional Settings.

  3. 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.

  1. 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.

  2. Right click a column header, and then select Change Type > Using Locale.

  3. In the Change Type with Locale dialog box, select a data type and locale.

  4. Select OK.

See Also

Power Query for Excel Help

Add or change data types

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×