Transpose rows and columns

[Srishti_Pawar]

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.

I tried using group by columns and then pivoting it on the basis of Direction in/out but could not get the desired output. Would appreciate any help on this!

[MFelix · Accepted solution]

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]

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.

Hope it works

[Srishti_Pawar]

Hi, I tried using this method but doesn't work. I'll have to merge the table on the basis of index column after replicating it. 

[MFelix]

Hi @Srishti_Pawar ,

Have you tried the approach I suggested?