excelpowerquerymdxazure-analysis-services

Passing a parameter into an MDX Query using Power Query in Excel

I am using a parameter to build an MDX query to analysis services in a SQL Server Cube/Tabular Model. This is an example

AnalysisServices.Database("server_name", db_param, [Query = "SELECT * FROM table_a WHERE db = 'db_name'])

My parameter is 'db_param', and it works fine to build the connection to the right database inside the server, but I can't manage to pass it to the MDX query. I would essentially like to replace 'db_name' with db_param such that it calls the parameter.

Any ideas? I tried single brackets, double brackets, inside quotation marks, not inside quotation marks. And I can't seem to create a chain either. (e.g. "SELECT * FROM table_a WHERE db = " + db_param + "rest of the query"]

Any ideas?

Solution

  • In general, you need to use & not + to combine.

    let aa = "ddd", 
bb= "ccc", 
statement = "SELECT * FROM "&aa& " WHERE db='"&bb&"'", 
Source =[Query = statement]
in Source