Use VBA to Define Dymanic MDX Parameters in Power Query

Question

I have a power query connection to a Tabular data model. I have some MDX that runs and would like to pass a value in the WHERE statement to the MDX from the value of a cell. Any ideas on how to do this? I set the Power Query procedure up before to grab the value of the cell and use that as a filter by pulling it in through a second procedure, but that method does not work when I use that named range in the MDX.

Essentially I have a "... WHERE ( [Table].[ID].&[123] )" and I want to "123" part to be replaced with the value in cell A1 each time I refresh the process.

Any ideas?

Alejandro Lopez-Lago - MSFT · Accepted Answer · 2016-10-11 19:31:36Z

2

You can combine text values in the Power Query language using the & operator, like "[Table].[ID].&[" & CellValue & "])". If you do use this method you may have to turn off privacy levels, and you should make sure that you either trust the values coming from the cells or properly encode those values by doubling the ] character (you can use Text.Replace(CellValue, "]", "]]") to do this).

answered Oct 11, 2016 at 19:31

Alejandro Lopez-Lago - MSFT

4,18422 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tylerpol Over a year ago

Thanks! So if the cell I want to use as the value is on "Sheet1" and cell B2, how would that look?

Alejandro Lopez-Lago - MSFT Over a year ago

Depends on the query name. You can use "From Table" in the ribbon to get the value of the cell into Power Query. That creates a query (let's call it Query1) with the value in a table cell You can then drill down into the cell to get its value, and then reference Query1 whenever you want to use that value.

Collectives™ on Stack Overflow

Use VBA to Define Dymanic MDX Parameters in Power Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related