row to column in power query

[Varan_15]

Hi All,

I have data like same worksteps (input, Pending) with entry and exit date time i want to convert into All the worksteps with entry - exit column level in power query.

Please refer below data and expected result.

Raw source table:

Process ID	Process datetime	Workstep	status
12120	01Aug2024 15:35:52	Input	Entry
12120	01Aug2024 15:43:39	Input	exit
12120	01Aug2024 15:43:39	Pending	Entry
12120	05Aug2024 13:27:37	pending	exit
12120	05Aug2024 13:27:37	Input	Entry
12120	05Aug2024 13:52:53	Input	exit
12120	05Aug2024 13:52:53	Pending	Entry
12120	06Aug2024 14:05:55	Pending	exit
12120	06Aug2024 14:05:55	Input	Entry
12120	06Aug2024 15:22:47	Input	exit
12120	06Aug2024 15:35:47	Pending	Entry

Expected result 1 :

process ID	Workstep	Entry	Exit
12120	Input	01Aug2024 15:35:52	01Aug2024 15:43:39
12120	Pending	01Aug2024 15:43:39	05Aug2024 13:27:37
12120	Input	05Aug2024 13:27:37	05Aug2024 13:52:53
12120	Pending	05Aug2024 13:52:53	06Aug2024 14:05:55
12120	Input	06Aug2024 14:05:55	06Aug2024 15:22:47
12120	Pending	06Aug2024 15:35:47	Null

Or expected result 2:

process ID	Workstep	Entry	Exit
12120	Input 1	01Aug2024 15:35:52	01Aug2024 15:43:39
12120	Pending 1	01Aug2024 15:43:39	05Aug2024 13:27:37
12120	Input 2	05Aug2024 13:27:37	05Aug2024 13:52:53
12120	Pending 2	05Aug2024 13:52:53	06Aug2024 14:05:55
12120	Input 3	06Aug2024 14:05:55	06Aug2024 15:22:47
12120	Pending 3	06Aug2024 15:35:47	Null

 Result 1 or result 2 anything is fine how to achive using in power query

Thanks in advance,

MS

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Uppercased Text" = Table.TransformColumns(Source,{{"Workstep", Text.Upper, type text}}),
    #"Grouped Rows" = Table.Group(#"Uppercased Text", {"Process ID", "Workstep"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1)}},GroupKind.Local),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Process datetime", "status", "Index"}, {"Process datetime", "status", "Index"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Count", "Order", 1, 1, Int64.Type),
    #"Grouped Rows1" = Table.Group(#"Added Index", {"Process ID", "Index"}, {{"Count", each Table.AddIndexColumn(_,"Index1",1,1)}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"Workstep", "Process datetime", "status", "Order", "Index1"}, {"Workstep", "Process datetime", "status", "Order", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Count1",{{"Order", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Order", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Process datetime", type datetime}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[status]), "status", "Process datetime"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns1",{{"Process ID", Order.Ascending}, {"Entry", Order.Ascending}})
in
    #"Sorted Rows1"

Hope this helps.

[MSuser5]

Final:

End DateTime =
VAR StartDateTime = MAX(YourTable[StartDateTime]) -- The start date and time for the current row context
VAR HoursToAdd = [Additional Hours] -- Reference the dynamic measure for additional hours
VAR WorkingStartHour = 8
VAR WorkingEndHour = 16
VAR WorkingHoursPerDay = WorkingEndHour - WorkingStartHour

-- Filter the calendar table to get only future working days starting from the start date
VAR FilteredCalendar =
FILTER(
Calendar,
Calendar[Date] >= DATEVALUE(StartDateTime) &&
Calendar[WorkingDay] = 1
)

-- Calculate the number of complete working days required
VAR FullDaysRequired = INT(HoursToAdd / WorkingHoursPerDay)
VAR RemainingHours = MOD(HoursToAdd, WorkingHoursPerDay)

-- Find the date after adding the required number of working days
VAR EndDate =
MINX(
FILTER(
ADDCOLUMNS(
FilteredCalendar,
"CumulativeDays",
RANKX(
FilteredCalendar,
Calendar[Date],
,
ASC,
DENSE
)
),
[CumulativeDays] > FullDaysRequired
),
Calendar[Date]
)

// Start calculating the remaining hours if any
VAR InitialHour =
IF(
HOUR(StartDateTime) < WorkingStartHour,
WorkingStartHour, -- Set to 8 AM if before working hours
HOUR(StartDateTime) -- Use the current hour otherwise
)

VAR TotalEndHour = InitialHour + RemainingHours -- Add remaining hours to the initial hour

VAR AdjustedEndDate =
IF(
TotalEndHour > WorkingEndHour,
EndDate + 1, -- Move to the next working day if we exceed 4 PM
EndDate -- Stay on the current end date
)

VAR AdjustedEndHour =
IF(TotalEndHour > WorkingEndHour,
TotalEndHour - WorkingHoursPerDay, -- Subtract the working hours to move to the next working day
TotalEndHour -- Keep within the working hours
)

RETURN
AdjustedEndDate + TIME(AdjustedEndHour, 0, 0) -- Return the date with adjusted hours

[MSuser5]

Try 4:

End DateTime =
VAR StartDateTime = MAX(YourTable[StartDateTime]) -- The start date and time for the current row context
VAR HoursToAdd = [Additional Hours] -- Reference the dynamic measure for additional hours
VAR WorkingStartHour = 8
VAR WorkingEndHour = 16
VAR WorkingHoursPerDay = WorkingEndHour - WorkingStartHour

-- Filter the calendar table to get only future working days starting from the start date
VAR FilteredCalendar =
FILTER(
Calendar,
Calendar[Date] >= DATEVALUE(StartDateTime) &&
Calendar[WorkingDay] = 1
)

-- Calculate the number of complete working days required
VAR FullDaysRequired = INT(HoursToAdd / WorkingHoursPerDay)
VAR RemainingHours = MOD(HoursToAdd, WorkingHoursPerDay)

-- Find the date after adding the required number of working days
VAR EndDate =
MINX(
FILTER(
ADDCOLUMNS(
FilteredCalendar,
"CumulativeDays",
RANKX(
FilteredCalendar,
Calendar[Date],
,
ASC,
DENSE
)
),
[CumulativeDays] > FullDaysRequired
),
Calendar[Date]
)

-- Calculate the final time based on the remaining hours
VAR EndTime =
IF (
RemainingHours > 0,
// If there's remaining time, adjust based on the current time
VAR CurrentHour = HOUR(StartDateTime)
VAR CurrentMinute = MINUTE(StartDateTime)

// Check if the start time is before working hours
VAR EffectiveStartHour = IF(CurrentHour < WorkingStartHour, WorkingStartHour, CurrentHour)

// Determine the final hour within working hours
VAR FinalHour = MIN(EffectiveStartHour + RemainingHours, WorkingEndHour)

// If adding remaining hours exceeds working hours, set to working end hour
IF (FinalHour >= WorkingEndHour, WorkingEndHour, FinalHour) + TIME(0, 0, 0) // Reset minutes and seconds
)
ELSE
// If no remaining hours, maintain the start time
TIME(HOUR(StartDateTime), MINUTE(StartDateTime), SECOND(StartDateTime))

RETURN
EndDate + EndTime

[MSuser5]

Try 3:

End DateTime =
VAR StartDateTime = MAX(YourTable[StartDateTime]) -- The start date and time for the current row context
VAR HoursToAdd = [Additional Hours] -- Reference the dynamic measure for additional hours
VAR WorkingStartHour = 8
VAR WorkingEndHour = 16
VAR WorkingHoursPerDay = WorkingEndHour - WorkingStartHour

-- Filter the calendar table to get only future working days starting from the start date
VAR FilteredCalendar =
FILTER(
Calendar,
Calendar[Date] >= DATEVALUE(StartDateTime) &&
Calendar[WorkingDay] = 1
)

-- Calculate the number of complete working days required
VAR FullDaysRequired = INT(HoursToAdd / WorkingHoursPerDay)
VAR RemainingHours = MOD(HoursToAdd, WorkingHoursPerDay)

-- Find the date after adding the required number of working days
VAR EndDate =
MINX(
FILTER(
ADDCOLUMNS(
FilteredCalendar,
"CumulativeDays",
RANKX(
FilteredCalendar,
Calendar[Date],
,
ASC,
DENSE
)
),
[CumulativeDays] > FullDaysRequired
),
Calendar[Date]
)

-- Calculate the final time based on the remaining hours
VAR StartHour =
IF(
HOUR(StartDateTime) < WorkingStartHour,
WorkingStartHour,
MAX(HOUR(StartDateTime), WorkingStartHour)
)
VAR EndTime =
TIME(StartHour + RemainingHours, MINUTE(StartDateTime), SECOND(StartDateTime))

RETURN
EndDate + EndTime

[MSuser5]

Try 2

End DateTime =
VAR StartDateTime = MAX(YourTable[StartDateTime]) -- The start date and time for the current row context
VAR HoursToAdd = [Additional Hours] -- Reference the dynamic measure for additional hours
VAR WorkingStartHour = 8
VAR WorkingEndHour = 16
VAR WorkingHoursPerDay = WorkingEndHour - WorkingStartHour

-- Filter the calendar table to get only future working days starting from the start date
VAR FilteredCalendar =
FILTER(
Calendar,
Calendar[Date] >= DATEVALUE(StartDateTime) &&
Calendar[WorkingDay] = 1
)

-- Calculate how many full working days are required
VAR FullDaysRequired = INT(HoursToAdd / WorkingHoursPerDay)
VAR RemainingHours = MOD(HoursToAdd, WorkingHoursPerDay)

-- Identify the date when the required full days are used up
VAR EndDate =
MINX(
FILTER(
ADDCOLUMNS(
FilteredCalendar,
"CumulativeDays",
RANKX(
ALL(FilteredCalendar),
Calendar[Date],
,
ASC,
DENSE
)
),
[CumulativeDays] > FullDaysRequired
),
Calendar[Date]
)

-- Calculate the final time based on remaining hours
VAR EndTime =
IF(
HOUR(StartDateTime) < WorkingStartHour,
TIME(WorkingStartHour + RemainingHours, 0, 0),
TIME(HOUR(StartDateTime) + RemainingHours, MINUTE(StartDateTime), SECOND(StartDateTime))
)

RETURN
EndDate + EndTime

[MSuser5]

New:

EndDateTime =
VAR StartDateTime = YourTable[StartDateTime] -- The start date and time
VAR HoursToAdd = [Additional Hours] -- Measure for additional hours
VAR WorkingStartHour = 8
VAR WorkingEndHour = 16
VAR WorkingHoursPerDay = WorkingEndHour - WorkingStartHour

-- Filter the calendar table to get only future working days from the start date
VAR FilteredCalendar =
FILTER(
Calendar,
Calendar[Date] >= DATEVALUE(StartDateTime) &&
Calendar[WorkingDay] = 1
)

-- Calculate the number of complete days needed to accumulate the required working hours
VAR FullDaysRequired = INT(HoursToAdd / WorkingHoursPerDay)
VAR RemainingHours = MOD(HoursToAdd, WorkingHoursPerDay)

-- Find the date when all the full days have been used up
VAR EndDate =
MINX(
FILTER(
ADDCOLUMNS(
FilteredCalendar,
"CumulativeDays",
COUNTROWS(FILTER(FilteredCalendar, Calendar[Date] <= EARLIER(Calendar[Date])))
),
[CumulativeDays] > FullDaysRequired
),
Calendar[Date]
)

-- Calculate the final time based on remaining hours
VAR EndTime =
IF(
HOUR(StartDateTime) < WorkingStartHour,
TIME(WorkingStartHour + RemainingHours, 0, 0),
TIME(HOUR(StartDateTime) + RemainingHours, MINUTE(StartDateTime), SECOND(StartDateTime))
)

• RETURN
  EndDate + EndTime

[MSuser5]

NewDateTime =
VAR StartDateTime = YourTable[DateTimeColumn]
VAR HoursToAdd = [Additional Hours] -- Reference to the dynamic measure
VAR WorkingStartHour = 8
VAR WorkingEndHour = 16
VAR WorkingHoursPerDay = WorkingEndHour - WorkingStartHour

-- Filter calendar to get only future working days from the start date
VAR FilteredCalendar =
FILTER(
Calendar,
Calendar[Date] >= DATEVALUE(StartDateTime) &&
Calendar[WorkingDay] = 1
)

-- Accumulate the working hours
VAR Result =
ADDCOLUMNS(
FilteredCalendar,
"AccumulatedHours",
VAR CurrentDay = Calendar[Date]
VAR StartTime = IF(CurrentDay = DATEVALUE(StartDateTime), MAX(WorkingStartHour, HOUR(StartDateTime)), WorkingStartHour)
VAR EndTime = WorkingEndHour
VAR AvailableHours = EndTime - StartTime
RETURN
MIN(HoursToAdd, AvailableHours)
)

-- Sum the accumulated hours to determine the end date and time
VAR TotalHoursAccumulated = SUMX(Result, [AccumulatedHours])
VAR AdditionalDays = INT(TotalHoursAccumulated / WorkingHoursPerDay)
VAR RemainingHours = MOD(TotalHoursAccumulated, WorkingHoursPerDay)

-- Calculate the final date and time
VAR FinalDate = MINX(FILTER(FilteredCalendar, Calendar[Date] >= DATEVALUE(StartDateTime)), Calendar[Date] + AdditionalDays)
VAR FinalTime = TIME(WorkingStartHour + RemainingHours, 0, 0)

RETURN
FinalDate + FinalTime

[PavanLalwani · Accepted solution]

To transform the data from row to column in Power Query and achieve either **Result 1** or **Result 2**, follow these steps:

### Steps in Power Query:

1. **Sort the Data**: Ensure the data is sorted by `Process ID`, `Workstep`, and `Process datetime`.

2. **Group the Data**:
- Use **Group By** on `Process ID` and `Workstep`.
- Inside **Group By**, aggregate the **Entry** and **Exit** times:
- For **Entry**, choose **Min**.
- For **Exit**, choose **Max** (filter for "Exit" status).

3. **Conditional Column**: If needed, add a counter or index column to differentiate repeated worksteps for **Result 2**.

This will reshape the data as you need.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Uppercased Text" = Table.TransformColumns(Source,{{"Workstep", Text.Upper, type text}}),
    #"Grouped Rows" = Table.Group(#"Uppercased Text", {"Process ID", "Workstep"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1)}},GroupKind.Local),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Process datetime", "status", "Index"}, {"Process datetime", "status", "Index"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Count", "Order", 1, 1, Int64.Type),
    #"Grouped Rows1" = Table.Group(#"Added Index", {"Process ID", "Index"}, {{"Count", each Table.AddIndexColumn(_,"Index1",1,1)}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"Workstep", "Process datetime", "status", "Order", "Index1"}, {"Workstep", "Process datetime", "status", "Order", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Count1",{{"Order", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Order", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Process datetime", type datetime}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[status]), "status", "Process datetime"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns1",{{"Process ID", Order.Ascending}, {"Entry", Order.Ascending}})
in
    #"Sorted Rows1"

Hope this helps.

[Varan_15]

@Ashish_Mathur  @PavanLalwani 

Thanks for your reply it's working both methods as well.

Regards,

Varan

[Varan_15]

@Ashish_Mathur ,

Thanks for your update i have follow the same but getting error while using in SQL table as source

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
[List]

I have noticed that were as Entry and Exit date& time both are same for both workstep then making this error kindly suggest how to avaoid incase of same time for all.

Thanks,

[Ashish_Mathur]

Hi,

As per the data that you shared, my code is working fine.  Share data with the row which are triggering that error.

[Varan_15]

@Ashish_Mathur ,

Thanks for your update, For example i have other scenario data which is same workstep and entry - exit time as well in that case getting error.

Process ID	Process datetime	Workstep	status
12120	01/08/2024 15:35	Input	Entry
12120	01/08/2024 15:43	Input	exit
12120	01/08/2024 15:43	Pending	Entry
12120	05/08/2024 13:27	pending	exit
12120	05/08/2024 13:27	Input	Entry
12120	05/08/2024 13:52	Input	exit
12120	05/08/2024 13:52	Pending	Entry
12120	06/08/2024 14:05	Pending	exit
12120	06/08/2024 14:05	Input	Entry
12120	06/08/2024 15:22	Input	exit
12120	06/08/2024 15:35	Pending	exit
12120	06/08/2024 14:05	Pending	Entry
12120	06/08/2024 14:05	Input	Entry
12120	06/08/2024 15:35	Pending	exit

example raw table :

Expected Output:

Process ID	Workstep	Entry	Exit
12120	Input	01/08/2024 15:35	01/08/2024 15:43
12120	Pending	01/08/2024 15:43	05/08/2024 13:27
12120	Input	05/08/2024 13:27	05/08/2024 13:52
12120	Pending	05/08/2024 13:52	06/08/2024 14:05
12120	Input	06/08/2024 14:05	06/08/2024 15:22
12120	Input	06/08/2024 14:05	Null
12120	Pending	06/08/2024 14:05	06/08/2024 15:35
12120	Pending	Null	06/08/2024 15:35

but i'm getting error:

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
[List]

kindly suggest  Thanks,

[Ashish_Mathur]

Hi,

My code works perfectly.  See image

[Varan_15]

please suggest how to achive usig power query  regarding above highlighted.

Thanks in advance

[Kedar_Pande]

@Varan_15 

M code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Process ID", type text}, {"Process datetime", type datetime}, {"Workstep", type text}, {"status", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Process ID", Order.Ascending}, {"Process datetime", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([status] = "Entry" or [status] = "exit")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Process ID", "Workstep"}, {{"All Data", each _, type table}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"Process datetime", "status"}),
#"Added Exit Date" = Table.AddColumn(#"Expanded Data", "Exit", each if [status] = "Entry" then List.First(List.Select(#"Expanded Data"[Process datetime], (x) => x > [Process datetime] and [Workstep] = [Workstep])) else null)
in
#"Added Exit Date"

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Varan_15]

@Kedar_Pande ,

Thanks for your reply.

Anyhow as per your logic may need some similar changes based on data discrepancy as i have data like begins with exit time  those may have to exclude it.

example raw data:

Process ID	Process datetime	Workstep	status
12120	01Aug2024 14:10:30	Pending	Exit
12120	01Aug2024 15:35:52	Input	Entry
12120	01Aug2024 15:43:39	Input	exit
12120	01Aug2024 15:43:39	Pending	Entry
12120	05Aug2024 13:27:37	pending	exit
12120	05Aug2024 13:27:37	Input	Entry
12120	05Aug2024 13:52:53	Input	exit
12120	05Aug2024 13:52:53	Pending	Entry
12120	06Aug2024 14:05:55	Pending	exit
12120	06Aug2024 14:05:55	Input	Entry
12120	06Aug2024 15:22:47	Input	exit
12120	06Aug2024 15:35:47	Pending	Entry

So expected output:

process ID	Workstep	Entry	Exit
12120	Pending	Null	01Aug2024 14:10:30
12120	Input	01Aug2024 15:35:52	01Aug2024 15:43:39
12120	Pending	01Aug2024 15:43:39	05Aug2024 13:27:37
12120	Input	05Aug2024 13:27:37	05Aug2024 13:52:53
12120	Pending	05Aug2024 13:52:53	06Aug2024 14:05:55
12120	Input	06Aug2024 14:05:55	06Aug2024 15:22:47
12120	Pending	06Aug2024 15:35:47	Null

Kindly help to achive 

thanks,