Filtering a list with another list then collapsing columns back into SSAS

[gp93]

Hi all,

Not sure if possible. I want to filter a list of "account numbers" from an SSAS query using another list I have. Here is what i have so far:

let
    Source = AnalysisServices.Databases("bi-livecubes", [TypedMeasureColumns=true, Implementation="2.0"]),
    Cubes = Source{[Name="Cubes"]}[Data],
    LoanServicing1 = Cubes{[Id="LoanServicing"]}[Data],
    LoanServicing2 = LoanServicing1{[Id="LoanServicing"]}[Data],
    #"Added Items" = Cube.Transform(LoanServicing2,
        {
            {Cube.AddAndExpandDimensionColumn, "[Account]", {"[Account].[Account Number].[Account Number]"}, {"Account.Account Number"}}
        }),
    
    #"Filtered Rows" = Table.SelectRows(#"Added Items", each List.ContainsAny(Table2,{[Account.Account Number]})),
    #"Collapsed and Removed Columns" = Cube.CollapseAndRemoveColumns(#"Filtered Rows", {"Account.Account Number"})
in
    #"Collapsed and Removed Columns"

Get an error saying "The value is not a cube". Is there a way of doing this? if i was to filter to a single value using text.contains it would allow me to collapse columns?

[Fowmy · Accepted solution]

@gp93 

I am not familiar with CUBEs in PQ but 
Filter a column based on another list will go like:

= Table.SelectRows(Sales_Customers, each List.Contains(Query1, [CustomerCategoryID]))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[gp93]

Hi Fowmy,

Thanks for getting back to me, you have solved it! Very strange - List.ContainsAny was working to filter the list in the previous step but when i changed it to your method it now allows me to collapse the columns!

I will mark your post as the solution but would appreciate if anybody knows the reason for this to let me know just for my knowledge.