cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edgarjb-68 Frequent Visitor
Frequent Visitor

How to calculate rows on a table?

How to calculate rows on a table? I want to create a column "started" as below, for each row of this column I need the result of this excel like formula: =IF(AND(A3=A2;B3-B2<1);"No";"yes")

 

partinstall datestarted
PAU-010126/03/2019 05:17yes
PAU-010107/04/2019 18:56No
PAU-010107/04/2019 19:56yes
PAU-010226/03/2019 05:17yes
PAU-010207/04/2019 18:56No
PAU-010207/04/2019 19:56yes
1 REPLY 1
stvsurf Regular Visitor
Regular Visitor

Re: How to calculate rows on a table?

you can edit your table in power query:

 

#"Sorted Rows" = Table.Sort(#"Changed Type",{ {"part", Order.Ascending},{"install date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "next part", each #"Added Index"{[Index]+0}[part]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "next date", each #"Added Custom"{[Index]+0}[install date]),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "check", each if [part] = [next part] and Date.AddDays([install date],1) > [next date] then "NO" else "YES"),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column", {{"check", "YES"}})

 

I'm sure there is an way to do this with less lines... but at least this should do the trick

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!