VBA and PowerQuery: Get value from query without creating table on worksheet

[PierreBenko]

Hi all,

I have an Excel file with an underlying pretty complicated PowerQuery query.
On the Excel sheet, I have a table from a query, which displays only the needed information, without any redundancy (meaning, that you cannot calculate one column from another).
I want to have a button, which sets the values of a newly added column (which doesn't come from the query) according to values from another query.
Point is, that I don't want to have the whole query table on any sheet.
How can I do this in VBA? Is there a way to directly access the queries? Cube formulas?

For clarification:
Queries:
Base_Query (with all data):
Columns: Year, Country, Market Size, Revenue, Market Share
Table_Query (derived from Base_Query, only the columns to be displayed)
Columns: Year, Country, Market Share in 2021

Table on the Worksheet:
Columns: [Year, Country, Market Share in 2021], Market Share in current year

Button: Set defaults
What it should do:
1) get the [Revenue] from 2021 from Base_Query (for given Country)
2) get the [Market size] from 2021 from Base_Query (for given Country)
3) divide Revenue from 2021 with Market size from 2021
4) write the result to the [Market share in current year] column for given Country

So again, the question is, how can I get the values from Base_Query to a variable in VBA. (Step 2: how can I get several values according to filter criteria e.g. year and country)

Thank you in advance.

Peter

 

[RoryA]

Is there a reason you don't just add that to the query that is the source for the table?

 

[PierreBenko]

Is there a reason you don't just add that to the query that is the source for the table?

Yes. The output table should contain only the data that is asked, and nothing else (as the user is not particularly fluent in Excel). Besides I try to keep the file size low.

 

[RoryA]

I don’t follow. You want the answer in the table so why not do it as part of the query that creates the table?

 

[PierreBenko]

I don’t follow. You want the answer in the table so why not do it as part of the query that creates the table?

The table has a column that the user can modify. What I want is to have a button, that fills this column with values calculated from the data in the base query.

 

[PierreBenko]

Hi,

after tons of googling without result, I was able to find a solution.

It is based on Cube formulas, but since they are not part of the WorksheetFunction Methods they have to be called with Application.Evaluate.

The method looks roughly like this (this is attached to a button):

'Create vars to store MarketSize and Revenue21
Dim MarketSize, Rev21, Share_default As Double

'Parameters for CUBE functions
Dim param_MS, param_Rev21 As String 'Parameters for CUBEVALUE
Dim t_sales As ListObject 'var for table

'Set table var
Set t_sales = ActiveSheet.ListObjects("Sales")

'Get t_sales dimensions
With t_sales.DataBodyRange
    nRows = .Rows.Count
    nColumns = .Columns.Count
End With

'Loop through table
For R = 1 To nRows
    'Set filter criteria for query (Year, Country)
    t_Year = t_sales.DataBodyRange(R, t_sales.ListColumns("Year").Index).Value2
    t_Country = t_sales.DataBodyRange(R, t_sales.ListColumns("Country").Index).Value2

    'Query MarketSize and Revenue21 line by line
    'Build param string for cubevalue
    param_MS = """ThisWorkbookDataModel"", ""[Measures].[Sum of Market]"", ""[Sales_Base].[Year].[" + CStr(t_Year) + "]"", ""[Sales_Base].[Country].[" + t_Country + "]"""
    param_Rev21 = """ThisWorkbookDataModel"", ""[Measures].[Sum of Revenue]"", ""[Sales_Base].[Year].[2021]"", ""[Sales_Base].[Country].[" + t_Country + "]"""

    'Getting values for market size and revenue from 2021
    MarketSize = Application.Evaluate("=CUBEVALUE(" + param_MS + ")") * 1000000
    Rev21 = Application.Evaluate("=CUBEVALUE(" + param_Rev21 + ")")
    
    'Calculating output market share
    Share_default = Rev21 / MarketSize
    
    'Writing result to table
    t_sales.DataBodyRange(R, t_sales.ListColumns("MS").Index).Value2 = Share_default
Next

It gets the job done, although I have a hunch, that there are more elegant solutions, as this generates a ton of queries (every cubevalue is a separate query).

This solves my problem, still, I am grateful for any constructive criticism and additional info or alternative solution.

Cheers,
Peter