Skip to main content
Microsoft
Support
Support
Sign in
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.
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 More...Less
Different locales (also called regions) have different ways to display data.For example, some regions use a default dateformat of mm/dd/yyyy, while others use dd/mm/yyyy. The Power Query locale is basedon the regional setting of your operating system. For more information see Change the Windows regional settings to modify the appearance of some data typesand 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) theChange 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.
-
SelectOK.
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:
-
SelectData> Get Data >Query Options.
-
In the Query Options dialog box, underCURRENT WORKBOOK, selectRegional Settings.
-
Select a locale from the drop-down list.
In addition to the default Locale setting ofa workbook, you can specify a non-default locale setting to use when you applya specific Change Type operationover 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.
-
SelectOK.
See Also
Power Query for Excel Help
Add or change data types
Need more help?
Want more options?
Discover Community
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Microsoft 365 subscription benefits
Microsoft 365 training
Microsoft security
Accessibility center
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
Thank you for your feedback!
×
