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.
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.- 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
