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?

Answer 1

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).