Power query errors: Detect, prevent & fix them
HTML-код
- Опубликовано: 7 авг 2024
- Power Query is an amazing tool to manipulate, reshape and change data however managing & fixing errors can be challenging in the app. Something as simple as changing case of a column name can cause it to break.
Sometimes errors appear on load, other times the entire query breaks or they could come from the data source. I cover in this video the different types of errors, how to detect & fix them and good practice to avoid making errors.
I cover all different kinds of errors at various points of the video:
0:00 - Introduction
0:49 - Errors in cells
5:41 - Errors in query steps
7:27 - Errors in precedent queries
11:13 - Data source errors
14:06 - Relationship errors
15:25 - Transformations to avoid (as they often break) 
Хобби
Using unpivot column was a great help to detect cell errors in a file containing thousands of both rows and columns, thanks a lot!
No worries! Glad you like it. I also have a shorter, more recent video where I go through using the try function, to convert an error message into a cell, I now use that solution together with the one in this video.
Considering I have a mess of a query of over 500 files pulled into tables then appended that takes minutes to load this is a life saver on time. Thank you
Amazing! Great to hear you found it useful. Feel free to share with others too 😃
Very clear and lot of information is given in the video, which normally you don't find in other training Playlist
Thanks so much! Glad you like it
Thanks for getting right to the point and saving me some time!
Glad you like it, it’s actually one of my longer videos!
Wow, clear, detailed, and to the point. Thank you, that is very useful.
Thanks so much! Glad you like it!
Thank you. Very useful info.
David, you realy help me with this video, thanks from Portugal
Glad you find it useful 😃
Great video. I was using an API and when drilling down i was getting errors for some columns.
Thanks! I have another more recent one about the try function that would be useful to check out too
Thank you David
Glad you found it useful 😃
Just discovered your channel. Thanks for this great video. Can you maybe do one on Data Load - Background Load settings. I sometimes open a query and then wait for a while for everything in background to first update before I can work. I assume I have to select Never Allow? Is it that simple a setting or are there issues I should know of? Kind regards from South Africa.
Hi sorry I missed this comment, i tend to use custom load settings & tick nothing in query settings so that I manually choose which queries to load & which are “connection only” which ends up being most of them! The refresh settings get quite complicated actually sadly
THANK YOU! 🙌
Glad you like it!
Hmmm I have a power query linked to a file that is used by the entire company. What can I do to avoid errors every day if someone in a different department make changes to it?
Good question, this would be hard to manage. I personally would make the data input more robust through data validation, error counts etc. Or create an issues log via Power Query, I have one video on each use case
Thanks a lot this is realy helpful
Glad it helped
THANK YOU
What is the best way to update the records in the source table/sheet directly form the Power Query ( like, adding records, deleting a record, editing,.....). Can we make a column properties ( one or more columns) a Hyperlink that be clicking it, a custom data entry form be opened to add, deleted or update the record/s in the source tables?
I’d love to know the answer of this too
Hi sorry I think you’re trying to push power query beyond what it is able to do. It isn’t able to return formatted text (like hyperlinks or colours) and it’s only about manipulating existing data not creating new data albeit there is a way to add a custom table through “enter data”
Super .. thank you.
No worries!
Hey David. Pl advise how to enable that error bar showing as green and red line right below of headers? i am using office 2019 version but unable to enable this.
Im not 100% sure if this is an Office 365 only feature, if it is in 2019 you will find it by clicking on the "View" tab then choosing "Column quality"
Hi David, I've managed to replace all the errors in my table using unpivot other columns. How do I incorporate those changes to the main table ?
You can re pivot the table using pivot columns but requires for all rows to be intact & no blanks I have another video on pívot columns if you like. In my case I usually go back to the source & change the data there
Hi David, great channel! I keep getting this error "Expression.Error: We couldn't find an Excel table named 'PO!_FilterDatabase'." What is causing this and how can I fix it?
Hey! Thanks for the feedback, glad you like it. Sorry but I’m not sure about that one
Hi! Can you help me with the problem? It says "dataformat.error the input couldn't be recognized as a valid excel document: binary"
Hello, Ive not seen that one before but it seems to suggestthe file is saved as .xlsb maybe try opening the source file and save as a different type. .xlsx is the most common one. This talks about your issue: exceleratorbi.com.au/importing-xlsb-into-power-bi/
Another type of error I am getting is "Load was cancelled by an error in loading previous table". The data is pretty huge and I have done few merging and changing data types and renaming etc but its not listing any error in the table per se, it just shows the error and when I close that error and go back to qry editor I don't see warnings in any tables or steps. How do you detect the exact error in that case or how do you fix that?
Hey. That means there is usually the main error in another table that may not even be connected to this one, when you see that error, scroll down all the queries and you will usually see one which has a different error. Fix that different error and others should be ok
@@learnspreadsheets Than you .I already started a new one and that worked. But I am sure what you said will work as well. Thank you again.
Yay! Glad it’s worked out
@@learnspreadsheets Thank you
Hello to everyone. I wanted to know if anyone could help me please? PowerQuery doesn't stop wanting to recognize EVERYTHING in a column as number, when I clearly ask for text, as I want to keep my data exactly as it is in that column. Is there a way to avoid that? PowerQuery just eliminates those rows as they appear as errors, when they are not for me! I changed the automatic type detection, and it still happens :(
Hi there, Power Query loads the data as type: any, there would be a step to change this into type: number, that could happen automatically or manually. If you look on the right you should see a step called "Changed type" somewhere and you should be able to delete it. Hope that helps
Hi David,
I have moved excel dashboard created in server with Office-365 to server with Office-2016, and still connecting to same db. I am able to open power query editor and establish DB connection. But I am getting below error when i exit the power query editor by saying "Close and load"
"We couldn't get data from the Data Model. Here's the error message we got:
No error message available, result code: -2146233052(0x80131524)"
Can you please help with this issue.
Hello sorry but that is quite specific so I cannot help I’m afraid
when im type 2222 after that i showing 22.22 Why coming point befor last two number please tell me
Sorry I don’t understand
HI David, I have made a power query in excel version 2102.But my clients version it is 2008.So for them whenever they open the excel.it is showing "implementation is not a valid option.Valid option is "Api Version". and their queries are not refreshed and loaded.SO what can we do for this?In the source formula of the power query,implementation and api version functions are there I tried removing implementation and made api version="Auto".But still it is not working.Can you suggest any other way?
2008 means year 2020 month 8, aug 2020. 2102 means feb 2021. Assuming you’re in the semi annual cycle, this month you should both be able to update to 2108 aug 2021 then everyone will have the same version
@@learnspreadsheets Thankyou for the reply David.In that case will this power query work properly without showing the above error?why is it showing?
Heya if a query is made with a newer version of might not work with the other one. Update all excels to the new version & it should work well
Hu David, for errors in cell when I load the data the power bi is not taking in all of the data because of those data format errors, is there a way of fixing those errors in power query
The issue I am facing is that if I make a pivot table in Excel for that data I see tot. Numbers as 50,000 (which is correct) but when I load it in power BI through the power query it shows data format in some rows and hence the tot. Numbers I am getting are like 48,000
Any fix for this issue as I can't work with the data in power BI as the data is having wrong values :(
Thanks for the question, it would be hard for me to answer without seeing, some of the forums are more made for these scenarios where you can upload files/screenshots etc. Maybe try those? Sorry I can't be of more help
@@learnspreadsheets no worries bro, will surely try out the forums
@@ryansodhi1815 Nice, good luck!
Can you help me with Expression.Error : Token Equal Expected?
Thanks!
Heya I’m not sure sorry, easiest way is to google that & see what comes up
@@learnspreadsheets Hi!
Yeah tried that before did not get any relevant information 😅😅
Thanks anyways!
Sorry! I haven’t seen that one before
You missed to include error which says underlying connection closed SSL issue which prevents connecting Data from web link to power query
Hi, thanks for the feedback. That is a good point but there are too many Power Query errors to handle them all, I rather cover the concepts, for specific errors I would probably type it into Google and it should be helpful
Maybe I'm missing something but when you get to the point where you created a new column to show the errors you didn't show how to get rid of them when you have them all in the same column.
Hi thanks for the question. Once you know the errors, you could delete the rows but the point is that it shows you where in your source data to go if you want to fix the errors at the source.
errorFlag":"1","msg1":"THERE IS AN QUERY ERROR"
Sorry there are so many errors. Try pasting that into google or chat gpt & seeing what it gives
Limit of 1000values errer
By default some actions display based on 1000 rows but when you perform actions they should be on the whole dataset, hope that helps