Cannot refresh a power query in Excel Sharepoint (in browser)

%3CLINGO-SUB%20id%3D%22lingo-sub-2082277%22%20slang%3D%22en-US%22%3ECannot%20refresh%20a%20power%20query%20in%20Excel%20Sharepoint%20(in%20browser)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2082277%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20file%20on%20Sharepoint%20(Office%20365)%20that%20has%20a%20power%20query%20which%20creates%20a%20table%20(TableB)%20that%20uses%20another%20table%20(TableA)%20in%20the%20same%20file%20as%20its%20source.%20If%20I%20open%20it%20In%20App%2C%20I%20can%20do%20a%20RefreshAll%20and%20TableB%20is%20updated%20if%20the%20contents%20of%20TableA%20have%20changed.%20However%2C%20if%20I%20try%20to%20do%20the%20same%20with%20the%20file%20open%20in%20the%20browser%2C%20I%20first%20get%20a%20warninin%20stating%3A%20%22%3CSPAN%3EBe%20careful.%20The%20query%20to%20get%20the%20data%20might%20be%20unsafe%20so%20you%20should%20only%20refresh%20the%20workbook%20if%20you%20trust%20its%20source.%20Do%20you%20want%20to%20refresh%20the%20workbook%3F%22.%20If%20I%20click%20on%20Yes%2C%20I%20get%20an%20error%20titled%20%22External%20Data%20Refresh%20Failed%22%20saying%20%22We%20were%20unable%20to%20refresh%20one%20or%20more%20data%20connections%20in%20this%20workbook.%3CBR%20%2F%3EThe%20following%20connections%20failed%20to%20refresh%3A%20Query%20-%20qryUpdate%22%2C%20which%20is%20the%20name%20of%20the%20query%20that%20powers%20TableB.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20the%20file%2C%20I%20own%20it%2C%20I%20have%20permissions%20to%20delete%2Fupdate%2Fcreate%20files%20and%20folders%20in%20my%20department's%20Sharepoint%20area.%3C%2FP%3E%3CP%3EThis%20is%20an%20issue%20with%20any%20file%20containing%20a%20Power%20Query%20that%20I%20try%20to%20update.%20One%20final%20thing%2C%20I%20used%20to%20be%20able%20to%20refresh%20these%20queries%20until%20a%20few%20days%20ago.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20so%20many%20different%20approaches%20suggested%20by%20posts%20on%20forums%20but%20none%20have%20worked.%20I%20feel%20it%20must%20be%20some%20sort%20of%20permissions%20issue%20but%20I%20am%20a%20total%20newbie%20to%20Sharepoint%20so%20I%20do%20not%20know%20how%20to%20fix%20the%20problem.%20I%20am%20an%20experienced%20programmer%2C%20especially%20in%20Excel(desktop)%2C%20so%20I%20find%20it%20frustrating%20that%20something%20that%20should%20be%20so%20easy%20cannot%20be%20done.%20I%20have%20even%20looked%20created%20a%20Typescript%20macro%2C%20which%20just%20has%20one%20functional%20line%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3Efunction%20main(workbook%3A%20ExcelScript.Workbook)%20%7B%0A%20%20%20workbook.refreshAllDataConnections()%3B%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CSPAN%3EThis%20runs%20without%20error%20but%20does%20not%20update%20the%20table.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EAny%20help%20would%20be%20appreciated.%20I%20have%20attached%20a%20basic%20example.%20In%20the%20real%20product%20there%20are%20many%20more%20columns%20in%20TableA%20and%20TableB%20has%20had%20calculated%20columns%20added.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThanks%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EChris%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2082277%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharepoint%20Power%20Query%20Error%20Updating%20Excel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi,

 

I have a file on Sharepoint (Office 365) that has a power query which creates a table (TableB) that uses another table (TableA) in the same file as its source. If I open it In App, I can do a RefreshAll and TableB is updated if the contents of TableA have changed. However, if I try to do the same with the file open in the browser, I first get a warninin stating: "Be careful. The query to get the data might be unsafe so you should only refresh the workbook if you trust its source. Do you want to refresh the workbook?". If I click on Yes, I get an error titled "External Data Refresh Failed" saying "We were unable to refresh one or more data connections in this workbook.
The following connections failed to refresh: Query - qryUpdate", which is the name of the query that powers TableB.

 

I created the file, I own it, I have permissions to delete/update/create files and folders in my department's Sharepoint area.

This is an issue with any file containing a Power Query that I try to update. One final thing, I used to be able to refresh these queries until a few days ago.

 

I have tried so many different approaches suggested by posts on forums but none have worked. I feel it must be some sort of permissions issue but I am a total newbie to Sharepoint so I do not know how to fix the problem. I am an experienced programmer, especially in Excel(desktop), so I find it frustrating that something that should be so easy cannot be done. I have even looked created a Typescript macro, which just has one functional line

 

 

 

 

function main(workbook: ExcelScript.Workbook) {
   workbook.refreshAllDataConnections();
}

 

 

 

 

This runs without error but does not update the table.
 
Any help would be appreciated. I have attached a basic example. In the real product there are many more columns in TableA and TableB has had calculated columns added.
 
Thanks,
Chris
0 Replies