Replace Data Connection with a PowerQuery

Jaroslaw Szczygielski
Copper Contributor
Apr 10, 2018
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.
- Wyn Hopkins
  MVP
  Apr 10, 2018
  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.
  - mcom40
    Copper Contributor
    Apr 13, 2022
    When I Do another find and replace ^= to =, I get a formula error pop up

Forum Discussion

Replace Data Connection with a PowerQuery

Resources