Forum Discussion
Replace Data Connection with a PowerQuery
Hi,
I have searched far and wide for this. I have a lot of workbooks that use Data Connections. I would like to replace them with PowerQueries as they are a bit more flexible and easier to maintain whenever data source changes.
I managed to find a way to do that without breaking formula references etc. But now I'm left with both a PowerQuery (as "Connection Only") and a Data Connection that loads data from PowerQuery (example attached, you need to go to Data->Existing Connections to see "Scorecard NPS" connection as for some reason it disappears from Connection list on the side panel).
Is there a better way to do it?
Regards, Jarek
- Hi
What I would try is this:
Load your data into a table with Power Query and name it Table1PQ (ensure it has exactly the same column names as your current Table1)
Do a find / replace on your entire workbook for = and replace with ^=
That should convert your fomulas to text
Delete your original Table1 and the connection
Rename your Table1PQ to Table1
Do another find and replace ^= to =
All your formula should work again.
6 Replies
- Hi Jaroslaw,
So in your file do you just have Pivot Tables feeding off Scorecard NPS, or other things as well?
What type of formula was breaking?Hi,
The spreadsheet is just example.
Long story short:
I am looking for a good way to replace data connection with a PowerQuery.
Here's a long story:
"Scorecard NPS" used to load data to a table called "Table1".
There are a lot of measures (formulas, charts etc) that were using Table1 as a data source.
"Scorecard NPS" data connection takes a long time to refresh as it is using Access Database from virtual drive (over the network). Since this data is also available in SQL Server, I wrote a query and want to make the "Scorecard NPS" feed data from SQL Server, not Access (which now takes 10 second, not 15 minutes).
I also want to replace data connection with PowerQuery as it's easier to edit the query this way.
I tried different ways to replace data connection with PowerQuery but they either resulted in "REF" errors or converting formulas like "sum(Table1[NPScore])" to "sum(A1:A10)".
The spreadsheet I attached was my best attempt to replace it without those issues, but now I ended up with 1 connection and 1 PowerQuery for some reason. Ideally I would like to remove the connection and only leave PowerQuery but I cannot seem to do that.
- Hi
What I would try is this:
Load your data into a table with Power Query and name it Table1PQ (ensure it has exactly the same column names as your current Table1)
Do a find / replace on your entire workbook for = and replace with ^=
That should convert your fomulas to text
Delete your original Table1 and the connection
Rename your Table1PQ to Table1
Do another find and replace ^= to =
All your formula should work again.
