Transpose rows and columns

Hi, I have been trying to transform the data in desirable format but need some help in it. Current Datasets looks like the below snapshot What I'm trying to achieve is the below format. ...

Need Help

MFelix

1 year ago

Hi Srishti_Pawar ,

Do you have any other column that identifies both lines has part of the same group? The in and out time? How do you know that a specific in is corresponding to the out? is it based on order?

If you always have a in and a out try the following:

Sort by Employee, and Log date
Select the Direction and do Pivot columns
On the Pivot do the Values column by the log date and advance Don't aggregate
Right Click the in column and do a fill down
Filter the null from the out column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUTI00zew1DcyMDJRMDS2Mja1MjEgUTQzTylWB5t5RgZWphZWpiYkiuaXluAy0NDKwNzKlFRR3A40tjIwBSISRcEOjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, LogTime = _t, #"Log Date" = _t, #"Log Time" = _t, direction = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"A", Order.Ascending}, {"Log Time", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[direction]), "direction", "Log Time"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"in"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([out] <> null))
in
    #"Filtered Rows"

rajendraongole1

Super User

1 year ago

Hi Srishti_Pawar - Yes, you can achieve this using a pivot in Power Query

Add an Index Column to the data set (from the "Add Column" tab).

Create a Custom Column to mark "In-Out" pairs by creating a new column that increments by 1 only for "In" records. This will serve as a group identifier.

if [Direction] = "In" then 1 else 0

now select the direction column and use pivot transformation

Verify that each "In" record is paired with the correct "Out" record. The index or grouping step ensures that "In" and "Out" logs correspond correctly.