Filter data based on two or more conditions from existing columns

[donodackal]

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. 

Contract	Supplier	Invoice Date	Invoice Amount	Buyer
A	A	01/06/2022	$          5,000.00	Don
B	B	02/06/2022	$          5,001.00	Don
C	C	03/06/2022	$          5,002.00	John
D	D	04/06/2022	$          5,003.00	John
A	E	05/06/2022	$          5,004.00	John
B	F	06/06/2022	$          5,005.00	Ron
C	G	07/06/2022	$          5,006.00	Ron
D	H	08/06/2022	$          5,007.00	Don
A	I	09/06/2022	$          5,008.00	Don
B	J	10/06/2022	$          5,009.00	John
C	K	11/06/2022	$          5,010.00	John
D	L	12/06/2022	$          5,011.00	John
A	M	13/06/2022	$          5,012.00	Ron
B	N	14/06/2022	$          5,013.00	Ron
C	O	15/06/2022	$          5,014.00	Don
D	P	16/06/2022	$          5,015.00	Don
A	Q	17/06/2022	$          5,016.00	John
B	R	18/06/2022	$          5,017.00	John
C	S	19/06/2022	$          5,018.00	John
D	T	20/06/2022	$          5,019.00	Ron
A	U	21/06/2022	$          5,020.00	Ron
B	V	22/06/2022	$          5,021.00	Don
C	X	23/06/2022	$          5,022.00	Don
D	Y	24/06/2022	$          5,023.00	John
A	Z	25/06/2022	$          5,024.00	John

[CNENFRNL]

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"

[Payeras_BI · Accepted solution]

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))
)