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

7ohm7

New Member
Joined
Oct 31, 2019
Messages
9
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: 1
Last edited:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,454
  1. Power Query doesn't support vba
  2. ConnectionString is SQL, not vba
 

7ohm7

New Member
Joined
Oct 31, 2019
Messages
9
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:

Watch MrExcel Video

Forum statistics

Threads
1,119,227
Messages
5,576,838
Members
412,749
Latest member
BlakeVanderMeer
Top