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
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE

[@charliemather3368]

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" = Table.RenameColumns(#"Converted 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

[@CurbalEN]

Fantastic! Let me pin your post as you cover the other suggestion too! Thanks a million for sharing :) /Ruth

[@ruslanfokin]

Hey Charlie, tried to use your functions. As I understand ErrorCheckAll requires to create ErrorCheck first because it invokes fnShowError. When creating ErrorCheckAll I have the following message: The name 'fnShowError' wasn't recognized. Make sure it's spelled correctly. How to fix it?

[@stephenrwafa2519]

Powerful lifebouy thanks a million

[@charliemather3368]

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" = Table.ExpandRecordColumn(#"Added Custom", "Test", {"HasError"}, {"HasError"}), // Filter to Error rows only #"Filtered Rows" = Table.SelectRows(#"Expanded Test", each ([HasError] = true)), // Remove the temporary column #"RemoveTempColumn" = Table.RemoveColumns(#"Filtered Rows",{"TestColumn"}) in #"RemoveTempColumn" in ErrorCheck

[@CurbalEN]

You are amazing!!! Did you see the pinned comment? There is a button in the UX to keep errors, you can use that to fine-tune your function. Awesome work!! 👏👏👏 /Ruth

[@robinherrick2177]

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 xxxxx and 99999 or something like that.

[@amani1997]

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

[@mariollevillanueva2896]

Hello :) I just want to ask how the columns I added through dax would appear to my table when editing in power query? I tried to refresh the data but it won't show up. Thank you! 🙂

[@sqfiitteam4679]

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

[@CurbalEN]

It is a lifesaver trick :) /Ruth

[@nickaubrey7150]

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

[@dw1]

4 Years later, you still make my day!

[@CurbalEN]

🥳🥳

[@wiekevisser6995]

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

[@CurbalEN]

Wonderful 👏!

[@steevdavis8429]

Hi Ruth, what's the reason for the following error: "The DataSet 'DS0' contains a filter with duplicate columns and distinct values for the duplicate columns, which is not allowed."

[@Unethical1991]

Hi, what about situation when I have column with strings: "NO", "YES" answers? I want to divide YES answers with total numbers of tickets(the same column) for each month. I can't just change data type and fix errors.

[@ayobamiologun9184]

Thanks for sharing this Ruth. Good job always.

[@farmanalishah8836]

Thanks Ruth.Same happened to me yesterday and i was literally struggling this error. Now i can handle these errors as you suggested. Thanks again

[@CurbalEN]

Please note that there is a keep errors button. That will save you all my steps! /Ruth

[@samuelmanseau467]

Very usefull ! Thanks for the Tips (add column + "try" ) !

[@capitandisfruton1400]

what about the red white dash line in the query editor??

[@avivzach4926]

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

[@michelleodell1236]

Thank you so much! I have been fighting with this for hours!

[@CurbalEN]

🥳 Glad it helped! /Ruth

[@donovannewton9507]

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?

[@referencialstore4591]

I have an ID collumn with numbers which I need to use as a text. I did change it's type to text, but when I load, my power query error says: "we can't convert it in numbers Detail: 000000000-1" what should I do?

[@yes-pg5dy]

If i have two different date formats in the same column but I want to see in the one format only then how to achieve this.... ???

[@monalisaakinyemi1473]

This was very helpful. Thanks Curbal

[@romeop2345]

Hi Ruth, How to get rid of stack overflow error? Thanks

[@peteadrian7743]

Okay. so I set up a query (active members) and it worked fine. Then I copied it over to another worksheet where I am tracking activity, so I can update the list of members whose activity I'm tracking. Now, on the copied query, I get this error message: "Expression.Error: We couldn't find an Excel table named 'Table 1'." what the heck happened and how can I fix it?

[@MiguelRodriguez2010]

Bravo!! Great knowledge as always!

[@georgexenakis3545]

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!

[@CurbalEN]

👏👏

[@Nikhilmandekar]

You are great. Thanks a lot for detailed video. 🙂

[@YamaDrahma]

Thank you very much for these solutions! It helped me deal with someone's bright idea to provide inconsistent data types :)

[@CurbalEN]

😂😂

[@1yyymmmddd]

Hi. What if I do not get TRUE in HasErrors column because the error is somewhere after a 1 million rows ?

[@bhuvaneshbabu8238]

This is amazing Ruth.. thank you for sharing🙂

[@stephenanointing7345]

Tbh you're teaching skill is so cool

[@muraliprasath99]

Thank you so much. You made my day. Great Job!

[@CurbalEN]

Thanks!

[@dr.sushilkumarph.d.8186]

Great video......Thanks CURBAL

[@barttitulaerexcelbart9400]

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)?

[@CurbalEN]

Hi Bart! I probably explained myself poorly, the neat thing about this trick is that it finds errors over 1000 line pq limit. Test it!! /Ruth

[@barttitulaerexcelbart9400]

@@CurbalEN Thank you Ruth, I just did this in Excel. Works fine!!

[@CurbalEN]

Oh and there is a “keep errors” button in power query, use that instead :) Not sure if excel has it, but probably! /Ruth

[@sonu1patel]

upon trying to create col..by examples I get red & white lines. I can cancel and close to load to come out. I see under under other queries - table table and says x,xxx errors. how do i trouble shoot and correct this. Help! any one?

[@zoeygreene]

Hello, thank you for the video.. I am trying to add a column if between 2 values.. the problem is the values are GL account ID.. for example: 10101.. i try to say if [account_id)*1

[@Angela-bw3mb]

Genius!! Thanks a lot

[@pedropradocarvalho]

This video is so good I wish I could like it two times!

[@CurbalEN]

Thanks!

[@Raju-we6ow]

This helped me alot. You are the best 👍🏼

[@CurbalEN]

🥳

[@josepromero1452]

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.

[@CurbalEN]

Thanks Josep! /Ruth

[@rachidwatcher5860]

Good Idea, but when you deal with a millions of rows if you try to use quality bars or column profile it will take hours to know where the errors are.

[@CurbalEN]

@rachidwatcher5860 Yes, it is reaaaaally slooow :(

[@giovannadb8698]

THANK YOU - THANK YOU - THANK YOU SOOOOOO MUCH .... you just saved my Report and I managed to fix Errors which looked like MONSTERS

[@CurbalEN]

🎉🎉

[@rachidwatcher5860]

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?

[@damarmuhammet]

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

[@castromarcio]

You really save my life... thanks a lot

[@CurbalEN]

My pleasure :)

[@ybarsabal]

DataFormat.Error: Invalid cell value '#REF!' How do you deal with this error? My query combines several Excel workbooks, and somewhere there's a broken reference. This is problematic because PQ won't even Keep Errors or Replace Errors, so I don't even know which workbook to fix. :(

[@ravingagunawardana178]

Thank you!

[@tomcarter3586]

I am running into this error when trying to pivot a table and wondering if you know a solution for this thanks Expression.Error: There weren't enough elements in the enumeration to complete the operation. Details: [List]

[@midsummer4864]

Thanks for this

[@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!

[@CurbalEN]

Oh, I don’t normally use google sheets, so I haven’t seen it before. I add it to my list and If I come across the issue I will do a video about it. For faster resolution check with the power bi community, they might have a solution already! /Ruth

[@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 😉

[@CurbalEN]

Thanks for sharing :)

[@shadowitself]

Curbal, You know what - U'r kind of live saver :D thanks fully digestable metarial (another one ;) )

[@CurbalEN]

At your service ☺️

[@dr.sushilkumarph.d.8186]

I am looking for a video that discusses the solution for column name problems. When we have 100s of sheets or many Excel files, so column names are likely to be mismatched in some of the tables/sheets. How to resolve this issue...............

[@sparshkapoor8517]

I am getting following error while uploading the excel dataset in power bi. can anybody help me out troubleshoot this. Unexpected error: The specified key is not a valid size for this algorithm. Parameter name: key Key length: 40 Details: Microsoft.Mashup.Evaluator.Interface.ErrorException: The specified key is not a valid size for this algorithm. Parameter name: key Key length: 40 ---> System.ArgumentException: The specified key is not a valid size for this algorithm. Parameter name: key Key length: 40 ---> System.ArgumentException: The specified key is not a valid size for this algorithm. Parameter name: key Key length: 40

[@monalymistry2010]

Ruth, could you use the keep errors seletion?

[@CurbalEN]

That is the one to use for sure :) /Ruth

[@deesterdee6650]

Thank you soooooo much!!! :)

[@CurbalEN]

🥳🥳/Ruth

[@leiladousti3769]

hello , pls let me know where is the first video for power bi learning i want to watch it as a beginner.

[@CurbalEN]

Hi Leila, Try these: m.kzbin.info/www/bejne/ram5oKyCmpqjgtU m.kzbin.info/www/bejne/iHyqaoKCfrFgj9E m.kzbin.info/www/bejne/on2uamuEbqp2gqM /Ruth

[@sarathiveda361]

Gud one. Sorry for yesterdays comments. I was frustated with some functions in BI

[@CurbalEN]

No worries. /Ruth

[@Henrik.Vestergaard]

View > Column distribution: here I can see the error even though the profiling line is green as in your example. Click the distribution line > Keep error

[@CurbalEN]

I don’t have that on unless I need it because it slows down power query (in my experience), but thanks for sharing!! /Ruth

[@bhuvaneshbabu8238]

I am facing a different error while querying an OLAP cube using Analysis Services. "DataSource.error Analysis Services : The server sent an unrecognizable response" I am unable to understand this error. Its been a week.

[@CurbalEN]

I Don’t know what that could be

[@georgetosounidis5545]

Fantastic!

[@CurbalEN]

🥳 /Ruth

[@ahmedal-dossary4386]

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?

[@ahmedal-dossary4386]

Hi Ruth, I finally fixed this error. There were two things: 1) Data type mismatch in one of the columns (having both text and number) and data type assignment was number. So I replaced all errors with a dummy number. 2) Another table name was actually changed in current month and power query couldn’t identify this name and caused the loading error. You really made my as I was about to work on previous version of this dashboard and performed a lot dax calculations and formatting.

[@CurbalEN]

Glad you worked it out and share the results to help others :). /Ruth

[@jesuseduardobittersuarez7517]

hello can you made a video about this error:" Initialization of the data source failed" cant use power query in an clean excel sheet, dont know what to do

[@CurbalEN]

I dont think I have seen that error, can you check with the power bi community? /Ruth

[@jesuseduardobittersuarez7517]

@@CurbalEN I had but no where to be help :(

[@CurbalEN]

Report it as a bug at issues.powerbi.com to see if you get help there. /Ruth

[@diogosilva588]

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?

[@CurbalEN]

No, something else might be wrong :(

[@diogosilva588]

@@CurbalEN Thank you very much for your response. After some time i already reached a solution. For someone that ends up with the same problem here's the problem and solution: The problem was that i had a column that had decimal numbers and the type of that column was set for integer numbers. Once i changed it to decimal numbers the errors were gone. I checked and the amount of errors given was equal to the amount of decimals in that column. In conclusion, it may identify the error but it will not show it maybe because it still worked as a number. Thanks again and keep the good work :)

[@CurbalEN]

Awesome, thanks for sharing!

[@agapheputrasusilo741]

Thanks mam

[@alexandrus]

Home button keep row - keep errore

[@CurbalEN]

Oh! Cool, thanks! :) Pin for more visibility!! /Ruth

[@ThGrum]

Hello all, I know I'm a bit late with this hint but you can show all rows by clicking in the bottom bar where the message shows that the data set is based on the first 1000 rows and then choose the complete data set.

[@EricaDyson]

Thanks Ruht. Helpful, but shouldn't one go back into the source and correct the errors because if you just select remove errors, you delete the rows don't you from the dataset.. and surely that isn't something we should be doing,unless we are sure those rows shouldn't be there in the first place. Or have I missed something?

[@CurbalEN]

Sometimes you don’t don’t want the rows with errors maybe because you know the data is incorrect or you can’t correct it and in those cases, removing them is a valid option. :) /Ruth

[@McKaySavage]

Also, sometimes (surprisingly often) the client themselves needs to correct the errors, or some database work needs to be done to correct a systemic error and this often takes weeks (or longer!). In these cases, removing rows or replacing errors with a null (or an obvious "placeholder" value) is often needed as a temporary solution so we can keep working on the data without errors while the original data is being fixed. This is also a major case when there is unfortunately a high chance of new errors being introduced as live data gets added to an existing report. One needs to build a solution that can both handle and identify errors so they don't halt refreshes or mess up visuals.

[@eagillum]

The time when you click to view errors and it doesn't give you anything: 1:41 (click on Edit Data/Transform Data) Solutions are at 5:25

[@EricaDyson]

Gotcha! We're aligned then!

[@EricaDyson]

Hi Ruth.. a question. Wouldn't we leave the query step (add custom column with 'try=") in there, so that next time we update the dataset, it's easy to see if there are errors?

[@ddvantandar-kw7kl]

Look what is important is that take the people through problem

[@silverfunnel6819]

You have the special look. Like!

[@elijahd.spragueph.d8905]

If only power query would perform calculations despite errors. I don't understand why having one error messes up the whole process.

[@CurbalEN]

Sometimes it does, sometimes it doesnt. Havent figured out yet why sometimes it doesnt :( You can always add a “replace errors with” step to load the data regardless.

[@user-ic3jf5yl4c]

i complete 1k like...

[@PC-wg2mp]

I could not understand what you are speaking. Not clear

[@CurbalEN]

Put the subtitles on, that might help

[@VinceM797]

Thank you!