• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Change Type Using Locale with Power Query

You are here: Home / Power Query / Change Type Using Locale with Power Query
Power Query change type using locale
March 25, 2021 by Mynda Treacy

One of the most common issues I help people fix is data imported to Excel or Power BI using Power Query where the regional settings of the PC differ from that of the source data. Usually, the problem arises with dates. For example, data formatted dd/mm/yyyy imported on a PC with a mm/dd/yyyy date format. It can also be an issue with numeric data containing thousand separators or decimals where your region uses a comma in place of a period, or vice versa. Thankfully, it’s easily fixed using Change Type Using Locale with Power Query, but diagnosing the problem isn’t obvious.

Watch the Video

Subscribe YouTube

Change Type Using Locale with Power Query Steps

The problem often presents itself when the user is expecting data for a 12 month period, but they only have data for January. For example, below is the CSV file opened in Excel and you can see in the filter drop down there is data for every month:

excel dates

Note: this dataset only contains data for the first date in each month, as you can see with January to March expanded to the day level of detail in the filter drop down above.

This data is stored in the CSV file in a dd/mm/yyyy date format.

excel dates

When the data is imported to Excel, Power Pivot or Power BI on a PC with date format mm/dd/yyyy you get the following result where it appears there is only data for the first 12 days of January:

power bi dates

This is because Power Query assumes the date format in the CSV file is the same as your PC settings, in this case mm/dd/yyyy, and it imports the data accordingly.

If your files have data on days 13 to 31st of the month you may also notice that the query returns a lot of errors and only imports some of the data. This is because the data for dates 13 through to 31 of each month are discarded.

For example, consider the date 13 January 2020 formatted with dd/mm/yyyy looks like this 13/01/2020 in the CSV file. And when Power Query on a PC with date format mm/dd/yyyy imports it, it reads it as the 1st of the 13th month, 2020. Of course, there’s no 13th month, so this data ends up with errors which don’t get imported to your model.

So, now you know what to look out for, let’s look at how to fix it.

Fixing Power Query Locale Issues

Step 1: Go to the Power Query editor and remove the very first Changed Type step. It’s usually right after the Source step:

power query changed type

This might break the query, but don’t worry, it’s temporary.

Step 2: Go to the date columns (use CTRL to select multiple) and right-click > Change Type > Using Locale…

power query change type using locale

Step 3: in the Change Type with Locale dialog box choose Data Type ‘Date’ and in the Locale choose the locale of the dataset. For my CSV exmaple it’s English (Australia) which has a date format of dd/mm/yyyy:

power query change type using locale dialog box

You should now see the correct date format for your region.

Repeat for the other columns where required.

Step 4: Set the data types for the remaining columns via the icon in the top left of the column header:

power query change type

Note: if your data also contains numeric values with thousand separators or decimals, you’ll also need to use the Change Type Using Locale… technique for those columns.

That’s it. Your data should be ready to close and load with no more errors.

Power Query change type using locale

More Power Query Posts

power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
Category: Power Query
Previous Post:get data from sharepoint or OneDriveGet Data from OneDrive or SharePoint with Power Query
Next Post:Static Tables in Power Query, Power Pivot and Power BIstatic data tables

Reader Interactions

Comments

  1. Kristy Thai

    April 2, 2022 at 3:36 am

    Hi – I keep getting an error on my () negative amount in the Power Query. I tried the Locale method, but I imported the data set from a PDF file, so I cannot view the format of the amount. Please help.

    Reply
    • Mynda Treacy

      April 3, 2022 at 11:24 am

      Hi Kristy,

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  2. Jessica

    December 3, 2021 at 6:28 am

    I’m trying to change the date format, without using the ‘locale’ feature. Is their a way to do that within the M code?

    Right now my date is displaying 9/1/2021 – but I want the month and day to always be two digits (as 09/01/2021) and the year to always be 4 digits. It should display MM/dd/yyyy – can’t I do this with a formula as a new custom column? I need to do this in Power Query Editor (not Power Query BI).

    It would be swell to do this as one formula, not have to split the date into three columns to force a leading 0.

    Reply
    • Mynda Treacy

      December 3, 2021 at 9:04 am

      Hi Jessica,

      Power Query isn’t the place to worry about how the dates are displayed. This formatting you describe is done in Excel once the data is loaded. If you force a date to appear as mm/dd/yyyy in Power Query you would have to convert the dates to text, and you should never do that to a date because then it becomes unusable in calculations and is not able to be grouped in filters or PivotTables. My advice is to simply make sure the dates are correctly formatted for your locale, and then use the Excel cell formatting to get the desired look of mm/dd/yyyy once you load the data.

      Mynda

      Reply
  3. Will Ryu

    August 6, 2021 at 10:35 am

    Thank you and Phil for the no-nonsense and to-the-point no-fluff videos delivered in that calm manner. The videos helped me take the first steps towards the world of Power Q magic and continue to inspire me to explore further.

    Is there a way to display large numbers in a non-scientific format – 1234567891011 displays as 1E12. I know it can be formatted as text to show all the digits. Similarly with the commas – 1,234,567,891,011 or even the #,,, format? And retain that format.

    Thanks

    Reply
    • Mynda Treacy

      August 6, 2021 at 11:19 am

      Hi Will,

      Use a custom number format with 13 zeros.

      Mynda

      Reply
      • Will

        August 7, 2021 at 11:18 am

        Thanks Mynda

        However, in my version of PowerQuery Editor, I am unable to choose a custom number format. I don’t and can’t have PowerBI loaded.

        Reply
        • Mynda Treacy

          August 7, 2021 at 11:26 am

          You don’t apply the custom number format in Power Query. You do it in the Excel Worksheet. Power Query is not for formatting. That’s something that is done in the worksheet cells.

          Reply
          • Will

            August 7, 2021 at 6:33 pm

            Thanks for the clarification. Yes, I do know how to do that in Excel.

            It’s just that the larger numbers cant be read in PowerQuery as they show up as 1E12 etc. which is why I formatting them as text, which allows me to see the entire number.

            After loading to Excel, I convert them back to number

  4. Jean-Marie Lambert

    March 26, 2021 at 12:59 am

    Hello,

    Thanks for your mail.
    Why do not use in Power Query Editor : Query Options and modify Regional Settins ?.

    Best regards,

    Jean-Marie
    https://perso.unamur.be/~jmlamber/

    Reply
    • Mynda Treacy

      March 26, 2021 at 12:23 pm

      Great question, Jean-Marie. You can do that if you’re confident that all data you import is from the locale setting you choose, but often people are importing data from a foreign locale as well as their own, so this approach would also end up with errors in your data.

      Reply
  5. David James Bonin

    March 26, 2021 at 12:20 am

    Mynda,

    On the related topic of dates with VBA, I discovered an odd quirk with Excel and Windows 10.

    I set my PC to use a date format of yyyy-mm-dd. Why? Because it visually collates better in Windows Explorer. My PC displays today’s date as 2021-03-25.

    When importing / exporting data from / to our mainframe SAP database using Visual Basic, I needed to convert dates from Excel’s numeric date values to string values in the format of mm/dd/yyyy.

    I figured I could use something like this in VBA to create a string value of today’s date:
          Format( Date, “mm/dd/yyyy” )

    That didn’t work. VBA creates a string value in the format of mm-dd-yyyy. Windows and Excel ignored my explicit instructions about the delimiters.

    I found I needed to use something like this instead:
          Format( Date, “mm” ) & “/” & Format( Date, “dd” ) & “/” & Format( Date, “yyyy” )

    That worked.

    Reply
    • Mynda Treacy

      March 26, 2021 at 11:45 am

      Interesting, David. I’d have just used a custom number format on the cell so that the dates were still proper date serial numbers and could be used in formulas.

      Reply
  6. Claus Scheyda

    March 25, 2021 at 11:09 pm

    Thanks for the information on using “change type with locale”. Perhaps it would be helpful to mention that the locale can be changed (at least in Excel Power Query) for all workbook queries at once. This is helpful when importing lots of data with a different (but identical) locale.
    On Data ribbon select Get Data > Query Options (exact position varies by Excel version); then Current Workbook > Regional Settings > Locale. This applies to all existing and future queries in the specific workbook.

    Reply
    • Mynda Treacy

      March 26, 2021 at 12:25 pm

      Yes, you can do that if you’re certain all data being imported is from the locale you change it to. Be careful though, especially if you’re sharing that file with others who might not be aware that you’ve applied that setting.

      Reply
  7. jomili

    March 25, 2021 at 11:00 pm

    I have a similar but different problem. My dates should be m/d/yyyy. They came in as 1/2/2021 (internal value 44,198.07 ), formatted as 2/1/2021 (in column C). I need them to actually BE 2/1/21 (internal value 44,228.07). I can’t get PQ to do that for me. Only solution I’ve found is the formula =–IF(N(C13),TEXT(C13,”d/m/yy h:m”),C13). Is there a PQ method that would do this? I can send a sample file if it helps.

    Reply
    • Mynda Treacy

      March 26, 2021 at 12:26 pm

      Yes, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  8. jomili

    March 25, 2021 at 10:31 pm

    Mynda,
    It would be nice if you included a sample data file so users like me could work the steps.

    Reply
    • Mynda Treacy

      March 26, 2021 at 11:28 am

      It’s a bit tricky to provide a source file in a different locale to my own as my system creates the files for my locale, which is dd/mm/yyyy. If you’re using a mm/dd/yyyy format then any file you download from our site will contain data in a dd/mm/yyyy format that you can test. For example, the Power BI sample files here: https://www.myonlinetraininghub.com/workbook-downloads

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x