smozgur

MoreQuery for Mac 2.1

No permission to download
@smozgur is there a way to use regular PQ connectinos (mainly from different excels) combine, transfor, etc, and then instead of loading as a table in the file, also use this external connection workaround?
one of the limitations we found with PQ for mac is that if the end result is more then 1M rows we can't use it, since excel loads it as a table inside the file
Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
@michelf - actually you can, because the add-in provides connection-only option that PQ interface on Mac currently doesn't.

I just downloaded a 2M rows sample CSV file, and put it into a folder, and then used "From Folder" add-in command to get data from that file, and loaded it very fast into PQ, and when I close & load, the add-in asked me to "where" and I selected connection only option.

This can be also done natively with a few steps. You just need to create a temporary query, then let it load to a worksheet. Once it is loaded as a table in a worksheet as default on Mac, just delete that worksheet and the query will become a connection query only. Now, you can edit the M code of that query to load the actual data source. For example, to load a CSV file:

Power Query:
let 
    Source = Csv.Document(File.Contents("/Users/cuma/Documents/2MillionRows.csv"), [Delimiter = ",", Columns = 14, QuoteStyle = QuoteStyle.None])
in
    Source

Since the query is known as connection-only, it won't be loaded to the worksheet.

Actually, I just realized that I should also implement the From File option that will let connection-only query option in the add-in. I will likely release a new update for this in a day or two. Thanks for the idea! (y)
 
I am getting this error when I try to install the add-in. How do I get it to work?
 

Attachments

  • Screenshot 2023-09-08 at 5.49.05 PM.png
    Screenshot 2023-09-08 at 5.49.05 PM.png
    136.8 KB · Views: 9
I am getting this error when I try to install the add-in. How do I get it to work?
Yes. Actually it still works but there is a missing library.

Without opening a new workbook but just add-in, Please go to VBA, Tools and References, then find the one that says MISSING in the list and remove the checkmark. Then save the add-in (Or type
Thisworkbook.Save
in the immediate window and hit enter).

It should not warn again.
 
Yes. Actually it still works but there is a missing library.

Without opening a new workbook but just add-in, Please go to VBA, Tools and References, then find the one that says MISSING in the list and remove the checkmark. Then save the add-in (Or type
Thisworkbook.Save
in the immediate window and hit enter).

It should not warn again.
Got it, thanks!
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top