Refresh all connections button click on excel online gives an error

%3CLINGO-SUB%20id%3D%22lingo-sub-1525591%22%20slang%3D%22en-US%22%3ERefresh%20all%20connections%20button%20click%20on%20excel%20online%20gives%20an%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525591%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20configured%20an%20excel%20file%20with%20a%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fservices.odata.org%2FV3%2FOData%2FOData.svc%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Epublic%20OData%20feed%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eon%20excel%202016%20desktop.%20The%20data%20is%20loaded%20into%20the%20sheet%20from%20Queries%20%26amp%3B%20Connections%20tab.%20Refresh%20works%20fine%20here.%20This%20file%20is%20uploaded%20on%20sharepoint%20which%20is%20then%20opened%20with%20excel%20online.%3C%2FP%3E%3CP%3EWhen%20we%20do%20%22Refresh%20All%20Connections%22%20on%20excel%20online%20we%20get%20an%20error%20as%20shown%20in%20the%20screenshot.%20The%20technical%20details%20of%20the%20error%20are%20also%20shared%20below.%3C%2FP%3E%3CP%3ECan%20you%20guide%20us%20on%20what%20could%20be%20the%20issue%20with%20refresh%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Fi.stack.imgur.com%2FVQQEH.png%22%20border%3D%220%22%20%2F%3E%3C%2FP%3E%3CP%3ETechnical%20details%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EConnection%3A%20Query%20-%20PersonDetails%20Error%3A%20COM%20error%3A%20Microsoft.Sql.Analysis.ASOnPremisesDataAccess%2C%20Failed%20discovery%20for%20connection%20string%20provider%3DMicrosoft.Mashup.OleDb.1%3Bdata%20source%3D%24Workbook%24%3Blocation%3DPersonDetails%3Bextended%20properties%3D%22UEsDBBQAAgAIAICY71BUwQxrpgAAAPgAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAIWPMQ6CMBhGr0K605YKashPGVwlMSEaV1IqNEIxtFju5uCRvIIkiro5fi9veN%2Fjdod0bBvvKnujOp2gAFPkSS26UukqQYM9%2BWuUctgV4lxU0ptkbeLRlAmqrb3EhDjnsFvgrq8IozQgx2ybi1q2BfrI6r%2FsK21soYVEHA6vGM7wiuEoipY4DAMgM4ZM6a%2FCpmJMgfxA2AyNHXrJpfb3OZB5Anm%2F4E9QSwMEFAACAAgAgJjvUA%2FK6aukAAAA6QAAABMAHABbQ29udGVudF9UeXBlc10ueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAG2OSw7CMAxErxJ5n7qwQAg1ZQHcgAtEwf2I5qPGReFsLDgSVyBtd4ilZ%2BZ55vN6V8dkB%2FGgMfbeKdgUJQhyxt961yqYuJF7ONbV9Rkoihx1UUHHHA6I0XRkdSx8IJedxo9Wcz7HFoM2d90Sbstyh8Y7JseS5x9QV2dq9DSwuKQsr7UZB3Fac3OVAqbEuMj4l7A%2FeR3C0BvN2cQkbZR2IXEZXn8BUEsDBBQAAgAIAGw071DdiFNqFgEAAO4BAAATABwARm9ybXVsYXMvU2VjdGlvbjEubSCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAABtj81qwzAQhO8Gv4PQyQZjN%2B2twYdSE%2BilKXXIxZii2pvEIEtGWhlKyLtXstSWlOign9nVfLMaOhykILU%2FV%2Bs4iiN9Ygp68gZKS1EBsoFrUhIOGEfErloa1YFVthVDlm8A%2BoSeECf9WBQa1Dx0oHPZu6JUx2L%2FUCydfs%2F13BU0I8JwnpHmZZw4jCCQuQAlvc%2FvaJtmnnQV4QPZJ3dYzz83r2yEkl710KwejoKhUbay9NP20jhsGyyfDMp3GOUM%2FbPkZhR6ZS197XdCt9DKO%2BeQb5Qc94wbSG7ksSNUcGCGo7dzoezPf7Ha1BsP4g%2FgzX2WECXBLMhB2B52XxM4%2BYz2QgJyuSvopOrDgw8aL6ml%2FBBuzLn%2BBlBLAQItABQAAgAIAICY71BUwQxrpgAAAPgAAAASAAAAAAAAAAAAAAAAAAAAAABDb25maWcvUGFja2FnZS54bWxQSwECLQAUAAIACACAmO9QD8rpq6QAAADpAAAAEwAAAAAAAAAAAAAAAADyAAAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLAQItABQAAgAIAGw071DdiFNqFgEAAO4BAAATAAAAAAAAAAAAAAAAAOMBAABGb3JtdWxhcy9TZWN0aW9uMS5tUEsFBgAAAAADAAMAwgAAAEYDAAAAAA%3D%3D%22.%20A%20connection%20could%20not%20be%20made%20to%20the%20data%20source%20with%20the%20DataSourceID%20of%20'19273d50-a993-4e06-813c-a6675b1d7d94'%2C%20Name%20of%20'Query%0A%0APersonDetails'.%20An%20error%20occurred%20while%20processing%20table%20'PersonDetails'.%20The%20current%20operation%20was%20cancelled%20because%20another%20operation%20in%20the%20transaction%20failed.%0ALearn%20more%20about%20data%20refresh%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1525591%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1526911%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20all%20connections%20button%20click%20on%20excel%20online%20gives%20an%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1526911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730281%22%20target%3D%22_blank%22%3E%40amitshah1935%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPower%20Query%20refresh%20is%20not%20supported%20by%20Excel%20Online.%20Perhaps%20something%20of%20this%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fofficeonlineserver%2Fdata-authentication-for-excel-online-in-office-online-server%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fofficeonlineserver%2Fdata-authentication-for-excel-online-in-office-online-server%3C%2FA%3E%20%26nbsp%3Bworks%2C%20but%20I%20have%20no%20such%20experience.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlternatively%20you%20may%20load%20data%20to%20data%20model%20through%20PowerPivot%20connector%20and%20return%20result%20back%20using%20linked-back%20tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1528595%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20all%20connections%20button%20click%20on%20excel%20online%20gives%20an%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B-%20Thanks%20for%20the%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26gt%3B%20Power%20Query%20refresh%20is%20not%20supported%20by%20Excel%20Online%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOk.%20I%20could%20not%20find%20any%20official%20documentation%20stating%20that%20but%20I%20could%20get%20to%20%3CA%20href%3D%22https%3A%2F%2Fsocial.msdn.microsoft.com%2FForums%2Fsqlserver%2Fen-US%2F1d4a4875-4492-4efb-b684-d31be99efe0f%2Fdata-model-refresh-on-sharepoint-online-with-power-query%3Fforum%3Dsqlkjpowerpivotforexcel%234c154fd2-5dfa-4116-b558-47cb080372dc%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ethis%20forum%20post%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26gt%3B%20Perhaps%20something%20of%20this%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fofficeonlineserver%2Fdata-authentication-for-excel-online-in-office-online-server%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fofficeonlineserver%2Fdata-authentication-for-excel-online-in-office-o...%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20for%20sharing%20the%20link.%20According%20to%20this%2C%20we%20need%20to%20create%20a%20ODC%20file%20from%20the%20OData%20service%20and%20a%20custom%20data%20provider.%20Will%20check%20more%20on%20it.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26gt%3B%20Alternatively%20you%20may%20load%20data%20to%20data%20model%20through%20PowerPivot%20connector%20and%20return%20result%20back%20using%20linked-back%20tables.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20didn't%20completely%20follow%20on%20how%20would%20it%20help%20to%20refresh%20the%20excel%20data%20when%20it%20changes%20in%20the%20source%20which%20is%20the%20OData%20feed%20in%20my%20case.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

We have configured an excel file with a public OData feed on excel 2016 desktop. The data is loaded into the sheet from Queries & Connections tab. Refresh works fine here. This file is uploaded on sharepoint which is then opened with excel online.

When we do "Refresh All Connections" on excel online we get an error as shown in the screenshot. The technical details of the error are also shared below.

Can you guide us on what could be the issue with refresh?

 

 

Technical details

 

Connection: Query - PersonDetails Error: COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=PersonDetails;extended properties="UEsDBBQAAgAIAICY71BUwQxrpgAAAPgAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAIWPMQ6CMBhGr0K605YKashPGVwlMSEaV1IqNEIxtFju5uCRvIIkiro5fi9veN/jdod0bBvvKnujOp2gAFPkSS26UukqQYM9+WuUctgV4lxU0ptkbeLRlAmqrb3EhDjnsFvgrq8IozQgx2ybi1q2BfrI6r/sK21soYVEHA6vGM7wiuEoipY4DAMgM4ZM6a/CpmJMgfxA2AyNHXrJpfb3OZB5Anm/4E9QSwMEFAACAAgAgJjvUA/K6aukAAAA6QAAABMAHABbQ29udGVudF9UeXBlc10ueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAG2OSw7CMAxErxJ5n7qwQAg1ZQHcgAtEwf2I5qPGReFsLDgSVyBtd4ilZ+Z55vN6V8dkB/GgMfbeKdgUJQhyxt961yqYuJF7ONbV9Rkoihx1UUHHHA6I0XRkdSx8IJedxo9Wcz7HFoM2d90Sbstyh8Y7JseS5x9QV2dq9DSwuKQsr7UZB3Fac3OVAqbEuMj4l7A/eR3C0BvN2cQkbZR2IXEZXn8BUEsDBBQAAgAIAGw071DdiFNqFgEAAO4BAAATABwARm9ybXVsYXMvU2VjdGlvbjEubSCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAABtj81qwzAQhO8Gv4PQyQZjN+2twYdSE+ilKXXIxZii2pvEIEtGWhlKyLtXstSWlOign9nVfLMaOhykILU/V+s4iiN9Ygp68gZKS1EBsoFrUhIOGEfErloa1YFVthVDlm8A+oSeECf9WBQa1Dx0oHPZu6JUx2L/UCydfs/13BU0I8JwnpHmZZw4jCCQuQAlvc/vaJtmnnQV4QPZJ3dYzz83r2yEkl710KwejoKhUbay9NP20jhsGyyfDMp3GOUM/bPkZhR6ZS197XdCt9DKO+eQb5Qc94wbSG7ksSNUcGCGo7dzoezPf7Ha1BsP4g/gzX2WECXBLMhB2B52XxM4+Yz2QgJyuSvopOrDgw8aL6ml/BBuzLn+BlBLAQItABQAAgAIAICY71BUwQxrpgAAAPgAAAASAAAAAAAAAAAAAAAAAAAAAABDb25maWcvUGFja2FnZS54bWxQSwECLQAUAAIACACAmO9QD8rpq6QAAADpAAAAEwAAAAAAAAAAAAAAAADyAAAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLAQItABQAAgAIAGw071DdiFNqFgEAAO4BAAATAAAAAAAAAAAAAAAAAOMBAABGb3JtdWxhcy9TZWN0aW9uMS5tUEsFBgAAAAADAAMAwgAAAEYDAAAAAA==". A connection could not be made to the data source with the DataSourceID of '19273d50-a993-4e06-813c-a6675b1d7d94', Name of 'Query

PersonDetails'. An error occurred while processing table 'PersonDetails'. The current operation was cancelled because another operation in the transaction failed.
Learn more about data refresh

 

 

2 Replies
Highlighted

@amitshah1935 

Power Query refresh is not supported by Excel Online. Perhaps something of this https://docs.microsoft.com/en-us/officeonlineserver/data-authentication-for-excel-online-in-office-o...  works, but I have no such experience.

 

Alternatively you may load data to data model through PowerPivot connector and return result back using linked-back tables.

Highlighted

@Sergei Baklan - Thanks for the reply. 

> Power Query refresh is not supported by Excel Online

Ok. I could not find any official documentation stating that but I could get to this forum post.

 

> Perhaps something of this https://docs.microsoft.com/en-us/officeonlineserver/data-authentication-for-excel-online-in-office-o...  

 

Thanks for sharing the link. According to this, we need to create a ODC file from the OData service and a custom data provider. Will check more on it.

 

> Alternatively you may load data to data model through PowerPivot connector and return result back using linked-back tables.

 

I didn't completely follow on how would it help to refresh the excel data when it changes in the source which is the OData feed in my case.

 

Thanks.