single rows multiple IN/OUT time change to column level

Load Data into Power Query:
Open Excel and load your data into Power Query by selecting your data range and clicking on Data > From Table/Range.
Split the Data into IN and OUT:
Duplicate your query by right-clicking on the query name in the Queries pane and selecting Duplicate.
In the first query, filter the status column to show only IN values.
In the second query, filter the status column to show only OUT values.
Rename Columns:
In the IN query, rename the Date_Time column to IN.
In the OUT query, rename the Date_Time column to OUT.
Merge Queries:
Go to the IN query and select Home > Merge Queries.
Merge the IN query with the OUT query on Reference No, Barcode, Process ID, and Workstep.
Choose the Left Outer join type to keep all rows from the IN query.
Expand the Merged Table:
After merging, expand the OUT table to include the OUT column.
Clean Up:
Remove any unnecessary columns and ensure the data types are correct.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Dear All,

Can anyone help me on this above request how to achive using power query need to fix urgently

Thanks,

Varan

Try,

let
// Load the data
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

// Convert Workstep to uppercase for consistency
#"Uppercased Text" = Table.TransformColumns(Source, {{"Workstep", Text.Upper, type text}}),

// Sort by Process ID and Process datetime to ensure order
#"Sorted Rows" = Table.Sort(#"Uppercased Text", {{"Process ID", Order.Ascending}, {"Process datetime", Order.Ascending}}),

// Group by Process ID and Workstep, adding conditional indexes for Entry and Exit separately
#"Added Conditional Index" = Table.Group(#"Sorted Rows", {"Process ID", "Workstep"}, {
{"AllRows", each
let
// Separate tables for Entry and Exit, with conditional indexing
EntryTable = Table.AddIndexColumn(Table.SelectRows(_, each [status] = "Entry"), "EntryIndex", 1, 1),
ExitTable = Table.AddIndexColumn(Table.SelectRows(_, each [status] = "Exit"), "ExitIndex", 1, 1),

// Rename columns to avoid conflicts
RenamedEntryTable = Table.RenameColumns(EntryTable, {{"Process ID", "Process ID_Entry"}, {"Process datetime", "Entry datetime"}, {"status", "Entry status"}, {"Workstep", "Workstep_Entry"}}),
RenamedExitTable = Table.RenameColumns(ExitTable, {{"Process ID", "Process ID_Exit"}, {"Process datetime", "Exit datetime"}, {"status", "Exit status"}, {"Workstep", "Workstep_Exit"}}),

// Merge the two indexed tables back together using a join
Combined = Table.Join(RenamedEntryTable, {"Process ID_Entry", "Workstep_Entry"}, RenamedExitTable, {"Process ID_Exit", "Workstep_Exit"}, JoinKind.FullOuter)
in
Combined
}
}, GroupKind.Local),

// Expand the nested table to flatten the structure
#"Expanded AllRows" = Table.ExpandTableColumn(#"Added Conditional Index", "AllRows", {"Entry datetime", "Exit datetime", "EntryIndex", "ExitIndex", "Process ID_Entry", "Process ID_Exit", "Entry status", "Exit status", "Workstep_Entry", "Workstep_Exit"}, {"Entry datetime", "Exit datetime", "EntryIndex", "ExitIndex", "Process ID_Entry", "Process ID_Exit", "Entry status", "Exit status", "Workstep_Entry", "Workstep_Exit"}),

// Combine the Process ID and Workstep columns into one and remove the temporary columns
#"Added Combined Process ID" = Table.AddColumn(#"Expanded AllRows", "Process ID", each if [Process ID_Entry] <> null then [Process ID_Entry] else [Process ID_Exit]),
#"Added Combined Workstep" = Table.AddColumn(#"Added Combined Process ID", "Workstep", each if [Workstep_Entry] <> null then [Workstep_Entry] else [Workstep_Exit]),

// Remove temporary columns used for processing
#"Removed Temporary Columns" = Table.RemoveColumns(#"Added Combined Workstep", {"Process ID_Entry", "Process ID_Exit", "EntryIndex", "ExitIndex", "Entry status", "Exit status", "Workstep_Entry", "Workstep_Exit"}),

// Sort the final table by Process ID for proper organization
#"Sorted Final Rows" = Table.Sort(#"Removed Temporary Columns", {{"Process ID", Order.Ascending}})
in
#"Sorted Final Rows"

To achieve the desired transformation in Power Query, follow these steps:

### Steps:

1. **Sort the Data**:
- Ensure the data is sorted by **Reference No**, **Process ID**, and **Date_Time**.

2. **Add a Custom Column**:
- Create a custom column in Power Query to mark each `IN` and `OUT` row.

3. **Pivot the Data**:
- Select the **Workstep** and **Status** columns.
- Use the **Pivot Column** option, setting **Date_Time** as the values column.

4. **Adjust the Output**:
- Rename columns as **IN** and **OUT**, and handle null values for proper representation.

This process will reshape your data as expected.

Hello Varan_15 

Below is the pbix file of my slotuion.

https://we.tl/t-J1sbC6gimD

Hope it helps.

Regards

sanalytics

If it is your solution then please like and accept it as solution

To check if the tickets are within or beyond the SLA based on the given conditions, you will need to calculate the allocated SLA time considering working hours (8 AM to 4 PM) and incorporate the additional hours for specific worksteps (like "Pending" and "Action"). Here’s how you can approach it in Power BI DAX:

Step 1: Calculate the Base SLA Deadline

Create a calculated column or measure to calculate the SLA deadline for each ticket based on the start time:

BaseSLADeadline =
VAR StartTime = 'Table'[StartDateTime]
VAR StartDate = DATEVALUE(StartTime)
VAR SLADeadline =
IF(
HOUR(StartTime) < 11,
StartDate + TIME(16, 0, 0), // Same day 4 PM if started before 11 AM
StartDate + 1 + TIME(16, 0, 0) // Next day 4 PM if started after 11 AM
)
RETURN
SLADeadline

Step 2: Add Extra Time for Specific Worksteps

Now, you need to account for additional hours based on specific worksteps ("Pending" adds 8 hours, "Action" adds 5 hours).

Create a measure or calculated column to calculate the total additional time:

ExtraTimeInHours =
SUMX(
FILTER(
'Table',
'Table'[CustomerID] = EARLIER('Table'[CustomerID]) &&
('Table'[Workstep] = "Pending" || 'Table'[Workstep] = "Action")
),
SWITCH(
'Table'[Workstep],
"Pending", 8,
"Action", 5,
0
)
)

Step 3: Calculate the SLA Deadline Including Additional Hours

You need to adjust the base SLA deadline considering working hours (8 AM to 4 PM). The additional hours should be added only during the working hours.

Create a measure to calculate the adjusted SLA deadline:

AdjustedSLADeadline =
VAR BaseDeadline = [BaseSLADeadline]
VAR ExtraHours = [ExtraTimeInHours]
VAR WorkingStartTime = TIME(8, 0, 0)
VAR WorkingEndTime = TIME(16, 0, 0)
VAR FullDaysToAdd = INT(ExtraHours / 😎
VAR RemainingHours = MOD(ExtraHours, 😎
VAR ExtraDays = IF(HOUR(BaseDeadline) + RemainingHours > 16, 1, 0)
VAR NewDate = BaseDeadline + FullDaysToAdd + ExtraDays
VAR NewTime = TIME(HOUR(BaseDeadline) + RemainingHours, MINUTE(BaseDeadline), SECOND(BaseDeadline))

RETURN
IF(
NewTime > WorkingEndTime,
NewDate + 1 + WorkingStartTime,
NewDate + NewTime
)

Step 4: Compare the Actual Completion Time Against the SLA Deadline

Finally, you can create a measure to check if the ticket is within or beyond the SLA:

SLAStatus =
IF(
'Table'[EndDateTime] <= [AdjustedSLADeadline],
"Within SLA",
"Beyond SLA"
)

Summary

1. BaseSLADeadline calculates the initial SLA deadline based on the start time.

2. ExtraTimeInHours adds additional time for specific worksteps.

3. AdjustedSLADeadline recalculates the SLA deadline considering working hours.

4. SLAStatus compares the actual end time against the adjusted SLA deadline to check if it is within or beyond SLA.

This approach ensures that SLA calculations account for working hours and the specified rules for different scenarios.

ExtraTimeInHours =
VAR HasPending =
CALCULATE(
COUNTROWS(FILTER('Table', 'Table'[Workstep] = "Pending")),
ALLEXCEPT('Table', 'Table'[CustomerID])
) > 0
VAR ActionHours =
CALCULATE(
SUMX(
FILTER('Table', 'Table'[Workstep] = "Action"),
5
),
ALLEXCEPT('Table', 'Table'[CustomerID])
)
RETURN
IF(HasPending, 8, 0) + ActionHours