Regroup lines together according to Time range

[Julbak]

Dear all,

I need help regarding a way to combine lines together according to a time range.

I have got a facts table regrouping production stops/running time and also energy data in the same table. 

For example: 

I have got one Fabrication Order (FO) starting at 6:00 AM and finishing at 3:45 PM. Within this range I have got 2 breakdowns:

- Breakdown 1: from 6:00 AM to 7:AM = changeover time ( 1 hour )

- Breakdown 2: from 7:45 to 8:15 so 30 min

Then I have got lot of lines every 10 mins with the energy consumption at this point (for example from 6:00 AM to 6:10 AM I have got 10 lines with the machine and the energy consumed). 

Can you advise me what type of function can I use to split for example each breakdown and the running time into columns to know what kind of breakdon/running time it was?

Today it is one line for each breakdown but I want to split this line into several lines but separated by 10 min and fit with the energy consumptions date/hour. 

I don't know if it is clear... The idea is to link the energy consumptions according to the line statement (running or stopped)

Thanks in advance

[Julbak]

Hello Thanks for the answer, I will look at it but at least I have got something as a base to work on. Thank you again

[v-jianpeng-msft · Accepted solution]

Hi, @Julbak 

According to your description, you can use the split column function in Power Query with the merge columns function to implement your requirements. Here's the sample data I used:

You need to make sure that each of your lines has a similar form. Use the split column function as follows:

Here are the results:

Next, use the function to merge columns:

Change the type of these two columns to time:

Finally, add a custom column, and the calculation logic is shown in the following figure:

The final result is as follows:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.