Forum Discussion

JoaoMS's avatar
JoaoMS
Icon for Helper III rankHelper III
3 years ago
Solved

Create colum with the difference of two rows

Hi all,

 

I've been trying to figure out how to create a new column in Power Query in order to calculate the difference between two values of some columns. Every day we get data of Accumulated Operating Hours of some equipments, and we need to know the daily operating hours. As seen in the next table, we have a base line of data and it grows every day.

 

    To create  To create 
Date Motor 1 Acc.  Motor 2 Acc.  Motor 1 Day  Motor 2 Day 
28/04/2023                15,400                   8,752                          -                            -  
29/04/2023                15,424                   8,764                         24                         12
30/04/2023                15,428                   8,785                           4                         21

 

Thanks in advance,

Joao 

Need Help
Tips and Tricks

9 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    3 years ago
    motor 1 day = 
var previousValue = CALCULATE(max('Table'[m1]), FILTER('Table', 'Table'[date] < EARLIER('Table'[date])))
return IF(previousValue > 0, 'Table'[m1] - previousValue)

    Try this formula.

    • JoaoMS's avatar
      JoaoMS
      Icon for Helper III rankHelper III
      3 years ago

      Hi, it works fine for DAX, however we need to create these columns in Power Query because after that we need to do some unpivot and pivot actions.

       

      Thanks.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Will there be a row for each day (without anyday being missed)?  While this can be done using Power Query, the solution would be difficult to author if there are many Motors i.e. columns because one will have to create a calculated column for each motor.  How many motor columns do you have?

    • JoaoMS's avatar
      JoaoMS
      Icon for Helper III rankHelper III
      3 years ago

      Hi Ashish_Mathur , indeed, everyday the table will increase one row with data of at least 12 motors. So we'll need to create 12 new columns with the daily operating hours.

       

      Thanks,

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

        So that is then a convincing reason to not do it in the Query Editor because you will have to create those columns manually.  You should do this via a measure directly in the visual.