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
Highlighted
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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!