Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
@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:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi edhans,
I will take your suggestion and move it to the Excel Power Query Forums.
Best,
mb
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |