Replace Data Connection with a PowerQuery

[Jaroslaw Szczygielski]

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

[#2]

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?

[#3]

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.

[#4 · Accepted solution]

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.

[#5]

That's actually quite smart and easy fix.

Unless you have Pivot Tables or Graphs connecting to that table it will work.

Thanks

[#6]

You’re welcome

[#7]

When I Do another find and replace ^= to =, I get a formula error pop up