Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I am trying to get the running time of a machine & I have only the stoppages recorded + few details on the starting/ending time.
Facts that I know is that on a daily basis the machine starts running at 07h00 am & stops at 15h00 pm / weekend off.
Current information available:
The information I need is actual running time as per below:
Any suggestion or idea would be highly appreciated! 😊
Solved! Go to Solution.
The algorithm would be something like: (Power Query)
Take the original 'Date Fin' Column and add in a record for 0700 for each date . This will end up as the 'Date Debut' column
-> So take the column , separate date and time, remove the time column. Remove duplicates. Add a column for time 0700. Merge columns to get a table with one column which is 0700 for each date.
Append this table to a table that holds the original 'Date Fin' column. Sort it by datetime.
You'll want to do a similar thing to the original 'Date Debut' column , by adding a 1500 record for each day to make the final 'Date Fin' column.
You then have to find a way of Merging the 2 tables to get the records to line up. I'm thinking of a new column with just the Date in each table. Create an index column with in each day so
1.9.20 0700 1.9.20 1
1.9.20 0805 1.9.20 2
.....
2.9.20 0700 2.9.20 1
You can then Merge the 2 tables on Date and Index columns. After that, you calculate the duration column.
Take each step carefully. Good luck.
The algorithm would be something like: (Power Query)
Take the original 'Date Fin' Column and add in a record for 0700 for each date . This will end up as the 'Date Debut' column
-> So take the column , separate date and time, remove the time column. Remove duplicates. Add a column for time 0700. Merge columns to get a table with one column which is 0700 for each date.
Append this table to a table that holds the original 'Date Fin' column. Sort it by datetime.
You'll want to do a similar thing to the original 'Date Debut' column , by adding a 1500 record for each day to make the final 'Date Fin' column.
You then have to find a way of Merging the 2 tables to get the records to line up. I'm thinking of a new column with just the Date in each table. Create an index column with in each day so
1.9.20 0700 1.9.20 1
1.9.20 0805 1.9.20 2
.....
2.9.20 0700 2.9.20 1
You can then Merge the 2 tables on Date and Index columns. After that, you calculate the duration column.
Take each step carefully. Good luck.
Thank you VERY much for your help!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |