Cube.AddAndExpandDimensionColumn() Question

Power Query
1

I don’t know if this is the right category for this question, but I hope someone can help me with this. I have created a query to pull in data from a Analysis Services cube. When I look at the M code in the advanced editor, it includes the following lines:

    #"Added Items" = Cube.Transform(#"EmployeeData",
    {
        {Cube.AddAndExpandDimensionColumn, "[Date]", {"[Date].[Month].[Month]"}, {"Date"}},
        {Cube.AddAndExpandDimensionColumn, "[EmployeeInfo]", {"[EmployeeInfo].[Area].[Area]", "[Employee History].[Region].[Region]"}, {"Area", "Region"}},
        {Cube.AddMeasureColumn, "Headcount", "[Measures].[Headcount]"}
    }),

I would like to be able to apply some filtering in these lines in order to limit the data the is returned by the query. For example, I’d like to restrict the date to Jan 2016 or later. I’d also like to restrict the Region value to only those records are not equal to 12.

I have searched high and low in the internet, and I have found a couple of instances of people asking that question, but I’ve not found any answers. Does anyone know if this is possible? Or if it is not possible to filter within the Cube.AddAndExpandDimensionColumn() function, is there another way to do some filtering at the data source so that it doesn’t return hundreds of rows that I will immediately have to filter out in Power Query?

Thanks!

2

Hi @DaveC,

No experience with Analysis Services cube but did find an article by Chris Webb that I hope you’ll find useful. Although I did see a Table.SelectRows don’t know if it folds back to the source…

3

Thanks, @Melissa! After reading that article, it appears that there isn’t a way to do filtering within that function. I guess my best bet is to hope for query folding. It’s working the way I currently have it set up. I was just trying to be as efficient as possible with my query.