How to deal with errors in Power Query (2 ways)

In today's video, I am going to show you how to manage errors in power query two ways: the "official one" and a faster way (that could be a lot faster ;)
#curbal #powerquery #powerbi

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

************

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[charliemather]

Ruth, I updated my original function with your suggestion and then created another function that calls it to search for errors on all columns in a table. Both functions are required for it to work.


//fnShowError This function can be used to check for a single column in a table
//
let ErrorCheck= (TableName as table, ColumnName as text) =>
let
// Add Index Column to keep Row#
#"Added Index" = Table.AddIndexColumn(TableName, "Row#", 1, 1),
// Duplicated column to be tested
#"DuplColumn" = Table.DuplicateColumn(#"Added Index", ColumnName, "TestColumn"),
// Filtered table to error rows only
#"Kept Errors" = Table.SelectRowsWithErrors(DuplColumn, {"TestColumn"}),
// Removed temporary column
#"Removed Columns" = Table.RemoveColumns(#"Kept Errors", {"TestColumn"})
in
#"Removed Columns"
in
ErrorCheck


//fnCheckAllErrors
This function invokes function "fnShowError" for ecah column in a table
//
let ErrorCheckAll = (TableName as table) =>
let
// Get listing of all column names
AllColumnNames = Table.ColumnNames(TableName),
// Convert listing to a table
#"Converted to Table" = Table.FromList(AllColumnNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Change the column name to "Column Tested"
#"Renamed Columns" = to Table", {{"Column1", "Column Tested"}}),
// Invoke function "fnShowError" for each column
#"InvokedFunction" = Table.AddColumn(#"Renamed Columns", "fnShowError", each fnShowError(TableName, [Column Tested])),
// Next two steps captures the list of columns to be expanded
fnShowError1 = #"InvokedFunction"{0}[fnShowError],
#"ColumnNames" = Table.ColumnNames(fnShowError1),
// Expand the list of errors
#"ExpandedTable" = Table.ExpandTableColumn(#"InvokedFunction", "fnShowError", #"ColumnNames", #"ColumnNames")
in
#"ExpandedTable"
in
ErrorCheckAll

[charliemather]

Ruth, Thanks!! You inspired me! I cobbled together a function that can be called for a table/column pair to display the errors. It basically mimics what you demonstrated


//fnShowError
//
let ErrorCheck= (TableName as table, ColumnName as text) =>
let
// Duplicate column to be tested.
#"DuplColumn" = Table.DuplicateColumn(TableName, ColumnName, "TestColumn"),
// Add Index column for the Row#
#"Added Index" = Table.AddIndexColumn(DuplColumn, "Row#", 1, 1),
// Test the selected duplicated column
#"Added Custom" = Table.AddColumn(#"Added Index", "Test", each try [TestColumn]),
// Expand the results
#"Expanded Test" = Custom", "Test", {"HasError"}, {"HasError"}),
// Filter to Error rows only
#"Filtered Rows" = Table.SelectRows(#"Expanded Test", each ([HasError] = true)),
// Remove the temporary column
#"RemoveTempColumn" = Rows", {"TestColumn"})
in
#"RemoveTempColumn"
in ErrorCheck

[robinherrick]

In the example you gave you can replace errors with an unusual date, say 1/1/0001, then filter on that date value. It's not as straightforward as what you requested as a feature but is simpler than creating a new column perhaps. For text and numbers you could use and or something like that.

[dw]

4 Years later, you still make my day!

[rachidwatcher]

Thank you for the video🙂! when we deal with millions of rows and the errors are far a way from the first 1000 records it become very difficult to find the columns that contain errors. Could you help me to find out how to solve this type of problems?

[josepromero]

Before error occurs (checked by the quality bars at the top of column), insert a numerated column and then filter by the numbers that appear in the error table.
Then you can check errors before they appear and fix them if possible (i.e. different format dates DD-MM-YY and MM-DD-YY).
I hope this trick also helps...
Thanks Ruth!!
Josep.

[nickaubrey]

Thanks Ruth great solution. Null values are my friend in Power Query !

[stephenanointing]

Tbh you're teaching skill is so cool

[georgexenakis]

This method at 9:23 is a lot better than wrapping queries in try/otherwise statements which has it's own disadvantages. I am imagining if at the end of the power query steps if I convert all columns to text and concatenate them all adding try to the start, the Has error output is something I can countif for in normal excel which means users won't have to open queries and connections to know errors have occured!

[diogosilva]

Excelent video, i love your way of explaining stuff, learned a lot with you as i am starting to learn about Power Query. I came here with hope to find a solution to a problem i have with errors.
I used the try function in all the collumns i have and it doesn't show any error. Once i load the table to Excel it says i have 200+ errors. When i click to view the errors the table is empty. It seems all data is ok but the error message won't disappear. Is that normal?

[amani]

Thanks very much for this. I found the error in a different column than I originally thought!

[donovannewton]

This is interesting.

I have a query that searches through a list of tables and retrieves a specific table.

In that table every column has a value that needs to be replaced by a specific words.

My issue is that I have to create a specific find replace statement.

I’m getting an error because the last columns are looking for a column that doesn’t exist and returns an error

Is it possible to use an Iferror statement on each column to remove it?

[learnspreadsheets]

Hey! There is a show errors! You can also go to keep rows in home tab> keep errors instead of the “try” approach. In fact, I tend to 1. Add an index Column 2. Reference the query 3. Keep errors 4. Click the index column then Unpivot other columns, that way you get the column and row intersection 😉

[damarmuhammet]

You are doing very valueable job... thanks a lot... if we have a question, may i ask ? am juniar on power bi...

[IRDSB]

A slightly different errors scenario, but one that I'd love to see a video on: often when I come to manually refresh data, PowerBI tells me it can't find specific columns in a table when I know (and double-check!) that they are there. After hitting 'Refresh' a number of times, and getting erroneous messages about missing columns in various tables, the report will eventually refresh. Generally this is data coming from web-published Google Sheets data so I don't know if it's a problem that end, or if this is a PowerBI issue with a known solution. Thanks!

[avivzach]

what type of errors can it be other than data type? and if there are other error types, can I filter only errors that are data type related? lets say I have a numeric column and I want to filter out all the rows that accidently have text values in them

[wiekevisser]

Yes! Thank you very much, fixed all the errors (which I have been ignoring until now). Perfect, thank you!

[barttitulaerexcelbart]

Thank you Ruth, please continue with video's like this, they are very helpful. Maybe next time also explain what to do if the error is in a row > 1.000 (say above the preview selection)?

[sqfiitteam]

Much appreciated Ruth - I simply used your logic to identify errors and corrected it by selecting a better data type.

[ahmedal-dossary]

That’s a cool tip.

I’m having 3 error group query that showed up as a result of having a mlixutre of data type in a single column. I was surprised to have them to be honest. When refreshing the data, I’m getting an error that reads ‘error in loading previous table’ and this is also affecting the load of other tables. Can data type mismatch result in such loading errors?