Load was cancelled by an error in loading a previous table when I refresh Power BI

[Anonymous]

Hi,

I've saved down data in the form of Excel which is linked to my Power BI report, when I refresh Power BI report it comes back with Load was cancelled by an error in loading a previous table.

I saved the excel file with the same file name as before so I did not need to change the data source each time, so I know it's not due to a change in the source file name.

I can't seem to see what else it could be.

Woould be very grateful to hear from anyone else who has encountered this error.

[Syndicate_Admin]

Hello
I have solved this same problem differently.

In my case, the update tables had included blank null rows in all cells.

Only, I've added a step in each dimension and Facts table from Power Query, with the option:

Remove rows => Remove blank rows.

If you manage to solve your problem in this way, like the comment so that other users take advantage of it.

[Anonymous]

Hi @Greg_Deckler 

There is a Promoted Headers step before the Change Type step.

[AllisonKennedy]

Ok, and what is the name of the first column when you select the Promoted Headers step? And what are all steps above that one? 

[Anonymous]

Ok, we've found it @AllisonKennedy ,

When I click on the Promoted Header step the title of the first, second and third columns are actual data itself.

I really can not think how this could have happened as I saved over the file with a fixed file name.

[AllisonKennedy]

I can't be certain, but as I hinted at in my previous post I suspect that at one point this data was pointing to an Excel worksheet and therefore needed to promote headers. Is it now using an Excel table? If so, it therefore does not need to promote headers and in doing so, uses the first row of data as column headings which then breaks the Changed Type step. 

In any case, glad we solved the problem. Take note of those steps, as this is a very useful exercise in troubleshooting Query Editor issues and will no doubt come in handy again. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Hi @AllisonKennedy @Greg_Deckler 

Thank you both with your assistance today, I managed to solve the issue in the end but this was also linked to another upload error, however I learnt alot expecially with locating the error in the Navigation page.

[Greg_Deckler]

Happy to assist @Anonymous ! What you learned will be very useful in the future!!

[Greg_Deckler]

There should be a more information link. Also, go into Power Query and see what query has an error. There should be information there to tell you what is happening.

[Anonymous]

Hi @Greg_Deckler 

I've reselected the Source files again and now I have an error that states "the column nOrderID of the table wasn't found".

However this is column has always been there as I checked a report I previously produced.

[AllisonKennedy]

@Anonymous   "the column nOrderID of the table wasn't found". means that nOrderID was previously in the Excel file but is now not being pulled through to the Excel file. If this is the first column of the Excel sheet you might have an issue with the headers not promoting properly. As @Greg_Deckler suggested the best way to solve this is to open the Power Query Editor, navigate to the 'table' that has the error and click the 'Go to error' on the yellow ribbon. Keep us posted on what messages you receive if you're still struggling.

[Greg_Deckler]

OK, so click on the Source or Navigation step and see if that column is in the data. Basically, work your way down through the steps of the query and see which step gives you an error where no data is displayed.

[Anonymous]

Ok, so first I checked my source data and the column is still there and it's the first column in my data set.

Then I went into Transform Data and clicked on Go to Error and it moved instantly to the Changed Type navigation step in the Navigation bar.

Could deleting this step get rid of this error?

[Greg_Deckler]

Maybe. I would save a copy of the query first or use Advanced Editor, copy the code and paste it into Notepad or something so that you don't lose it. What is the step above Changed Type? Does it perhaps rename columns or perhaps there is a missing Promoted Headers step?

[AllisonKennedy]

Can you please click on the step above Changed Type? what does the table look like in the query editor? Note you may need to click 'refresh preview'

Deleting the changed type step might solve the problem, but data type is very important so I'd rather get to the source of the error.

[Anonymous]

Hi @AllisonKennedy 

This is what appears in the formula bar Table.TransformColumnTypes(#"Promoted Headers",{{"nOrderId", Int64.Type}, 

[AllisonKennedy]

It sounds to me like Power BI is still doing a promoted headers step even though the navigation step is now selecting the table instead of worksheet. 

Follow @Greg_Deckler 's advice to save your current file or code, and then try deleting the Promoted Headers step. Does that solve the problem?

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Pragati11]

Hi @Anonymous ,

It will be nice to have some screenshots around the issue. This doesn't help in answering your query.

Thanks,

Pragati

[AllisonKennedy]

@Anonymous  This is a very common and generic error, so you are not alone. 

We will need more information to be able to help you though. Are you able to send screenshot of the error or let us know all the text, including which table results in error and what the error message is? Often it is relating to a column name being missing or changed.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Hi @AllisonKennedy 

Thanks for getting back to me.

For some odd reason it would not let me paste a screen shot as I tried on my original post.

Guess I will just have to try and figure this one out.