Power Query - Load Data without saving Queries or Connections, and NOT as a Table in Excel

[mbabla]

Hi,

 Thanks for reading my post, and I appreciate any helpful advice you can offer.

In using Power Query in Excel to get Data from Web - the default mode seems to be to Load the Result as a Table, and with Connections, and the saving of Queries. I need to do this just one-time, so there is no need to save the Query or Connection, and no need to Paste it as a Table. Also, I am doing many Power Query requests per row, and there are hundreds of rows, so I do not want the data pasted as a Table either, but rather as text. This is important as with hundreds of these tables, and with Connections and Queries in the sheet, even my quite Fast PC w/ 64-bit excel and 32GB RAM gets very slow and almost unusable after a few hundred requests that are all saved as Queries, Connections, and are pasted into the sheet as individual tables.

Is there a way to do that -

1> Have Power Query paste the result as text in the cells, NOT grouped into a table

2> disable power query from saving the query

3> disable power query from saving the connection

Best,

mb 

[edhans]

---

@mbabla wrote:

Hi,

 Thanks for reading my post, and I appreciate any helpful advice you can offer.

 Also, I am doing many Power Query requests per row, and there are hundreds of rows, so I do not want the data pasted as a Table either, but rather as text. This is important as with hundreds of these tables, and with Connections and Queries in the sheet, even my quite Fast PC w/ 64-bit excel and 32GB RAM gets very slow and almost unusable after a few hundred requests that are all saved as Queries, Connections, and are pasted into the sheet as individual tables.

Is there a way to do that -

1> Have Power Query paste the result as text in the cells, NOT grouped into a table

2> disable power query from saving the query

3> disable power query from saving the connection

---

You'd need to post some sample data of how you are using it. It doesn't seem you are using it correctly by using a unique query for every row of data. But to directly answer some of your questions:

• You cannot have Power Query paste the data as text. it connects via a table, or you can put into the Excel data model for Power Pivot. This is what the Power BI Desktop app does when it loads, for use by the data model for DAX measures and visuals.
• You can have it just do the query but not load. That means Excel/Power BI Desktop can process and show you the query results in the Power QUery editor, but not do anything with it.
• You can technically not save the connection by closing the window and discarding changes, but again, I don't understand how that would be a normal workflow and would need to understand more about what you are doing.

[mbabla]

Hi edhans,

Thanks for your thoughtful responses to my questions.

Your response, "you cannot have Power Query paste the data as text." answers the crux of my question. However, is there a setting to not have it save the Query and/or connection, but just download the data one-time, else in my current workflow, I have to manually remove them every time.

Best,

mb

[edhans · Accepted solution]

No, as when it creates a table, it is a live refresh.

You can disable the automatic refresh of the table. Go to the Data tab when the table is selected, click the dropdown to REFRESH ALL, click connection properties, then uncheck "refresh this connection on refresh all."

You'll have to right-click on that table to refresh going forward.

I think for further discussion, you should move this to the Excel Power Query forums as there is nothing analagous to how Excel works with flat tables in Power BI. It is all about data models here.

[mbabla]

 Hi edhans,

I will take your suggestion and move it to the Excel Power Query Forums.

Best,

mb