Query as record of subqueries - how to load subqueries in ExcelTable or DataModel

7ohm7

New Member
Joined
Oct 31, 2019
Messages
10
Hello everyone,

it proved to be the most useful to divide the query into small parts that can be easily tested and reused. So I'm trying always write the query as a record of subqueries like it is in #shared, for example:

Power Query:
Data =
[
Cleaned = let
   source = csv..
   renamedColumns = ..,
   replacedErrors = ..,
   in replacedErrors,
Grouped = let
   source = Cleaned,
   grouped = ..,
   in grouped
]

This way I can greatly simplify the queries system and partially obtain the benefits of encapsulation like in OO programming. Reference from other queries is easy e.g. ref to Data[Cleaned] or to Data[Grouped]. But there remains an obstacle how to load these parts into Excel as tables, or in Data Model.

I tried to edit connection properties. I understand the parameter 'ConnectionString' is an parameter array for VBA - I can use it to refer to a query as Location, but not in the PQ language (I can't write Location = Data[Cleaned], because 'Data[Cleaned]' is not the expression that VBA understand). I can't really find what the rules are for this parameter.
1601286552719.png

Then I tried to change the parameter 'CommandText' to SELECT [Value] FROM [Data] WHERE [Name] = 'Cleaned', which VBA understood, but the table in the excel table did not load, only the reference to unviewable object - I see only that there is a table behind such a query, but it is not loaded to excel.
1601286494487.png


The question may be simple - how to incorporate the custom PQ script into VBA code for Connections.Add2 or any other code that reads data into a table (listobject). I do not want to create separate queries for each connection.

Thank you.

Marek
 

Attachments

  • 1601286545588.png
    1601286545588.png
    6.2 KB · Views: 6
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Come on, I wouldn't take that conclusion about support that much. If I create a new query and connect on it, I can easily use a custom PQ script as a parameter for Query.Formula (VBA Macro to Create Power Query Connections for All Excel Tables - Excel Campus) . All I want to do is create a similar model without having to create a query for each connection.
  1. Power Query doesn't support vba
  2. ConnectionString is SQL, not vba


I need the goal in VBA, not in PQL if it wasn't clear. I need PQL embedded in VBA (or PQL embedded in SQL embedded in VBA), not the other way around.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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