Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
Hi,
You may download my PBI file.
Hope this helps.
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?
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.
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:
Date | Motor | Daily Hour | Acc. Hours |
28/04/2023 | Motor 1 | - | 15,400 |
28/04/2023 | Motor 2 | - | 8,752 |
29/04/2023 | Motor 1 | 24 | 15,424 |
29/04/2023 | Motor 2 | 12 | 8,764 |
30/04/2023 | Motor 1 | 4 | 15,428 |
30/04/2023 | Motor 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).
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
70 | |
67 |