Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Multiple Table.Select Rows Filters in Query

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). ...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

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.