Forum Discussion
Refresh Pivot Table while keeping the external source data open
Hi,
is there a way to refresh a power query + Power pivot file that has the data in the same file ( one sheet as DB uploaded to Power Query and then to Power pivot)?
i get an error when i tries to refresh from Teams ( or from excel on line for that matter) , no problem refreshing when opening in excel desktop)
This is the Technical details of the error i am getting:
"Connection: Query - DB
Error: COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=DB;extended properties="UEsDBBQAAgAIAKxMYVSGY5w/pwAAAPgAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAIWPvQ6CMBhFX4V0p38qUfNRBicTSExMjCspFRqhGFos7+bgI/kKkijq5nhPznDu43aHZGjq4Ko6q1sTI4YpCpSRbaFNGaPencIlSgTscnnOSxWMsrHrwRYxqpy7rAnx3mM/w21XEk4pI8cs3ctKNTn6yPq/HGpjXW6kQgIOrxjBccTwgq04nkcMyIQh0+ar8LEYUyA/EDZ97fpOiUqF2xTINIG8X4gnUEsDBBQAAgAIAKxMYVQPyumrpAAAAOkAAAATABwAW0NvbnRlbnRfVHlwZXNdLnhtbCCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAABtjksOwjAMRK8SeZ+6sEAINWUB3IALRMH9iOajxkXhbCw4ElcgbXeIpWfmeebzelfHZAfxoDH23inYFCUIcsbfetcqmLiRezjW1fUZKIocdVFBxxwOiNF0ZHUsfCCXncaPVnM+xxaDNnfdEm7LcofGOybHkucfUFdnavQ0sLikLK+1GQdxWnNzlQKmxLjI+JewP3kdwtAbzdnEJG2UdiFxGV5/AVBLAwQUAAIACADIA2FUfSVrMXkEAAC3FQAAEwAcAEZvcm11bGFzL1NlY3Rpb24xLm0gohgAKKAUAAAAAAAAAAAAAAAAAAAAAAAAAAAA5VhbayM3FH4P5D+I8YsDU0Og9KVNwet4L+0m3tretmDMIM/ItuqxFCRNmmD8H3ahu0soLG0pJdk/NH+nR3O/yImZti+NQ5Lx+c5dR0dzJImrKGdoFP8//vLw4PBALrEgHnrB5gJLJQJXBYKgE+QTdXiA4DPigXA1pX/lEr/TC4QgTP3AxWrG+ap9tJmc4zU5scoarOl20uNMAev08ICyoq6i3TPKKG5qMhPe19rgVf/HJoa03L42ng+dU3LZxEosua+d1yNnhH0iHcw8Zyyw1yiFdS372j99YrQ3xjOfdHp8PaOMtDd1/Xal1uy8BuxogewkhcA4ds45cwbPRmdOF2r2kqrr7ZEdW2xZT6mviHZlyH+WVmZ7RHwocE1rx17ZiGB3idqTV4L/BJCcoq++Rizw/aNcWW+J2QJ0ja8vSK4LPGZyzsW6x/1gzTQo21XL9mZjDQRdUIZ91GeQGqyIZSMF3MiD562NNlb/6gJsgwwBDs/IIVUHPQeXJUoxFqxnREToGQaP0UhBpmQKK3KlIjAmoyHBkrMaCquxhuWsi71An+nVgPiIV5cCEApBBhBrHe0C2nVdHjClM1XDh4APibwABdSEj7FcoVMiXTPSsnSdqC8+7+ic5/RnggcXNZF0YWsAJA0vdOwPeNOVki4YUhxlEnUeKFccrYBQ5iXOimAHT6F2z4jQ1fZdQAQlheI9JxKy/Q2nrF2pSbsQ5tZupV/QSypVBaxgWtu3lHmdl2SuBgFUbu4HVCXsTLBREsncieHoOd4B7ZrrNqqYi9JOWapRMzwJqO9RtkAzn7uroshoySFTuhdZ2+aSeTxDwoDiodjbQmJjICG3dwWut7Jpo+bEfkosrmbX05p6gVR8nZsEapa0ql+RStcPoGsHDOveRqDdIR+apgajhkXnKG9ZcMCOEXRDpLshSruhhSAINMm3xvRE97WY2kq3UpGY7buEqJaE3ecLIr4kcbM0N95jY+et5CRrwTsNTUFL3JLzQ6dmqXj+hG/D38Ib+PnkhJ/CO/iFxyZHoFFR8RRsckYkJ8/m3t5UKvUyWKt7Y8MrboIiy7aUxJLbpRR+hFDfh3+E78LbRpkryD+WhP0Fwd5AwbxrWGwlDY8jaX/qDQXhfnDCXyD+u/BN+NbRz2+iLNyFN40y+bDaR5fej+Gv8OVWl5YDf34PP4S3zj9NrlHpo0ht5SVrn3nnwcTZ++wHu9Jp7FKrts1nn56XioHVR7YhZqsm1aDl/r0Ffyr42jTgjLmBGJkuL11ivRsoPiRrfkmStyx5DB7EWBah/qjMMW35e+wHpPZujSanZI5h0olV6bBBKgn8KNaV5lV/0ndKbT59p1R2VgwRYTDXujV5E7uvwSQUQVwuvOSLD8W1Lb73GEIr3dUYxuJGNzYGPf/dvsbsescYV0JaRmpxUEyBB2a7lO2e0S5l2TGrZhrMo24KG+fkFDRN2JlgZSxP6feMkTWW6nVD5vOuYTVl2HkdkTHUbyMKma9dRaSY8SZCg3v2XJgHGpXy//dA+htQSwECLQAUAAIACACsTGFUhmOcP6cAAAD4AAAAEgAAAAAAAAAAAAAAAAAAAAAAQ29uZmlnL1BhY2thZ2UueG1sUEsBAi0AFAACAAgArExhVA/K6aukAAAA6QAAABMAAAAAAAAAAAAAAAAA8wAAAFtDb250ZW50X1R5cGVzXS54bWxQSwECLQAUAAIACADIA2FUfSVrMXkEAAC3FQAAEwAAAAAAAAAAAAAAAADkAQAARm9ybXVsYXMvU2VjdGlvbjEubVBLBQYAAAAAAwADAMIAAACqBgAAAAA=".
A connection could not be made to the data source with the DataSourceID of 'db5ba929-027e-4107-8a85-43b3c0bb8676', Name of '<pii>Query - DB</pii>'.
An error occurred while processing table '<pii>DB</pii>'.
COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=Rank.
A connection could not be made to the data source with the DataSourceID of 'b21404d5-dcd0-4289-8d0b-e3fd088e3a32', Name of '<pii>Query - Rank</pii>'.
An error occurred while processing table '<pii>Rank</pii>'.
The current operation was cancelled because another operation in the transaction failed.
Connection: Query - Rank
Error: COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=DB;extended properties="UEsDBBQAAgAIAKxMYVSGY5w/pwAAAPgAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAIWPvQ6CMBhFX4V0p38qUfNRBicTSExMjCspFRqhGFos7+bgI/kKkijq5nhPznDu43aHZGjq4Ko6q1sTI4YpCpSRbaFNGaPencIlSgTscnnOSxWMsrHrwRYxqpy7rAnx3mM/w21XEk4pI8cs3ctKNTn6yPq/HGpjXW6kQgIOrxjBccTwgq04nkcMyIQh0+ar8LEYUyA/EDZ97fpOiUqF2xTINIG8X4gnUEsDBBQAAgAIAKxMYVQPyumrpAAAAOkAAAATABwAW0NvbnRlbnRfVHlwZXNdLnhtbCCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAABtjksOwjAMRK8SeZ+6sEAINWUB3IALRMH9iOajxkXhbCw4ElcgbXeIpWfmeebzelfHZAfxoDH23inYFCUIcsbfetcqmLiRezjW1fUZKIocdVFBxxwOiNF0ZHUsfCCXncaPVnM+xxaDNnfdEm7LcofGOybHkucfUFdnavQ0sLikLK+1GQdxWnNzlQKmxLjI+JewP3kdwtAbzdnEJG2UdiFxGV5/AVBLAwQUAAIACADIA2FUfSVrMXkEAAC3FQAAEwAcAEZvcm11bGFzL1NlY3Rpb24xLm0gohgAKKAUAAAAAAAAAAAAAAAAAAAAAAAAAAAA5VhbayM3FH4P5D+I8YsDU0Og9KVNwet4L+0m3tretmDMIM/ItuqxFCRNmmD8H3ahu0soLG0pJdk/NH+nR3O/yImZti+NQ5Lx+c5dR0dzJImrKGdoFP8//vLw4PBALrEgHnrB5gJLJQJXBYKgE+QTdXiA4DPigXA1pX/lEr/TC4QgTP3AxWrG+ap9tJmc4zU5scoarOl20uNMAev08ICyoq6i3TPKKG5qMhPe19rgVf/HJoa03L42ng+dU3LZxEosua+d1yNnhH0iHcw8Zyyw1yiFdS372j99YrQ3xjOfdHp8PaOMtDd1/Xal1uy8BuxogewkhcA4ds45cwbPRmdOF2r2kqrr7ZEdW2xZT6mviHZlyH+WVmZ7RHwocE1rx17ZiGB3idqTV4L/BJCcoq++Rizw/aNcWW+J2QJ0ja8vSK4LPGZyzsW6x/1gzTQo21XL9mZjDQRdUIZ91GeQGqyIZSMF3MiD562NNlb/6gJsgwwBDs/IIVUHPQeXJUoxFqxnREToGQaP0UhBpmQKK3KlIjAmoyHBkrMaCquxhuWsi71An+nVgPiIV5cCEApBBhBrHe0C2nVdHjClM1XDh4APibwABdSEj7FcoVMiXTPSsnSdqC8+7+ic5/RnggcXNZF0YWsAJA0vdOwPeNOVki4YUhxlEnUeKFccrYBQ5iXOimAHT6F2z4jQ1fZdQAQlheI9JxKy/Q2nrF2pSbsQ5tZupV/QSypVBaxgWtu3lHmdl2SuBgFUbu4HVCXsTLBREsncieHoOd4B7ZrrNqqYi9JOWapRMzwJqO9RtkAzn7uroshoySFTuhdZ2+aSeTxDwoDiodjbQmJjICG3dwWut7Jpo+bEfkosrmbX05p6gVR8nZsEapa0ql+RStcPoGsHDOveRqDdIR+apgajhkXnKG9ZcMCOEXRDpLshSruhhSAINMm3xvRE97WY2kq3UpGY7buEqJaE3ecLIr4kcbM0N95jY+et5CRrwTsNTUFL3JLzQ6dmqXj+hG/D38Ib+PnkhJ/CO/iFxyZHoFFR8RRsckYkJ8/m3t5UKvUyWKt7Y8MrboIiy7aUxJLbpRR+hFDfh3+E78LbRpkryD+WhP0Fwd5AwbxrWGwlDY8jaX/qDQXhfnDCXyD+u/BN+NbRz2+iLNyFN40y+bDaR5fej+Gv8OVWl5YDf34PP4S3zj9NrlHpo0ht5SVrn3nnwcTZ++wHu9Jp7FKrts1nn56XioHVR7YhZqsm1aDl/r0Ffyr42jTgjLmBGJkuL11ivRsoPiRrfkmStyx5DB7EWBah/qjMMW35e+wHpPZujSanZI5h0olV6bBBKgn8KNaV5lV/0ndKbT59p1R2VgwRYTDXujV5E7uvwSQUQVwuvOSLD8W1Lb73GEIr3dUYxuJGNzYGPf/dvsbsescYV0JaRmpxUEyBB2a7lO2e0S5l2TGrZhrMo24KG+fkFDRN2JlgZSxP6feMkTWW6nVD5vOuYTVl2HkdkTHUbyMKma9dRaSY8SZCg3v2XJgHGpXy//dA+htQSwECLQAUAAIACACsTGFUhmOcP6cAAAD4AAAAEgAAAAAAAAAAAAAAAAAAAAAAQ29uZmlnL1BhY2thZ2UueG1sUEsBAi0AFAACAAgArExhVA/K6aukAAAA6QAAABMAAAAAAAAAAAAAAAAA8wAAAFtDb250ZW50X1R5cGVzXS54bWxQSwECLQAUAAIACADIA2FUfSVrMXkEAAC3FQAAEwAAAAAAAAAAAAAAAADkAQAARm9ybXVsYXMvU2VjdGlvbjEubVBLBQYAAAAAAwADAMIAAACqBgAAAAA=".
A connection could not be made to the data source with the DataSourceID of 'db5ba929-027e-4107-8a85-43b3c0bb8676', Name of '<pii>Query - DB</pii>'.
An error occurred while processing table '<pii>DB</pii>'.
COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=Rank.
A connection could not be made to the data source with the DataSourceID of 'b21404d5-dcd0-4289-8d0b-e3fd088e3a32', Name of '<pii>Query - Rank</pii>'.
An error occurred while processing table '<pii>Rank</pii>'.
The current operation was cancelled because another operation in the transaction failed.
https://euc-excel.officeapps.live.com/x/_layouts/xlviewerinternal.aspx?unified=1&ui=en-us&rs=he-IL&wopisrc=https%3A%2F%2Famstechltd.sharepoint.com%2Fsites%2FOGSM%2F_vti_bin%2Fwopi.ashx%2Ffiles%2F37d2955751a0402ca23f7ea8653045ed&wdenableroaming=1&mscc=1&hid=db2b9ad4-4d89-f13a-bdd6-94a84e1e12b9-1545&uiembed=1&uih=teams&uihit=files&hhdr=1&dchat=1&sc=%7B%22pmo%22%3A%22https%3A%2F%2Fteams.microsoft.com%22%2C%22pmshare%22%3Atrue%2C%22surl%22%3A%22%22%2C%22curl%22%3A%22%22%2C%22vurl%22%3A%22%22%2C%22eurl%22%3A%22https%3A%2F%2Fteams.microsoft.com%2Ffiles%2Fapps%2Fcom.microsoft.teams.files%2Ffiles%2F454822843%2Fopen%3Fagent%3Dpostmessage%26objectUrl%3Dhttps%253A%252F%252Famstechltd.sharepoint.com%252Fsites%252FOGSM%252FShared%2520Documents%252FGeneral%252FOGSM%25202022%252FOGSM%2520Template.xlsx%26fileId%3D37d29557-51a0-402c-a23f-7ea8653045ed%26fileType%3Dxlsx%26ctx%3Dfiles%26scenarioId%3D1545%26locale%3Den-us%26theme%3Ddefault%26version%3D21120606800%26setting%3Dring.id%3Ageneral%26setting%3DcreatedTime%3A1646123350978%22%7D&wdorigin=TEAMS-ELECTRON.teams.files&wdhostclicktime=1646123350917&jsapi=1&jsapiver=v1&newsession=1&corrid=3613a999-98e3-462c-b0f3-7f86ad726ea8&usid=3613a999-98e3-462c-b0f3-7f86ad726ea8&sftc=1&core=1&hvt=1&sams=1&accloop=1&sdr=6&scnd=1&sat=1&xlel=1&hodflp=1&wdredirectionreason=Force_SingleStepBoot&rct=Medium&ctp=LeastProtected#
https://euc-excel.officeapps.live.com/x/_layouts/xlviewerinternal.aspx?unified=1&ui=en-us&rs=he-IL&wopisrc=https%3A%2F%2Famstechltd.sharepoint.com%2Fsites%2FOGSM%2F_vti_bin%2Fwopi.ashx%2Ffiles%2F37d2955751a0402ca23f7ea8653045ed&wdenableroaming=1&mscc=1&hid=db2b9ad4-4d89-f13a-bdd6-94a84e1e12b9-1545&uiembed=1&uih=teams&uihit=files&hhdr=1&dchat=1&sc=%7B%22pmo%22%3A%22https%3A%2F%2Fteams.microsoft.com%22%2C%22pmshare%22%3Atrue%2C%22surl%22%3A%22%22%2C%22curl%22%3A%22%22%2C%22vurl%22%3A%22%22%2C%22eurl%22%3A%22https%3A%2F%2Fteams.microsoft.com%2Ffiles%2Fapps%2Fcom.microsoft.teams.files%2Ffiles%2F454822843%2Fopen%3Fagent%3Dpostmessage%26objectUrl%3Dhttps%253A%252F%252Famstechltd.sharepoint.com%252Fsites%252FOGSM%252FShared%2520Documents%252FGeneral%252FOGSM%25202022%252FOGSM%2520Template.xlsx%26fileId%3D37d29557-51a0-402c-a23f-7ea8653045ed%26fileType%3Dxlsx%26ctx%3Dfiles%26scenarioId%3D1545%26locale%3Den-us%26theme%3Ddefault%26version%3D21120606800%26setting%3Dring.id%3Ageneral%26setting%3DcreatedTime%3A1646123350978%22%7D&wdorigin=TEAMS-ELECTRON.teams.files&wdhostclicktime=1646123350917&jsapi=1&jsapiver=v1&newsession=1&corrid=3613a999-98e3-462c-b0f3-7f86ad726ea8&usid=3613a999-98e3-462c-b0f3-7f86ad726ea8&sftc=1&core=1&hvt=1&sams=1&accloop=1&sdr=6&scnd=1&sat=1&xlel=1&hodflp=1&wdredirectionreason=Force_SingleStepBoot&rct=Medium&ctp=LeastProtected#
Hi Eitanlif
currently it is not possible to refresh Power Query queries in Excel for the Web (this is what you usually use when you start Excel in Teams). You can just see and work with the query results, but you cannot refresh or modify any queries.
For this you need to open your file in the desktop version of Excel.
Many Thanks, I suspected as much, just hoped i was wrong.
do you now if it planned to change in the near future?
there is no possibility to refresh when someone else in the file as well, right?