cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sushi22
New Member

POWER QUERY conditions according to the repetition of specific value

Hi,

 

how can I put a condition if a specific value repeats itself continuously (WITHOUT INTERRUPTION) at least 3 times?

I want to see if a cell is ABOVE or equal to 3 at least 3 continuous times:

example:

 

the yes = the moment when we reach that 3rd time

 

thank you in advance!

sushi22_2-1669803186436.png

 

 

1 ACCEPTED SOLUTION

I didn't use your data because I didn't trust the site it took me to but I dummied up some test data and I created 2 custom columns in Power Query:

1st column creates a list of last 3 values. Call the column CustomDF

let i = [Index] in { #"Added Index"[val]{i}, #"Added Index"[val]{i - 1}, #"Added Index"[val]{i - 2}}

 

2nd column tests the previous column for all values above 2.

List.MatchesAll([CustomDF], each _ > 2)

 

So if you substitute column names and step names in the above this should work for you.

Please let me know.

 

View solution in original post

7 REPLIES 7
HotChilli
Super User
Super User

If you post the data (not a picture) i'll have a go.

It might not be today but I'll look at it

please find the link belw:

https://ufile.io/ef7qu5o0

 

thank you.

I didn't use your data because I didn't trust the site it took me to but I dummied up some test data and I created 2 custom columns in Power Query:

1st column creates a list of last 3 values. Call the column CustomDF

let i = [Index] in { #"Added Index"[val]{i}, #"Added Index"[val]{i - 1}, #"Added Index"[val]{i - 2}}

 

2nd column tests the previous column for all values above 2.

List.MatchesAll([CustomDF], each _ > 2)

 

So if you substitute column names and step names in the above this should work for you.

Please let me know.

 

I noticed that the formula doesn't take into account the product number

example:

how can I create unicity between the products?

when a new products appear in the list, the calculation sould start the counting again if you see what I mean?

sushi22_0-1669888587598.png

 

It works!! expect for the very last cell

Thanks a lot for your help I really appreciate.!!

HotChilli
Super User
Super User

In Power Query, there isn't a notion of above or below a row.  You will have to add an index column to use for order or maybe a solution involving Table.Group with GroupKind.Local.

It might make it simpler if you add a helper column "above 2"

Thank you HotChilli.

 

I am not familiar with GroupKindLocal, can you please help me ? I have done the index part in the file:

 

let
Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"val", Int64.Type}}),
#"Index ajouté" = Table.AddIndexColumn(#"Type modifié", "Index", 1, 1, Int64.Type)
in
#"Index ajouté"

sushi22_0-1669821480231.png

 

appreciate your help

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.