Understanding Dates and Locale

Quick reference

• 00:04 Let's do a quick review on how dates work in programs like Excel and Power BI.
• 00:11 Now, if you've ever taken a date in Excel, and accidentally hit the comma style or
• 00:16 a numeric style, and you end up with a number like 43,000 and some odd, and
• 00:21 you wonder, what the heck is that all about?
• 00:23 It's because dates are actually based off of what we call a serial number.
• 00:27 And that serial number is the number of days since January 1st, 1900.
• 00:32 So, when you hit that date with the comma style, and you get this 43,000, and
• 00:37 some odd answer, its because its 43,000 and some odd days since January 1st, 1900.
• 00:42 Now the reason why this is so important is that it actually allows us to take a date
• 00:47 and add or subtract values to get other dates and
• 00:50 that's the big benefit of having a system like this.
• 00:54 It gives a common number that we can say,
• 00:56 hey everything started off as of this date.
• 00:58 And now when we have these serial number underlying in our cell and
• 01:01 our data we can get a formatted correctly for world wide exchange of data.
• 01:06 And this is huge because that's the way that you can
• 01:09 actually start sharing things like work books or somebody and
• 01:12 when you open it in the US it shows that the month-day-year format, and
• 01:15 when you open it in say, Canada, it shows in the day-month-year format.
• 01:20 So it actually allows things to work really well and be seamless because
• 01:24 everything's really pinned off of that date serial number underneath.
• 01:28 The challenge though comes when you actually start trying to import data from
• 01:32 other file types.
• 01:33 And the reason being is because that if there is a date in there,
• 01:37 it needs to be interpreted in order to get the correct date serial number.
• 01:42 So, what's the big issue anyway?
• 01:44 Well, it comes down to when you reach into a text file and you go and
• 01:48 you grab a date that looks like this, 4/5/12.
• 01:51 So what date is it?
• 01:53 Well, the answer totally depends on which date format was in use at the source.
• 01:59 Some interpretations of how this date might be interpreted?
• 02:03 If it were a month-date-year format it would have a date serial of 41,004.
• 02:08 If it were a day, month, year, it is a different date serial number.
• 02:12 And if it were year-month-day, it comes out to something completely different.
• 02:16 And this is why it's super, super important to get this right, because this
• 02:20 could actually shift it off by years or days, depending on what we interpret.
• 02:26 Now, Power Query actually makes a guess when you try to interpret the data but
• 02:31 it can be wrong so we need to sometimes take control of that.
• 02:35 This is where Power Query's locale settings come in.
• 02:39 But what does that even mean?
• 02:41 Well, locale is actually an IT-based term
• 02:45 to tell us where the data's home region is.
• 02:48 So where is the data actually coming from?
• 02:51 This allows us to say, hey, I've got a text file over here and
• 02:55 it's got dates represented in a specific way,
• 02:58 those dates came out of a system that was an English US format.
• 03:03 So if I actually go and use Power Query now, I can force the data to interpret in
• 03:08 a specific way and correctly interpret the data from a different region.
• 03:14 I should also point out this is not restricted to just working with dates.
• 03:18 In Europe, they often use commas for decimal separators where in North America,
• 03:23 we use commas for separating thousands and dots for separating decimals.
• 03:27 So obviously, that's gonna cause some problems,
• 03:29 because it's not gonna get interpreted correctly.
• 03:32 And this is why locale is so important to you.
• 03:36 By default, data is imported using your locale.
• 03:40 So if you have a file that you've set up and everything works fine,
• 03:44 uz you've set up your dates, you've set up your currencies no problem.
• 03:47 And then you email that file to somebody in another country of the source file and
• 03:51 the ultimate solution, but their locale settings are different.
• 03:56 You've build a file in a month-day-year format for
• 03:58 date, you send off to a country that's using day-month-year.
• 04:02 They try and refresh the solution, it gets interpreted with their locale.
• 04:06 So every date is attempted to be interpreted with the day-month-year
• 04:11 format, and as soon as it gets to what was January 13th on years,
• 04:17 Power Query's now looking and going, well, you can't have 13 months, and
• 04:20 it fails, and that's a challenge.
• 04:23 For this reason,
• 04:24 we actually have the ability inside Power Query to specifically choose when we're
• 04:29 changing data types on columns to set the data types using a locale setting.
• 04:34 We would actually go and say change type using locale, and then we get to pick what
• 04:38 kind of data is, is it a date, is it currency, is it a value, what is it?
• 04:43 And at that point,
• 04:44 we can choose the region that the data source is coming from.
• 04:47 And this is what it's always about, is where is the data coming from?
• 04:51 If I'm in Canada and my data source was kicked out in an English US format,
• 04:55 I'm gonna choose to use the English US where the data's coming from format for
• 05:00 my imports.
• 05:03 Doing this, even if you don't think you need to send it to somebody else,
• 05:07 it's always a good idea.
• 05:09 It makes your files more portable because you don't have to worry about somebody
• 05:12 picking it up later who has different regional settings than you do.
• 05:16 It makes your solution last longer because you never know.
• 05:19 You might get a new computer and
• 05:21 maybe you set your Windows regional settings a little bit differently because
• 05:24 that's the locale that it's gonna use to interpret your data.
• 05:27 It also helps prevent errors when importing
• 05:31 because sometimes data changes a little bit.
• 05:33 And you may not even have any original preview, all of the data and
• 05:37 you'll get some new which actually kick off the errors.
• 05:41 So this helps prevent errors when porting.
• 05:43 It can also someone trigger errors, which can be useful, but
• 05:47 at the end of the day, it's a really good practice to make sure that if you know
• 05:51 that somebody else might use your file, you actually set columns that
• 05:55 could be problematic to use locale when you're porting.

Lesson notes are only available for subscribers.