Cannot find table - accidentally removed data table from direct query source

[AnttiKai]

Hey all, first time for me at the power BI forums!

I've got a report based on a dataverse dataset with a direct query connection. I used to do testing of visualisations with a test table in Dataverse and now as the solution is ready for now, I deleted the table used for testing (I think this is the problem). However, in my powerBI dataset there is a piece of table that tries to draw from that dataverse table that I deleted earlier. This, I think produces such an error that I can't even open the .pbix file to fix the query...

Error: " Failed to save modifications to the server. Error returned: 'Cannot find table 'cr149_taskresource (2)'.
OLE DB or ODBC error: [Expression.Error] The column 'cr149_toteutetaanprojektinaname' of the table wasn't found.. "

Any ideas in how to solve the issue would be appreciated!
Regards
/AnttiKai

[AnttiKai · Accepted solution]

God thanks awhhh, I fixed it with such bubble gum that it's hard to believe 😄

The error reports gave 1-2 errors at a time for the data source, tables or columns that didn't exist anymore. There was more conflict / querys for non-existing table columns than I could first remember. One by one I created those back to dataverse as dummy-columns that existed again for respective tables with similar properties but with no data. After some "fixes" power bi seemed to decide that the data source was not "too much wrong" anymore, the file opened and I could fix the query to reflect changes in the data source.

[AnttiKai]

So, the main issue here is still that the file doesn't open with PowerBI desktop, at all. Just pops that error message and closes the program. I've tried to look into the .pbix file by turning it into a .zip file and going through the contents. It seems quite unlikely that I could remove all mentions of the deleted table as it is mentioned in the file 1700+ times and not very friendly code.

The report file has some 100 hours of work in it that I could recreate in maybe 20 hours now that I've done it once. Such a pain if it's the only way.. Seems problematic that an unlucky change in dataverse tables prevents opening a DirectQuery pbix.-report file at all so that it could be modified.
Any ideas would be welcome
Regards

[selimovd]

Hey @AnttiKai ,

if you don't need the table anymore that was connecting to the test dataverse table delete it.

Otherwise change the Query to the real table you want to connect to.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[AnttiKai]

Hey @selimovd !

Ah, the main issue that I'm facing, which I didn't state clearly enough is, that the report containing the powerbi dataset doesn't open with powerBi desktop. It functions perfectly and all changes in data are real-time visible in the visuals (online), but it seems like this "disconnect" in data prevents me from opening the dataset and report to be edited. Also if I do a re-download from powerBI web, powerbi desktop can't open it.
Regards