how to pivot several columns like this? TIA!

[Chimichanga2024]

this is my current table:

Shift	Date	Value	Index	Order
6:00 AM-2:00 PM	11/12/2024	Meghan	1	1
6:00 AM-2:00 PM	11/12/2024	2nd shift	2	2
6:00 AM-2:00 PM	11/13/2024	Nancy	3	1
6:00 AM-2:00 PM	11/13/2024	1st shift	4	2
6:00 AM-2:00 PM	11/14/2024	Troy	5	1
6:00 AM-2:00 PM	11/14/2024	2nd shift	6	2
6:00 AM-2:00 PM	11/15/2024	Tracie	7	1
6:00 AM-2:00 PM	11/15/2024	1st shift	8	2
7:00 AM-3:00 PM	11/11/2024	Kelly	9	1
7:00 AM-3:00 PM	11/11/2024	3rd Shift	10	2

And I just cannot transform into this:

Shift	Date	Staff	Shift
6:00 AM-2:00 PM	11/12/2024	Meghan	2nd shift
6:00 AM-2:00 PM	11/13/2024	Nancy	1st shift
6:00 AM-2:00 PM	11/14/2024	Troy	2nd shift
6:00 AM-2:00 PM	11/15/2024	Tracie	1st shift
7:00 AM-3:00 PM	11/11/2024	Kelly	3rd Shift

I was trying to use pivot column function, but when some of the staff and their shifts were showed as error, I simply jusr want to group by column of order. so that shift date

what should I do, any advice will be appreciated!

[mussaenda]

Hi @Chimichanga2024 ,

If you'll remove the index then Pivot the Value according to the Order field without aggregation,  then there will be no error.

Hope this helps

[Chimichanga2024]

Hi, Mussaenda, 

thank you for your reply, the potential issue will be: 

if two person with same date time and shift time, if I use pivot column, then it will be showing error

[Anonymous · Accepted solution]

Hi @Chimichanga2024 ,
Thanks for Omid_Motamedise and ronrsnfld reply.
Here is another way to achieve this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrMyMFBw9NU1AtEBvko6SoaG+oZG+kYGRiZAjm9qekZiHkgUjGN1COowyktRKM7ITCsBscEYtyZjmCa/xLzkSiBtTMAWuAbD4hK4LSYEbDGBaQopygdZYkrAEhNsXjEjYIkpwpLE5MxUIMOcgDWm2PxiAbfGHKrJGEWTIUyTd2pODsgzlnBbCGgwLkpRCIbaYmgAsSYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Shift = _t, Date = _t, Value = _t, Index = _t, Order = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shift", type text}, {"Date", type date}, {"Value", type text}, {"Index", Int64.Type}, {"Order", Int64.Type}}),
    Order1 = Table.SelectRows(#"Changed Type", each [Order] = 1),
    Order2 = Table.SelectRows(#"Changed Type", each [Order] = 2),
    Order2WithPrevIndex = Table.AddColumn(Order2, "PrevIndex", each [Index] - 1),
    Merged = Table.NestedJoin(Order1, {"Index"}, Order2WithPrevIndex, {"PrevIndex"}, "Order2", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Order2", {"Value"}, {"Shift Order"}),
    #"Removed Columns" = Table.RemoveColumns(Expanded,{"Index", "Order"})
in
    #"Removed Columns"

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Chimichanga2024]

Thank you Albert, your method is easier!

I end up revise like this:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Shift", type text}, {"Date", type date}, {"Value", type text}, {"Index", Int64.Type}, {"Order", Int64.Type}}),
Order1 = Table.SelectRows(#"Changed Type", each [Order] = 1),
Order2 = Table.SelectRows(#"Changed Type", each [Order] = 2),
Order2WithPrevIndex = Table.AddColumn(Order2, "PrevIndex", each [Index] - 1),
Merged = Table.NestedJoin(Order1, {"Index"}, Order2WithPrevIndex, {"PrevIndex"}, "Order2", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Order2", {"Value"}, {"Shift Order"}),
#"Removed Columns" = Table.RemoveColumns(Expanded,{"Index", "Order"})
in
#"Removed Columns"

[Omid_Motamedise]

Table.Split is a wounderful functino which can use you, so just copy the below code and past it into advance editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrMyMFBw9NU1AtEBvko6SoaG+oZG+kYGRiZAjm9qekZiHkhUKVaHoGqjvBSF4ozMtBIQG48GY5gGv8S85EoCpsMVGxaXEGW6CUxDSFE+IcNNSHW6KcLwxOTMVALGm+J2uzlUgzGKBkOYBu/UnByE4wkoNi5KUQhGmB4LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Shift = _t, Date = _t, Value = _t, Order = _t]),
    Custom1 = Table.Split(Source,2),
    Custom2 = List.Transform(Custom1, each  Table.Pivot(_, List.Distinct(_[Order]), "Order", "Value")),
    Custom3 = Table.Combine(Custom2)
in
    Custom3

[Chimichanga2024]

Hi Sir, this is the codes I tried to update, but showing error: 

let
// Load data from the Excel table
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

// Change data types of the relevant columns
#"Changed Type" = Table.TransformColumnTypes(Source, {
{"Shift", type text}, {"Date", type date}, {"Value", type text},
{"Index", Int64.Type}, {"Order", Int64.Type}}),

// Add a Custom column with split tables
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Split(Source, 2)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom2", each List.Transform([Custom],each Table.Pivot(_, List.Distinct(_[Order]), "Order", "Value"))),
#"Added Custom3" = Table.AddColumn(#"Added Custom1", "Custom2", each Table.Combine([Custom2]))
in

[ronrsnfld]

I would use a different approach

• Create a duplicate of the Value column but shift it one up
• Remove every other row from the table

No need for your Index and Order columns in your data:

Use this code (Paste into the Advanced Editor):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrMyMFBw9NU1AtEBvko6SoaG+oZG+kYGRiZAjm9qekZinlKsDkGVRnkpCsUZmWkleBQbwxT7JeYlVxKj0LC4hKCpJjDFIUX5+Aw1IcWppghDE5MzU4lRiepWc6hiYxTFhjDF3qk5OZXEKDQuSlEIhpgaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Shift = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shift", type text}, {"Date", type date}, {"Value", type text}}),
    
    #"Add Shifted Value" = Table.FromColumns(
        Table.ToColumns(#"Changed Type")
        & {List.Skip(#"Changed Type"[Value]) & {null}},
        type table[Shift=text, Date=date, Name=text, _Shift=text]),
    
    #"Removed Alternate Rows" = Table.AlternateRows(#"Add Shifted Value",1,1,1)
in
    
    #"Removed Alternate Rows"

Results:

[Chimichanga2024]

pivot columns were showing error, I don't know why, so any advices will be appreciated!