Forum Discussion
Replace Data Connection with a PowerQuery
- 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.
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.
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.
- When I Do another find and replace ^= to =, I get a formula error pop up
That's actually quite smart and easy fix.
Unless you have Pivot Tables or Graphs connecting to that table it will work.
Thanks
- You’re welcome
