I have 2 copies of the same SharePoint online site: different name, different site collection, but with the same lists, libraries..., I want to reuse the same Excel for the two sites. I can’t change connection strings because I have calculated columns via PowerPivot, is there a way to change connections without deleting PowerPivot calculated columns?
You can do this by doing some power query trickery. The caveat is the workbooks will be slightly different because of the PQ query difference. In PQ edit your queries, there should be an 'advanced editor' option in the query editing window. You can manually insert a step like: Url="url.to.my.server.com/odatafeed",
Then refer is that instead of the literal url in the line that starts with Source=....
The PQ change, assuming the schema of the odatafeeds is the same, will not change the power pivot data model so you can change the query.
