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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How can I pivot columns but keep duplicates?

I have a dataset that I need to pivot to create a list of attendees for meetings. Here is a sample:

 

DateContextAttendeeEventTime
4/16/2020

8 AM Call

BillJoined8:05 AM
4/16/20208 AM CallBillLeft8:15 AM
4/16/20208 AM CallBillJoined8:17 AM
4/16/20208 AM CallKellyJoined8:25 AM
4/16/202010 AM CallKellyJoined9:45 AM
4/16/202010 AM CallKellyLeft10:45 AM
4/17/20208 AM CallBillJoined7:30 AM

 

What I would like to create is a report that displays as follows where "join" and "left" events are automatically "paired":

 

DateContextAttendeeJoinedLeft
4/16/20208 AM CallBill8:05 AM8:15 AM
4/16/20208 AM CallBill8:17 AM 
4/16/20208 AM CallKelly8:25 AM 
4/16/202010 AM CallKelly9:45 AM10:45 AM
4/17/20208 AM CallBill7:30 AM 

 

Is this possible? I was able to pivot the Event column, but I get errors where duplicate events happened in the same meeting, which is quite frustrating.

1 ACCEPTED SOLUTION
edhans
Super User
Super User

So the issue is when pivoting it is assumed there will be unique values after the pivot. If you tell it not to aggregate, and there are not unique items in the unpivoted columns, it won't work.

 

See this code - put it in Power Query, a blank query.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NNM3MjAyUNJRslBw9FVwTszJAbKdMsGUV35mXmoKSM7KwBQorRSrQ1CPT2paCViHIbE6kGwxNCekxzs1J6cSVZMRVosMDfDpsrQyIV4X1EuGBqiazInxk7mVsQFYTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Context = _t, Attendee = _t, Event = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Context", type text}, {"Attendee", type text}, {"Event", type text}, {"Time", type time}}),
    #"Added Joined" = Table.AddColumn(#"Changed Type", "Joined", each if [Event] = "Joined" then [Time] else null, type time),
    #"Filtered for Joined" = Table.SelectRows(#"Added Joined", each ([Joined] <> null)),
    #"Added Left" = Table.AddColumn(#"Changed Type", "Left", each if [Event] = "Left" then [Time] else null, type time),
    #"Filtered for Left" = Table.SelectRows(#"Added Left", each ([Left] <> null)),
    #"Combined Table" = Table.Combine({#"Filtered for Joined", #"Filtered for Left"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Combined Table",{"Date", "Context", "Attendee", "Joined", "Left"})
in
    #"Removed Other Columns"

 

It will get you to here.2020-04-16 13_05_40-Untitled - Power Query Editor.png

The problem is there is no good way immediately apparent for PQ to know you want row 6 to be combined with row 1, and 7 with 4.

 

I'd honestly look at this from another angle. Why are you wanting to pivot the Times? Why not leave them in their normalized format and construct your measures to filter based on Join or Leave? In other words, what is your end goal, and let's see if we can get there another way.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

Try this code on Advanced Editor:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NNM3MjAyUNJRslBw9FVwTszJAbKdMsGUV35mXmoKSM7KwBQorRSrQ1CPT2paCViHIbE6kGwxNCekxzs1J6cSVZMRVosMDfDpsrQyIV4X1EuGBqiazInxk7mVsQFYTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Context = _t, Attendee = _t, Event = _t, Time = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Time", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Attendee", "Context"}, {{"Count", each _, type table [Date=date, Attendee=text, Event=text, Time=time]}}),
Index = Table.TransformColumns(#"Grouped Rows", {{"Count", each Table.AddIndexColumn(_, "GroupIndex", 1, 1)}}),
#"Expanded Count" = Table.ExpandTableColumn(Index, "Count", {"Date", "Context", "Attendee", "Event", "Time", "GroupIndex"}, {"Date.1", "Context.1", "Attendee.1", "Event", "Time", "GroupIndex"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Date.1", "Attendee.1", "Context.1"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Number.Mod([GroupIndex], 2) = 0 then [GroupIndex] - 1
else [GroupIndex]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"GroupIndex"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Event]), "Event", "Time"),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"Joined", type time}, {"Left", type time}})
in
#"Changed Type1"

 

 

Ricardo



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

Proud to be a Super User!



edhans
Super User
Super User

So the issue is when pivoting it is assumed there will be unique values after the pivot. If you tell it not to aggregate, and there are not unique items in the unpivoted columns, it won't work.

 

See this code - put it in Power Query, a blank query.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3NNM3MjAyUNJRslBw9FVwTszJAbKdMsGUV35mXmoKSM7KwBQorRSrQ1CPT2paCViHIbE6kGwxNCekxzs1J6cSVZMRVosMDfDpsrQyIV4X1EuGBqiazInxk7mVsQFYTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Context = _t, Attendee = _t, Event = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Context", type text}, {"Attendee", type text}, {"Event", type text}, {"Time", type time}}),
    #"Added Joined" = Table.AddColumn(#"Changed Type", "Joined", each if [Event] = "Joined" then [Time] else null, type time),
    #"Filtered for Joined" = Table.SelectRows(#"Added Joined", each ([Joined] <> null)),
    #"Added Left" = Table.AddColumn(#"Changed Type", "Left", each if [Event] = "Left" then [Time] else null, type time),
    #"Filtered for Left" = Table.SelectRows(#"Added Left", each ([Left] <> null)),
    #"Combined Table" = Table.Combine({#"Filtered for Joined", #"Filtered for Left"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Combined Table",{"Date", "Context", "Attendee", "Joined", "Left"})
in
    #"Removed Other Columns"

 

It will get you to here.2020-04-16 13_05_40-Untitled - Power Query Editor.png

The problem is there is no good way immediately apparent for PQ to know you want row 6 to be combined with row 1, and 7 with 4.

 

I'd honestly look at this from another angle. Why are you wanting to pivot the Times? Why not leave them in their normalized format and construct your measures to filter based on Join or Leave? In other words, what is your end goal, and let's see if we can get there another way.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors