Original subtitles

Instructor: I want to talk about a common error

that you may experience when importing tables

that contain a date or value from countries

that use different standards.

So, for example, I'm in the United States

and the default date standard here is month, day, year,

but in the UK for example, the standard is day, month, year.

So, what would happen if I imported a table

into my Power BI data model that contained UK dates?

Well, I'll show you exactly what happens in a moment,

but the short answer is that I'd get an error

starting on the 13th of the month.

The good news is that there's an easy process

that we can use to update the locale

used to recognize the date format, and remedy the error.

All right, so our first step in the process here,

we need to make sure that all of the data types are the same

for the values within the column.

And then, we're gonna left click the data type icon

in the header and select using locale.

After that, and this is the most important piece

to get right, you need to select date as the data type

and then select the location that the file originates from.

So, in this course, all of the data types are based

in English, United States.

So, if you're running into this issue,

select that option regardless of your actual location.

If you select your own location

the records won't update accurately.

And then third, you really just want to confirm

that the updates have been applied

and that the data type is correctly recognized

and without any error or issues.

All right, so I'm gonna jump into Power BI

and show you what this looks like.

There isn't a data set to follow along with for this demo,

so just sit back, watch and enjoy.

All right, so we're gonna connect to an Excel file here

that's located on my desktop.

I go back to desktop and we have this date error demo.

And we're gonna load this into the query editor

to take a look at there

and make some of these transformation steps

that we talked about.

And with Excel, because there's multiple sheets

that are possible within an Excel document,

you're first wanna select the sheet

that you want to connect to.

And we have our same kind of data preview window here.

And immediately off the bat we kind of see

that something's going on here, right?

When we get from the 12th or the 13th,

there's some sort of issue.

So, let's click transform data, and we're gonna work

through the process that we outlined in the slides.

First things first here, I wanna kind of demonstrate the way

to not update this so you can kind of see what happens here.

Obviously, we've got something going on.

We're not seeing errors here on the 13th of the month,

but we do have our values split out to the left

and to the right side.

From the data type here, we can see that it's mixed

between text and integer values or whole numbers.

So, what if we clicked here and just clicked update date?

All right, now we get all of these errors here, right?

We can click into this error.

Power Query is saying we couldn't parse the input provided

as a date value, saying 13/1/2023, right?

It doesn't recognize that as a date value.

So, if we close out here

and then we'll also remove that change type step.

What we want to do first is

because this is actually a mixed data type in the column

we have both text and whole numbers,

we actually want to update this all to text.

We wanna make sure that we're working

with the exact same data type here.

Then we can come back in, we'll update using locale.

And for the purpose of this demo,

this file has come from a friend in the UK, right?

So, what we want to do is we want to update

this data type to date and the locale that we want

to base the transformation off of

is English, United Kingdom, right?

And we can see a sample of the input values, right?

So, we have the day, the month, and then the year,

which aligns with the way that this file is set up.

So, we'll click okay, and now look what happens.

Because I'm based in the US

and I want my date set up as month, day, year,

I now have that appropriately configured

for all of the values within the column.

We've got January 19th, 2023, January 20th, 2023,

so on and so forth.

And from here you can continue

and just use the add column tools to build out

additional columns based on this date field.

So, if you ever run into a situation where your date decimal

or currency columns are returning errors,

I'd suggest using this process to update

the data types based on the locale of the source.