How to deal with errors in Power Query (2 ways)
Вставка
- Опубліковано 5 жов 2024
- 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/don...
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/cou...
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
![[UA] NAVI проти G2 Esports | Blast Premier Fall Final](http://i.ytimg.com/vi/QWlIm4FGESQ/mqdefault.jpg)
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
Fantastic! Let me pin your post as you cover the other suggestion too!
Thanks a million for sharing :)
/Ruth
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?
Powerful lifebouy thanks a million
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
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
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.
4 Years later, you still make my day!
🥳🥳
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.
Thanks Josep!
/Ruth
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.
@rachidwatcher5860 Yes, it is reaaaaally slooow :(
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 😉
Thanks for sharing :)
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?
Thanks very much for this. I found the error in a different column than I originally thought!
Much appreciated Ruth - I simply used your logic to identify errors and corrected it by selecting a better data type.
It is a lifesaver trick :)
/Ruth
Yes! Thank you very much, fixed all the errors (which I have been ignoring until now). Perfect, thank you!
Wonderful 👏!
Thanks Ruth great solution. Null values are my friend in Power Query !
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!
👏👏
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)?
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
@@CurbalEN Thank you Ruth, I just did this in Excel. Works fine!!
Oh and there is a “keep errors” button in power query, use that instead :)
Not sure if excel has it, but probably!
/Ruth
Tbh you're teaching skill is so cool
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?
No, something else might be wrong :(
@@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 :)
Awesome, thanks for sharing!
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!
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
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
Please note that there is a keep errors button. That will save you all my steps!
/Ruth
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?
You are doing very valueable job... thanks a lot... if we have a question, may i ask ? am juniar on power bi...
THANK YOU - THANK YOU - THANK YOU SOOOOOO MUCH .... you just saved my Report and I managed to fix Errors which looked like MONSTERS
🎉🎉
Very usefull ! Thanks for the Tips (add column + "try" ) !
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
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
I don’t have that on unless I need it because it slows down power query (in my experience), but thanks for sharing!!
/Ruth
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."
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
Hi Ruth,
How to get rid of stack overflow error?
Thanks
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.
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...............
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]
This video is so good I wish I could like it two times!
Thanks!
Thank you so much! I have been fighting with this for hours!
🥳 Glad it helped!
/Ruth
This helped me alot. You are the best 👍🏼
🥳
what about the red white dash line in the query editor??
This was very helpful. Thanks Curbal
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.... ???
You really save my life... thanks a lot
My pleasure :)
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?
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.
Glad you worked it out and share the results to help others :).
/Ruth
This is amazing Ruth.. thank you for sharing🙂
Thank you very much for these solutions! It helped me deal with someone's bright idea to provide inconsistent data types :)
😂😂
You are great. Thanks a lot for detailed video. 🙂
Thank you so much. You made my day. Great Job!
Thanks!
Home button keep row - keep errore
Oh! Cool, thanks! :)
Pin for more visibility!!
/Ruth
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! 🙂
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?
Thanks for sharing this Ruth. Good job always.
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?
Hi. What if I do not get TRUE in HasErrors column because the error is somewhere after a 1 million rows ?
Curbal, You know what - U'r kind of live saver :D
thanks
fully digestable metarial (another one ;) )
At your service ☺️
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?
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
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.
Great video......Thanks CURBAL
Gud one. Sorry for yesterdays comments. I was frustated with some functions in BI
No worries.
/Ruth
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. :(
Thank you!
Genius!! Thanks a lot
Bravo!! Great knowledge as always!
Ruth, could you use the keep errors seletion?
That is the one to use for sure :)
/Ruth
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.
I Don’t know what that could be
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?
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.
hello , pls let me know where is the first video for power bi learning i want to watch it as a beginner.
Hi Leila,
Try these:
m.ua-cam.com/video/wxVoyLe5pLs/v-deo.html
m.ua-cam.com/video/RKG9OLIL-Yo/v-deo.html
m.ua-cam.com/video/lLK98N9ECLA/v-deo.html
/Ruth
Thanks for this
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
Gotcha! We're aligned then!
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?
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
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
I dont think I have seen that error, can you check with the power bi community?
/Ruth
@@CurbalEN I had but no where to be help :(
Report it as a bug at issues.powerbi.com to see if you get help there.
/Ruth
If only power query would perform calculations despite errors. I don't understand why having one error messes up the whole process.
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.
Look what is important is that take the people through problem
Thanks mam
Thank you soooooo much!!! :)
🥳🥳/Ruth
You have the special look. Like!
Fantastic!
🥳
/Ruth
I could not understand what you are speaking. Not clear
Put the subtitles on, that might help
i complete 1k like...
oi
Thank you!