MoreQuery for Mac 2.1

[michelf]

@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

 

[smozgur]

@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:

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!

 

[leratosiame]

I am getting this error when I try to install the add-in. How do I get it to work?

 

[smozgur]

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.

 

[leratosiame]

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!

 

[smozgur]

Got it, thanks!

 

[smozgur]

smozgur updated MoreQuery for Mac with a new update entry:

Bug fixes

Bug fixes:

• Removed the MISSING reference.
• Fixed typos in the code.

Thanks to @Jon Peltier and @leratosiame for reporting the bugs.

Read the rest of this update entry...