cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viveksingh90 Frequent Visitor
Frequent Visitor

How to filter records depending upon condition on different row values

Hello All

 

I have a table which store device id and its daily status. I want to filter first date where device was in worst condition for five consecutive dates. Please find below sample data.

I want investigate only whose device ID which are in worst condition in five consecutive dates. How to achieve this in power query or DEX.Please consider that I do have billions of records in table

Let me know if I am not clear. regards Vivek

 

MACDATESTATUS
ABCEERDDF2018-12-01GOOD
ABCEERDDF2018-12-02GOOD
ABCEERDDF2018-12-03GOOD
ABCEERDDF2018-12-04GOOD
ABCEERDDF2018-12-05WORST
ABCEERDDF2018-12-06GOOD
ABCEERDDF2018-12-07WORST
ABCEERDDF2018-12-08WORST
ABCEERDDF2018-12-09WORST
ABCEERDDF2018-12-10WORST
ABCEERDDF2018-12-11WORST
ABCEERDDF2018-12-12GOOD
ABCEERDDF2018-12-13WORST
ABCEERDDF2018-12-14WORST
ABCEERDDF2018-12-15WORST
YDSHHSKJL2018-12-01WORST
YDSHHSKJL2018-12-02GOOD
YDSHHSKJL2018-12-03WORST
YDSHHSKJL2018-12-04WORST
YDSHHSKJL2018-12-05WORST
YDSHHSKJL2018-12-06WORST
YDSHHSKJL2018-12-07WORST
YDSHHSKJL2018-12-08GOOD
YDSHHSKJL2018-12-09WORST
YDSHHSKJL2018-12-10WORST
YDSHHSKJL2018-12-11WORST
YDSHHSKJL2018-12-12WORST
YDSHHSKJL2018-12-13WORST
YDSHHSKJL2018-12-14GOOD
YDSHHSKJL2018-12-15GOOD
ABBCA12342018-12-01GOOD
ABBCA12342018-12-02GOOD
ABBCA12342018-12-03GOOD
ABBCA12342018-12-04GOOD
ABBCA12342018-12-05GOOD
ABBCA12342018-12-06GOOD
ABBCA12342018-12-07GOOD
ABBCA12342018-12-08GOOD
ABBCA12342018-12-09GOOD
ABBCA12342018-12-10GOOD
ABBCA12342018-12-11GOOD
ABBCA12342018-12-12GOOD
ABBCA12342018-12-13GOOD
ABBCA12342018-12-14GOOD
ABBCA12342018-12-15GOOD
ZDHWLHWL2018-12-01GOOD
ZDHWLHWL2018-12-02GOOD
ZDHWLHWL2018-12-03GOOD
ZDHWLHWL2018-12-04GOOD
ZDHWLHWL2018-12-05GOOD
ZDHWLHWL2018-12-06GOOD
ZDHWLHWL2018-12-07GOOD
ZDHWLHWL2018-12-08GOOD
ZDHWLHWL2018-12-09GOOD
ZDHWLHWL2018-12-10GOOD
ZDHWLHWL2018-12-11GOOD
ZDHWLHWL2018-12-12GOOD
ZDHWLHWL2018-12-13WORST
ZDHWLHWL2018-12-14GOOD
ZDHWLHWL2018-12-15GOOD
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: How to filter records depending upon condition on different row values

If your data is sorted already (like the sample data you've provided), this method should provide a result fairly fast:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdTdCoJAFATgd/E6yF1/sst0LalAyEAqev/XyMs5NqcJvBE+luPMcV+v7NB2fX9L6ZhtsjxsQ9zGPDTLy2kcU/berESUopCilKICMY+36f5NannITh/SaLKXJOSaBE1ksqHQh5Sa8GwfaRqG6XK+rtfAIXRaI/i0hvBpDflj2loTvgqGNPKD+CYgcTbBEJ2t2QSH6HAD/cuMqPjGtd0hxKL8cR+AcLYWhHMfgHDuAxB6Uuc6ALGTgq6AEXslzAZwITP1bgIQMlPevhE002ca5uvyuOUjoHMioGMioFMikEPS3hHQ2hHQ1hHQ0gHwzhGoJHnjCPj/jkJFSfp+fwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MAC = _t, DATE = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MAC", type text}, {"DATE", type date}, {"STATUS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"MAC", "STATUS"}, {{"Count", each Table.RowCount(_), type number}, {"All", each List.First(_[DATE]), type table}}, GroupKind.Local),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([STATUS] = "WORST")),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each [Count] >= 5)
in
    #"Filtered Rows"

see this article for more details, if interested: https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

6 REPLIES 6
Super User
Super User

Re: How to filter records depending upon condition on different row values

Not sure how it will scale, but you could add an Index and then filter out only the "WORST" in Power Query then add this column:

 

Column = COUNTROWS(FILTER(ALL('Table12'),[DATE]>=EARLIER([DATE]) && [DATE]<=EARLIER([DATE])+4 && [MAC]=EARLIER([MAC])))

Then create a table visual with MAC and filter it to Column = 5.

 

See attached, Table12 Page 7.

 

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

viveksingh90 Frequent Visitor
Frequent Visitor

Re: How to filter records depending upon condition on different row values

Thanks Greg, it is very helpful.

There are lot of other operation I am doing at power query level.

Is it possible to put this calculation at power query level?

 

Thanks for your help 

Gratitude 

Super User
Super User

Re: How to filter records depending upon condition on different row values

Oh, I'm sure there is a way but I'm not that best person to help you with that, but I know someone who would be, @ImkeF

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Super User
Super User

Re: How to filter records depending upon condition on different row values

If your data is sorted already (like the sample data you've provided), this method should provide a result fairly fast:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdTdCoJAFATgd/E6yF1/sst0LalAyEAqev/XyMs5NqcJvBE+luPMcV+v7NB2fX9L6ZhtsjxsQ9zGPDTLy2kcU/berESUopCilKICMY+36f5NannITh/SaLKXJOSaBE1ksqHQh5Sa8GwfaRqG6XK+rtfAIXRaI/i0hvBpDflj2loTvgqGNPKD+CYgcTbBEJ2t2QSH6HAD/cuMqPjGtd0hxKL8cR+AcLYWhHMfgHDuAxB6Uuc6ALGTgq6AEXslzAZwITP1bgIQMlPevhE002ca5uvyuOUjoHMioGMioFMikEPS3hHQ2hHQ1hHQ0gHwzhGoJHnjCPj/jkJFSfp+fwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MAC = _t, DATE = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MAC", type text}, {"DATE", type date}, {"STATUS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"MAC", "STATUS"}, {{"Count", each Table.RowCount(_), type number}, {"All", each List.First(_[DATE]), type table}}, GroupKind.Local),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([STATUS] = "WORST")),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each [Count] >= 5)
in
    #"Filtered Rows"

see this article for more details, if interested: https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

viveksingh90 Frequent Visitor
Frequent Visitor

Re: How to filter records depending upon condition on different row values

@ImkeF Thank you, it worked Smiley Happy

 

@Greg_Deckler Thank you Smiley Happy 

Super User
Super User

Re: How to filter records depending upon condition on different row values

@ImkeF is always the answer when it comes to Power Query!


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 109 members 1,426 guests
Please welcome our newest community members: