Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there
I have a large dataset from which I would like to filter down the data that meets the following conditions:
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 |
Solved! Go to 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))
)
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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))
)