Forum Discussion
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.
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!
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"
4 Replies
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
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.
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"
