Multiple Table.Select Rows Filters in Query

[Anonymous]

I'm trying to reduce the amount of data I'm receiving from SAP BW (HANA), since I'm working on a stand-alone laptop and it keeps running into memory errors when loading the data from SAP BW (HANA).

Below is the current setup I have (simplified). It gives me the data for 2 companies (Company 1 and Company 2). Now I want to add an additional filter on the "Fiscal year.Fiscal year Level 01". Values in that column (when I load it) are "K4/2018", "K4/2019" and "K4/2020". I want to only load 2020-data.

I've tried several things (adding another "or" statement, adding another "Table. Select Rows"-line below the first one), but nothing works. Either it doesn't load at all - giving me the error "Unknown error when executing MDX" - or it loads, but doesn't filter on the year.

Anyone got a solution?

let
    Bron = SapBusinessWarehouse.Cubes("sapbw.wp.sdc-gsa.intra", "00", "100", [Implementation="2.0", LanguageCode="EN"]),
    VCFTM01 = Bron{[Name="VCFTM01"]}[Data],
    #"VCFTM01/QP_VCFTM01_T007" = VCFTM01{[Id="VCFTM01/QP_VCFTM01_T007"]}[Data],
    #"Toegevoegde items" = Cube.Transform(#"VCFTM01/QP_VCFTM01_T007",
        {
            {Cube.ApplyParameter, "[!V000008]", {"[0CO_AREA].[Z001]"}},
            {Cube.ApplyParameter, "[!V000017]", {"[0FISCPER].[K42020004]"}},
            {Cube.ApplyParameter, "[!V000018]", {"[APLNCAT].[RF1]"}},
            {Cube.AddAndExpandDimensionColumn, "[0COMP_CODE]", {"[0COMP_CODE].[LEVEL01]"}, {"Company code.Company code Level 01"}},
            {Cube.AddAndExpandDimensionColumn, "[0FISCYEAR]", {"[0FISCYEAR].[LEVEL01]"}, {"Fiscal year.Fiscal year Level 01"}},
            {Cube.AddMeasureColumn, "Amount#(cr)#(lf)[CC]", "[Measures].[RKF_VCFTM01_127]"},
            {Cube.AddMeasureColumn, "Quantity", "[Measures].[RKF_VCFTM01_125]"},
            {Table.SelectRows, each [Company code.Company code Level 01] = "Company 1" or [Company code.Company code Level 01] = "Company 2"}})
in
    #"Toegevoegde items"

[fzfz]

Hello,

I need to filter measures column and import rows with measure.quantity different to zero.

Any help please ? 

Thank you 

[Icey · Accepted solution]

Hi @Anonymous ,

To my knowledge, you just want to load data in 2020, right? If so, try this:

let
    Bron = SapBusinessWarehouse.Cubes("sapbw.wp.sdc-gsa.intra", "00", "100", [Implementation="2.0", LanguageCode="EN"]),
    VCFTM01 = Bron{[Name="VCFTM01"]}[Data],
    #"VCFTM01/QP_VCFTM01_T007" = VCFTM01{[Id="VCFTM01/QP_VCFTM01_T007"]}[Data],
    #"Toegevoegde items" = Cube.Transform(#"VCFTM01/QP_VCFTM01_T007",
        {
            {Cube.ApplyParameter, "[!V000008]", {"[0CO_AREA].[Z001]"}},
            {Cube.ApplyParameter, "[!V000017]", {"[0FISCPER].[K42020004]"}},
            {Cube.ApplyParameter, "[!V000018]", {"[APLNCAT].[RF1]"}},
            {Cube.AddAndExpandDimensionColumn, "[0COMP_CODE]", {"[0COMP_CODE].[LEVEL01]"}, {"Company code.Company code Level 01"}},
            {Cube.AddAndExpandDimensionColumn, "[0FISCYEAR]", {"[0FISCYEAR].[LEVEL01]"}, {"Fiscal year.Fiscal year Level 01"}},
            {Cube.AddMeasureColumn, "Amount#(cr)#(lf)[CC]", "[Measures].[RKF_VCFTM01_127]"},
            {Cube.AddMeasureColumn, "Quantity", "[Measures].[RKF_VCFTM01_125]"},
            {Table.SelectRows, each ([Company code.Company code Level 01] = "Company 1" or [Company code.Company code Level 01] = "Company 2") and Text.Contains([Fiscal year.Fiscal year Level 01], "2020")}})    ------------------------added.
in
    #"Toegevoegde items"

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous ,

Try the following code:

let
    Bron = SapBusinessWarehouse.Cubes("sapbw.wp.sdc-gsa.intra", "00", "100", [Implementation="2.0", LanguageCode="EN"]),
    VCFTM01 = Bron{[Name="VCFTM01"]}[Data],
    #"VCFTM01/QP_VCFTM01_T007" = VCFTM01{[Id="VCFTM01/QP_VCFTM01_T007"]}[Data],
    #"Toegevoegde items" = Cube.Transform(#"VCFTM01/QP_VCFTM01_T007",
        {
            {Cube.ApplyParameter, "[!V000008]", {"[0CO_AREA].[Z001]"}},
            {Cube.ApplyParameter, "[!V000017]", {"[0FISCPER].[K42020004]"}},
            {Cube.ApplyParameter, "[!V000018]", {"[APLNCAT].[RF1]"}},
            {Cube.AddAndExpandDimensionColumn, "[0COMP_CODE]", {"[0COMP_CODE].[LEVEL01]"}, {"Company code.Company code Level 01"}},
            {Cube.AddAndExpandDimensionColumn, "[0FISCYEAR]", {"[0FISCYEAR].[LEVEL01]"}, {"Fiscal year.Fiscal year Level 01"}},
            {Cube.AddMeasureColumn, "Amount#(cr)#(lf)[CC]", "[Measures].[RKF_VCFTM01_127]"},
            {Cube.AddMeasureColumn, "Quantity", "[Measures].[RKF_VCFTM01_125]"},
            {Table.SelectRows, each ([Company code.Company code Level 01] = "Company 1" or [Company code.Company code Level 01] = "Company 2") and ([Fiscal year.Fiscal year Level 01] = "K4/2018" or [Fiscal year.Fiscal year Level 01] = "K4/2019" or [Fiscal year.Fiscal year Level 01] = "K4/2020")}})
in
    #"Toegevoegde items"

Basically I change the select rows to incorporate the columns filter in the same group with parenteses and then within each one have an or statment.

Be aware I may have made some error writing the columns names so please check them.