Power query errors: Detect, prevent & fix them

[@TheNkatsar]

Using unpivot column was a great help to detect cell errors in a file containing thousands of both rows and columns, thanks a lot!

[@learnspreadsheets]

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.

[@jackearlbryantjr]

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

[@learnspreadsheets]

Amazing! Great to hear you found it useful. Feel free to share with others too 😃

[@cherylbermudez5452]

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?

[@learnspreadsheets]

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

[@theplatinums2951]

Very clear and lot of information is given in the video, which normally you don't find in other training Playlist

[@learnspreadsheets]

Thanks so much! Glad you like it

[@tacijjola]

Wow, clear, detailed, and to the point. Thank you, that is very useful.

[@learnspreadsheets]

Thanks so much! Glad you like it!

[@keiljuanaphillips9663]

Thanks for getting right to the point and saving me some time!

[@learnspreadsheets]

Glad you like it, it’s actually one of my longer videos!

[@mradzi87]

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 ?

[@learnspreadsheets]

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

[@susheelchandra5228]

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.

[@learnspreadsheets]

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"

[@alvinyudapratama8001]

Hi! Can you help me with the problem? It says "dataformat.error the input couldn't be recognized as a valid excel document: binary"

[@learnspreadsheets]

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/

[@dianarama1215]

David, you realy help me with this video, thanks from Portugal

[@learnspreadsheets]

Glad you find it useful 😃

[@mikelennon1078]

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?

[@scottreed9602]

I’d love to know the answer of this too

[@learnspreadsheets]

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”

[@ryansodhi1815]

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 :(

[@learnspreadsheets]

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

[@ryansodhi1815]

@@learnspreadsheets no worries bro, will surely try out the forums

[@learnspreadsheets]

@@ryansodhi1815 Nice, good luck!

[@gborka]

Thank you. Very useful info.

[@sherryqueen31]

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?

[@learnspreadsheets]

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

[@sherryqueen31]

@@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.

[@learnspreadsheets]

Yay! Glad it’s worked out

[@sherryqueen31]

@@learnspreadsheets Thank you

[@fathimasamrinp2743]

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?

[@learnspreadsheets]

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

[@fathimasamrinp2743]

@@learnspreadsheets Thankyou for the reply David.In that case will this power query work properly without showing the above error?why is it showing?

[@learnspreadsheets]

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

[@bleepmaster23]

Great video. I was using an API and when drilling down i was getting errors for some columns.

[@learnspreadsheets]

Thanks! I have another more recent one about the try function that would be useful to check out too

[@kerivchar]

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.

[@learnspreadsheets]

Hello sorry but that is quite specific so I cannot help I’m afraid

[@soumyadeepmondal6130]

Can you help me with Expression.Error : Token Equal Expected? Thanks!

[@learnspreadsheets]

Heya I’m not sure sorry, easiest way is to google that & see what comes up

[@soumyadeepmondal6130]

@@learnspreadsheets Hi! Yeah tried that before did not get any relevant information 😅😅 Thanks anyways!

[@learnspreadsheets]

Sorry! I haven’t seen that one before

[@adikuchkar2575]

when im type 2222 after that i showing 22.22 Why coming point befor last two number please tell me

[@learnspreadsheets]

Sorry I don’t understand

[@alancoppin219]

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?

[@learnspreadsheets]

Hey! Thanks for the feedback, glad you like it. Sorry but I’m not sure about that one

[@abderrahmankilali4782]

Thanks a lot this is realy helpful

[@learnspreadsheets]

Glad it helped

[@migueldejesuslinomata7271]

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 :(

[@learnspreadsheets]

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

[@ezequielalmeida6378]

THANK YOU! 🙌

[@learnspreadsheets]

Glad you like it!

[@ExcelWithChris]

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.

[@learnspreadsheets]

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

[@VeasnaVet]

Thank you David

[@learnspreadsheets]

Glad you found it useful 😃

[@jholwol2699]

You missed to include error which says underlying connection closed SSL issue which prevents connecting Data from web link to power query

[@learnspreadsheets]

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

[@BernieMurray]

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.

[@learnspreadsheets]

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.

[@neilgore]

Super .. thank you.

[@learnspreadsheets]

No worries!

[@luigi5287]

THANK YOU

[@JavedMohammad-rq3nk]

errorFlag":"1","msg1":"THERE IS AN QUERY ERROR"

[@learnspreadsheets]

Sorry there are so many errors. Try pasting that into google or chat gpt & seeing what it gives

[@MohdMoin-uw1ng]

Limit of 1000values errer

[@learnspreadsheets]

By default some actions display based on 1000 rows but when you perform actions they should be on the whole dataset, hope that helps