Original subtitles

Instructor: Data profiling tools provide

a visual way for you to explore your data

and to get a sense of its composition,

and each of the data profiling tools that we'll cover

have some slightly different options, layout and purpose.

Digging into column quality first,

this is all about showing the percentage

of values within a column

and these percentages are broken down

into three types, valid, error or empty,

and if you're ever trying to quickly identify the percentage

of empty or error values within a column

this is a great tool to use.

Additionally, you can hover over the column quality box

to reveal a contextual menu

with some details and additional options.

On the lower right, you'll see a little ellipses icon

that when clicked will reveal a bigger contextual menu

with some other options to do things

like cleanup, duplicates, remove empty values

and remove or replace errors.

So one thing to keep in mind here is that the great thing

about these types of contextual menus

is that they allow you to solve column quality issues

without having to try

and find the appropriate steps within the query editor.

These menus really bring this functionality straight

to you without you having to go and find it.

Another profiling tool is column distribution

and this provides a sample distribution

of the data contained within a column

and very similar to column quality tools,

we have the same types

of contextual menu options that help us clean up

and sort out error or duplicate values

that may be contained within the column.

The other thing that I'd like to call out here

is that you actually have a suggestion from the query editor

and this suggestion is a potential remedy based

on the distribution that it finds within the column.

So here we can see

that there's a removed duplicate suggestion

to help clean up this data.

This may not always be exactly what you want,

but it can be kind of handy if the recommended option

is the exact data cleaning step that you're looking for.

Our last data profiling tool is called column profile,

and this provides a more holistic view of the data

within a column by providing a sample distribution

of the data along with specific column statistics.

So on the bottom left of this view,

we have the column statistics view,

which provides a bit more detail

than just the column distribution view we just reviewed.

This actually now gives us some detail about counts,

the number of distinct values, the number of uniques,

some min and max and empty values and so forth and so on.

On the right hand side, we have the value distribution

which shows the distribution

of each value within the column,

and similar to the other profiling tools

you can also hover to reveal a contextual menu

with suggested transformation options.

All right, so with all of this in mind,

let's go head over to the query editor

and we're gonna check out

these column profiling tools for ourself.

All right, so for this demo

we're gonna connect to the customer lookup table,

and this is the table that we're gonna use

to demo some of these profiling and QA tools.

All right, perfect.

So again, kind of following some of our best practices

and initial first steps, let's update our table name here

to just customer lookup, and then we'll check to make sure

that all of the data types and column headers look good.

All right, so kind of scrolling along here.

Text values, our birthdate as a date, marital status

gender, text, email, text, annual income here.

This is a whole number.

We could update that to currency if we want,

but that looks good.

Okay, everything looks really good here.

So all of these column profiling tools

that we talked about are located up here in the view menu

and let's enable our column quality,

and we can see that our quality, you know

it actually looks pretty decent, right?

We get this little results window for valid error and empty.

Like I said, when we hover, we get these contextual menus.

You can right click and also get a contextual menu

with some of these kind of

common transformation steps, right?

So if we scroll along here, right?

We've got prefix column here

with less than 1% of empty values here,

and again like this is stuff

that kind of makes sense, right?

We may not have a Mr or Mrs. prefix

for all of the customers within our data set.

So seeing that it's only nine here,

I'm not super concerned about this,

and it's probably not some sort of issue

that is gonna compromise the integrity of our data, right?

If we keep scrolling along here, again, everything is valid

at a 100%, no errors, no empties

everything looks pretty good here,

but this is actually a bit misleading,

because the default column profiling only analyzes

the first 1000 rows of the table,

and our customer lookup table contains over 18,000 records.

So in order to view all of the records,

we need to update the column profiling

from the first 1000 records

to be based on the entire data set

and then once we update that range

we'll see how this really looks.

So to do that, we see down at the bottom here

where it says column profiling is based

on the top 1000 rows,

and we actually want to have it

based on the entire data set.

So once we do that, power BI goes through and re-scans this

and once this updates, look what happens, right?

Our customer key column now has a bunch

of errors in it, right?

We're seeing that there's five errors here.

Some of our other columns like prefix and first name,

like we're actually seeing some empty values

in here as well.

So something is going on with this data set

that we actually need to clean up,

and like we had talked about

we can actually use Power Query's suggestions here

to remove these errors, right?

So if I click remove errors here,

a new applied step is going to be added for that

and then we'll see the updated results based on that change.

The only other piece that we have here now

is that we're actually getting some empty values

within this column, right?

So again, we can follow Power Query suggested update here,

click remove empty, right?

We've added another applied step here for filtering rows.

We're filtering out those empty rows

and now we're kind of back to where we were at, right?

We've got 130 empty values here for prefix,

but again, that may make sense based on the data set

and if I scroll over, right?

All of these other columns are now a 100% valid, right?

So it looks like that took care of it,

but what I would still like to understand is

what exactly was causing those errors, right?

Like what was the actual values or the reason behind it?

So let's delete these last two applied steps, right?

And now we're back to the state

where we have all of these errors.

If we come back to our header here

and we right click, if you select keep errors,

what this is going to do is add a new applied step

that shows a results table

with all of those errors in it,

and you can actually click into each of these errors

to see the Power Query error message.

So this is a data format error

that we couldn't convert to a number, right?

And here's the details.

This 30 with three dashes is what Power Query

was trying to convert to a number, right?

And if I clear out of this last applied step,

you know, I can click into another error here,

and again it's that same error.

We couldn't convert this to a number

and here's the number that was trying to be converted.

All right, we'll check one more here.

All right, so this is same error,

but a slightly different issue, right?

It looks like there's some source data here

or a link or something like that,

that couldn't be converted to a whole number.

So again, like that is the underlying reason why

those errors were happening.

Those are the actual issues within that data set.

We also see that there's some other values within prefix

that really don't make sense, right?

These are not picked up as inaccurate,

because of the column data type being text,

but lowercase M doesn't really mean anything, right?

If we scroll over here,

looks like we've got some birth dates from,

you know January 1st, 1900.

So again, these different rows here don't make sense

to keep as part of this data set.

So if we clear our keep errors step,

the great thing about this

is that we can just rerun through

those same cleaning steps, right?

We can either remove errors by clicking this,

we can right click and then select remove errors, right?

We can right click again, remove empty

and after that we're back to this nice clean data set,

where our only empty values are

within this customer prefix column.

All right, so now that we've got these errors cleaned up

let's quickly check out some

of the other profiling tool options,

and I'm gonna deselect column quality.

We'll check out column distribution,

and again, when we select our column distribution here

we see a sample distribution

of the values within each of the columns,

and if we go in, let's check out first name here,

we can see that we have 666 distinct values

and 89 unique values.

So this means that there are 666 distinct first names

within the column, and 89 of them appear only once, right?

So that's how to interpret those numbers,

and if we hover over the column header here,

again Power Query has this removed duplicate suggestion,

and again, we're a little bit smarter

than this suggestion and understand

that duplicates are actually necessary within the column,

so we'll ignore that

and then last, we'll check out our column profiling tools

and here we get a really nice view of our column statistics

and a chart showing the distribution.

Again, one of the important pieces to note here

is that some of these column statistics

really may not make sense.

Count makes sense.

We're gonna wanna see the errors

or empty values, distinct, unique, these may make sense,

but the min and max here,

these don't really tell you much beyond

the first and last names in an alphabetical order,

but if we scroll over to a column like our annual income

and select this one, now some of these column statistics

are gonna provide a little bit more value here,

especially when we get down to the minimum, maximum values

our average and standard deviation, right?

So depending on the data contained

within each of these columns, some of the statistics tools

and the distribution may make more sense.

The larger point here is that you have a bunch of tools

at your disposal that are really helpful to use

when you're exploring

and trying to understand

the data contained within your tables.

Data prep and QA is often a very time consuming

and intensive process as an analyst,

and these tools can help make that process

a bit quicker and scalable.