Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JoaoMS
Helper III
Helper III

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 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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,

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur , so you mean that it is more complicated to create these new columns in Power Query, because what we currently do is manually calculate these data in the source excel file, and then in Power Query we treat the data (unpivot and pivot) in order to get the following table and then easily create some visualizations:

DateMotorDaily HourAcc. Hours
28/04/2023Motor 1                    -             15,400
28/04/2023Motor 2                    -               8,752
29/04/2023Motor 1                   24           15,424
29/04/2023Motor 2                   12             8,764
30/04/2023Motor 1                     4           15,428
30/04/2023Motor 2                   21             8,785

Thanks,

Joao

Hi,

I would not suggest writing those custom column formulas in the Query Editor.  After unpivoting the raw dataset, we can write DAX measures to calculate the hours of each day.  If you need further help, then share the data with information of 10 motors (columns).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur  please find lines below the link of the spreasheet commented:

 

https://docs.google.com/spreadsheets/d/1NjQr7d10b-iQxUHnZm7Hc-xauk46yR-i/edit?usp=drive_link&ouid=11...

 

Thanks,

Joao

Hi,

You may download my PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
vicky_
Super User
Super User

motor 1 day = 
var previousValue = CALCULATE(max('Table'[m1]), FILTER('Table', 'Table'[date] < EARLIER('Table'[date])))
return IF(previousValue > 0, 'Table'[m1] - previousValue)

vicky__1-1685659370351.png

Try this formula.

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.