this video is for those times where you
don't have perfect data quality in your
data set so particularly with Excel
sources what you find is people can type
whatever they want in an Excel sheet
right and if they put text in a date
field that will break your data refresh
so the question becomes how can we
troubleshoot and prevent this sort of
thing obviously if you can get data
validation in your data source it's
always better to prevent these in the
first place versus trying to fix it
after the fact and Excel does have some
data validation options but there's
times where it won't work for your use
case so this video is going to be about
how to diagnose your power query error
issues and potentially how to alert on
it without breaking your data refresh so
first things first this is a query that
is linked to an Excel sheet there are
errors in this data so the first most
obvious thing you can do is to just
click onto the cell to see what the
error is the number one mistake people
make is to click on the text of the
error message don't do that just click
on the white space next to the error and
it'll tell you exactly what the error
message is and the value that has issues
with if you have a larger data source or
if you have lots of columns it can be
hard to track down exactly where the
issue is you'll be scrolling through and
trying to find it and it's not the most
userfriendly thing in the world so I'm
going to show you how to make a query of
your errors that shows you everything
you need to know in one place in a nice
scrollable list the end result looks
like this where you have a column for
your column names the type of error what
the error message is and the value has
issues with it'll pull all your error
messages into a table basically let's go
through how to do this I'm just going to
delete this query all right so start
with your Source table we're going to
duplicate The Source table don't
reference it duplicate it I'm just going
to rename this to errors in this query
select all your columns so if you select
the first column and do contr a on your
keyboard it'll get them all or you can
just hold shift to select them and then
we're going to right click on a column
and then click on unpivot columns so
this is something that if you have a
data set with like a billion rows this
probably isn't going to handle it very
well but it works really well for
smaller Excel sources then all we need
to do is go to this value column here
and go to keep rows and say keep errors
because that's the part we care about
are the errors in our data set so now we
have a table with our errors we can
expand this error information using a
custom column to do that we go to add
column in the power query menu up here
and then choose custom column I'm just
going to call this column error and for
the formula it's going to be really
simple we're just going to do each and
then the space and then try and
parentheses and then double click on the
value column here and then close the
parentheses click okay that gave us a
new column that we can expand so if I
click on this double arrow icon here and
then click okay and then expand it again
so we need to do this a second time so
Double Arrow icon again okay that gives
us all of our error information so we
can tell what the error message is what
the detail is so on so forth now all we
need to do is remove this column so this
column because it still has errors in it
will cause our data refresh to break if
it's still there when the refresh runs
so I'm just going to remove this one
because we have everything we need from
it so this can be useful for narrowing
things down temporarily but you can also
use this as a table in your data set so
you can drop a count of the rows in this
table in to a card Visual and then set
an alert on that in the powerbi
dashboard so setting alerts is one of
the very few legitimate uses for powerbi
dashboards right now so you can set an
email notification for when the number
of rows in this table is greater than
zero send me an email we'll go through
that real quick at the end of this video
but I want to jump back to our original
query so here now that we know what our
errors are it's always best to fix the
error in the data source itself it's
very common to have intermittent errors
like this and there are times where you
don't want it to break your refresh
right you want to be able to go back and
fix those things but you want the
refresh to continue running while you do
that so the fix for that is to handle
the errors in your query there's a few
ways to do this you want to think about
which way will work best for your
particular use case so your options are
remove rows or replace values so if you
want the rows that have errors to be
removed from your data set you just pick
the column that you want to do that for
and rightclick remove errors so that's
going to remove any row that has an
error in that column your other option
is to replace the values so something we
could do with this amount column for
example is to right click on it and go
to replace errors and replace the errors
with null and click okay so that's going
to take whatever text people put into
this number field and just make it blank
instead and if you do that for whatever
columns you have that are particularly
problematic it'll keep your refresh
running because it's not hitting any
errors when it runs another Super common
problem that people will run into with
Excel sources are column renames causing
problems or sheet renames or file
renames obviously it's better to avoid
those if you can but it happens so for
example I have a column here that I've
renamed if I refresh this query my query
is now broken so if I walk myself up my
steps in my applied steps on the right
here there will be a point at which the
query does work so you basically just
need to find the step that's failing
usually that's this first change type
step because that's something that power
query is automatically adding for you
based on how your data is structured so
for me when I renamed a column this part
right here can no longer find that
column because it's looking for it by
name if I update the name here so just
by taking out this two and then hit the
check box now it functions other things
you probably want to avoid are the steps
where you reorder columns in power query
so that usually like Yanks all of your
column names into the step and it's not
really necessary so try and avoid using
those if you're worried about your data
set refreshing but generally speaking
less is more in power query so both for
Speed and for reliability only do steps
on fields that you actually need to do
just makes your life easier I'm going to
go ahead and replace my errors on this
one too with null and then I'm going to
load this and show you how to do the
alert I just noticed I spelled Excel
wrong in with two L's so what we want to
do to reference our errors in an alert
while still refreshing our data set is
we drop a number for a row count of
Errors into a card visual so the
dashboards and powerbi will let you set
alerts on things they only work with
particular visuals and since we only
need to count we're just going to use
the card so I'm just going to make a new
measure on here and we're going to call
this count errors and we're going to set
equal to count rows of our errors table
so we can drop this into our Visual and
then we can publish and schedule Refresh
on this so this data source is an Excel
file in SharePoint I have another video
on how to schedule Refresh on Excel
files in SharePoint and another two
videos on how to change your Source from
local desktop to SharePoint so that you
can schedule a Refresh on it so we're
not going to cover that here if that's
something you need check out those
videos I'm going to put a link in the
video description I'm just going to
publish this and then we're going to go
set our alert real quick so here's our
incredibly intricate and exciting report
page I want to point out here that if
you don't want your viewers to see this
page you can hide it you can also just
create a new report referencing this
data model and not share it with your
viewers this can be something that's
just for you as far as notifications go
if I hover on this you'll see that
there's a bell icon here this is a new
feature that is data activator this one
is the thing that requires premium there
is an option that does not require
premium we'll get to that in a second
but if you do have premium you can set
an alert right here for your count of
Errors being greater than zero you can
have that send you an email or a teams
message and choose your recipients
you'll notice there's a alert here that
says that your workspace will be
upgraded to use the fabric free trial so
that's the premium there if you do not
have premium you can still use alerts
all you have to do is add this tile to a
dashboard first you want to use this pin
icon next to your card so if you pin
this this to a new
dashboard and then go to it you have the
option here in this context menu to
manage alerts and add an alert rule just
set the condition to be above zero and
then you can choose the frequency
there's a note here that alerts are only
sent if your data changes and you have
the option to decide whether or not you
want it to send you an email so just
click on Save and close and then you can
trigger this by refreshing your data set
and again I have another video on how to
a schedule Refresh on file sources so
check that out if you're not sure how to
schedule Refresh on your file Source the
email looks like this right here so this
is very useful probably one of the few
legitimate uses for powerbi dashboards
right now sadly so that's everything I
have for you today thank you for
