Forum Discussion

donodackal's avatar
donodackal
Helper I
4 years ago
Solved

Filter data based on two or more conditions from existing columns

Hi there

 

I have a large dataset from which I would like to filter down the data that meets the following conditions: 

  • Filter all data by Invoice Date to only show data from 09-06-2022 for Contract A; and
  • Filter all data by Invoice Date to only show data from 01-06-2022 for Contract B; and
  • Filter all data by Invoice Date to only show data from 01-06-2022 to 14-06-2022 for Contract C; and
  • Filter all data by Invoice Date to only show data from 20-06-2022 for Contract D.

I would appreciate any advice on how I can do this during data transformation in power query. 

 

ContractSupplierInvoice DateInvoice AmountBuyer
AA01/06/2022 $          5,000.00Don
BB02/06/2022 $          5,001.00Don
CC03/06/2022 $          5,002.00John
DD04/06/2022 $          5,003.00John
AE05/06/2022 $          5,004.00John
BF06/06/2022 $          5,005.00Ron
CG07/06/2022 $          5,006.00Ron
DH08/06/2022 $          5,007.00Don
AI09/06/2022 $          5,008.00Don
BJ10/06/2022 $          5,009.00John
CK11/06/2022 $          5,010.00John
DL12/06/2022 $          5,011.00John
AM13/06/2022 $          5,012.00Ron
BN14/06/2022 $          5,013.00Ron
CO15/06/2022 $          5,014.00Don
DP16/06/2022 $          5,015.00Don
AQ17/06/2022 $          5,016.00John
BR18/06/2022 $          5,017.00John
CS19/06/2022 $          5,018.00John
DT20/06/2022 $          5,019.00Ron
AU21/06/2022 $          5,020.00Ron
BV22/06/2022 $          5,021.00Don
CX23/06/2022 $          5,022.00Don
DY24/06/2022 $          5,023.00John
AZ25/06/2022 $          5,024.00John
Need Help
  • Payeras_BI's avatar
    Payeras_BI
    4 years ago

    Hi donodackal ,

    You could try something like this:

     

    = Table.SelectRows(#"Your previous step", each 
([Contract] = "A") and ([Invoice Date] = #date(2022, 6, 9))
or
([Contract] = "B") and ([Invoice Date] = #date(2022, 6, 1))
or
([Contract] = "C") and ( ([Invoice Date] >= #date(2022, 6, 1)) and ([Invoice Date] <= #date(2022, 6, 14)) )
or
([Contract] = "D") and ([Invoice Date] = #date(2022, 6, 20))
)

     

     

2 Replies

  • Payeras_BI's avatar
    Payeras_BI
    Solution Sage
    4 years ago

    Hi donodackal ,

    You could try something like this:

     

    = Table.SelectRows(#"Your previous step", each 
([Contract] = "A") and ([Invoice Date] = #date(2022, 6, 9))
or
([Contract] = "B") and ([Invoice Date] = #date(2022, 6, 1))
or
([Contract] = "C") and ( ([Invoice Date] >= #date(2022, 6, 1)) and ([Invoice Date] <= #date(2022, 6, 14)) )
or
([Contract] = "D") and ([Invoice Date] = #date(2022, 6, 20))
)

     

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

    The performance sucks when it comes to big dataset with Table.SelectRows().

     

    Try using table join operations in PQ or push such workload to DB side,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdO5bsMwEATQf1EtxLuru4ztXM7tHEgiuE8V/38X7U4zFFkYYPEMDGfEea4uqzp+/UY3JmbLsatF5EJkOe7Pf9Wpnqvtct6GMlaaqt1y3oVqWBnU4fwLtve/BGuZNSvmqa6CdczaFfNY18F6Zh3YkaLdhBpY9anyVLehRlZDek0PdhdqYjXmlR1QrDCbVvk92T0cL6BSaO0BjjdQLdT2CMcrqKVX9XhPYLyCNnlvz2C8grbpZT3cCxivoF3e3CsYz6B9YdMjHA+hQ6G7NzieQsdCd+/4fHkLndLLerwPMJ7CJK/uE4yXsMJr+ALjIczy6r7BeAgrPYcfOF7Ckvdw+gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Contract = _t, Supplier = _t, #"Invoice Date" = _t, #"Invoice Amount" = _t, Buyer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract", type text}, {"Supplier", type text}, {"Invoice Date", type date}, {"Invoice Amount", Int64.Type}, {"Buyer", type text}}),
    Lookup = Table.FromRows(
        {{#date(2022,6,9), "A"}, {#date(2022,6,1), "B"}, {#date(2022,6,20), "D"}}
        & List.TransformMany(List.Dates(#date(2022,6,1), Duration.TotalDays(#date(2022,6,14) - #date(2022,6,1))+1, #duration(1,0,0,0)), each {"C"}, (x,y) => {x,y}),
        {"Date", "Contract"}
    ),
    #"Inner Joined" = Table.NestedJoin(Lookup, {"Date", "Contract"}, #"Changed Type", {"Invoice Date", "Contract"}, "Info", JoinKind.Inner),
    #"Expanded Info" = Table.ExpandTableColumn(#"Inner Joined", "Info", {"Supplier", "Invoice Amount", "Buyer"}, {"Supplier", "Invoice Amount", "Buyer"})
in
    #"Expanded Info"