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