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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,376
  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,118,134
Messages
5,570,364
Members
412,321
Latest member
Yusuf_A
Top