Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Srishti_Pawar
Frequent Visitor

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

Srishti_Pawar_0-1728041985598.png

 What I'm trying to achieve is the below format.

Srishti_Pawar_1-1728042145286.png

 

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!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



rajendraongole1
Super User
Super User

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

rajendraongole1_0-1728056132339.png

 

now select the direction column and use pivot transformation 

rajendraongole1_1-1728056194121.png

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.

rajendraongole1_2-1728056298938.png

 

 

Hope it works

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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. 

Srishti_Pawar_0-1728634849706.png

 

Hi @Srishti_Pawar ,

 

Have you tried the approach I suggested?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors