cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KirkpM
Regular Visitor

Creating Event Counts using Power Query

I am trying to convert a data cleaning process from Excel into power query in Power BI and am having trouble replicating the following Excel formula in power query:

From cell D2

=IF(AND(AND(A2=A1, B2=B1, C2=C1)),(IF(D1>5, D1, D1 + 1)),1)

KirkpM_0-1634265225908.png

 

The purpose of the formula is to assign a count of incidences for each ID record where if all three fields are equal, that record is assigned a P.CNT value of 1. Each time this condition is met, the P.CNT should increase up to a maximum of 6. In the picture, rows 24 through 27 should be assigned a P.CNT value of 1,2,3 and 4 respectively and rows 28 and 29 would be assigned a P.CNT of 1 and 2.

I am unsure how to replicate this Excel formula in power query and have been unsuccessful with multiple methods. My initial thought was to create a helper column that concatenates the three columns and perform some sort of running count but have been unsuccessful.

Link to excel document:

 

https://docs.google.com/spreadsheets/d/1nHhX6MQLhxRdbXf264iT9Klh7S9CpEDP/edit?usp=sharing&ouid=11591...

 

Link to word document sample question:

 

https://docs.google.com/document/d/1PC8Mgox1l9HMm2FwjDl70asb2xJsbrSn/edit?usp=sharing&ouid=115911802...

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

NewStep=Table.Combine(Table.Group(PreviousStepName,{"ID","LACT","EVENT.O"},{"n",each Table.TransformColumns(Table.AddIndexColumn(_,"P.CNT",0),{"P.CNT",each Number.Mod(_,5)+1})},0)[n])

View solution in original post

1 REPLY 1
wdx223_Daniel
Super User
Super User

NewStep=Table.Combine(Table.Group(PreviousStepName,{"ID","LACT","EVENT.O"},{"n",each Table.TransformColumns(Table.AddIndexColumn(_,"P.CNT",0),{"P.CNT",each Number.Mod(_,5)+1})},0)[n])

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors