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

[7ohm7]

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:

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.

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.

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

 

[sandy666]

1. Power Query doesn't support vba
2. ConnectionString is SQL, not vba

 

[7ohm7]

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.

 

[sandy666]

sure, good luck