Forum Discussion

edgarjb-68's avatar
edgarjb-68
Frequent Visitor
7 years ago

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
General Info

2 Replies

  • stvsurf's avatar
    stvsurf
    Icon for Resolver I rankResolver I
    6 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    edgarjb-68 , try like

    Column = var _next = minx(filter(Sheet1,[part] =EARLIER([part]) && [install date] >EARLIER(Sheet1[install date])),[install date]) 
return if(DATEDIFF([install date],_next,day)<1 && NOT(ISBLANK(DATEDIFF([install date],_next,day))),"No","Yes")

     

    File attached after signature