Forum Discussion

Kaustubhhh's avatar
Kaustubhhh
New Member
2 years ago

Filtering repetetive rows in an sql database

  • Hi all, I have one dataset with column names- "tool no", "wear offset" , "tool length", "tool life" , "time stamp". In this dataset rows are coming per second.
  • There are different tool nos who have diff offsets.
  • I wanted to keep only those rows in the dataset where for a particular tool no the tool offset changes.
  • The offset change can be from 0.3 to 0.2 to again 0.3 but I want to record all these rows whenever and whereever the offset for that tool changes.
    • Kindly guide me with this.

       

        

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    You could group on Tool and Offset, using the GroupKind.Local parameter in the Table.Group function. It'll give you a new group for each change in Offset.

     

    --Nate

    • Kaustubhhh's avatar
      Kaustubhhh
      New Member
      2 years ago

      Hi Nate, 

      Can you please elaborate about the same as I am a beginner in Power BI.

      It will great if you can write down the query for this dataset

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    To ensure correct results using GroupKind.Local, you must be sure that your values are sorted--which appears to be the case.

     

    --Nate

  • j_ocean's avatar
    j_ocean
    Helper V
    2 years ago

    Group by all columns except time stamp, aggregate by min time stamp. So if you get readings like 

     

    a 0.3

    b 0.3

    c 0.2

    d 0.2

    e 0.3

     

    You'll retain rows a c and e, which are the time stamps when the value changed.