Transposing columns in a Table with multiple columns

[eWise]

I am in  a bind trying to transpose my table which appears as below and would greatly appreciate the help.  I would like to transpose the StatusName and StatusTime so that the end product is as shown below the main table.  That way, I can calculate the time differences between the status names.

CustomerId	DeliveryId	DocumentId	ShortId	StatusName	StatusTime
2	25	5	CPU6096178	allocated	12/3/2020 20:08
2	25	5	CPU6096178	awaitingPickup	12/3/2020 20:08
2	25	5	CPU6096178	complete	12/3/2020 22:52
2	25	5	CPU6096178	delivered	12/3/2020 22:52
2	25	5	CPU6096178	inProgress	12/3/2020 20:08
2	25	5	CPU6096178	picked	12/3/2020 22:52
2	25	5	CPU6096178	sourcingCourier	12/3/2020 20:08
2	25	60	CPU1265194	allocated	11/10/2020 19:25
2	25	60	CPU1265194	awaitingPickup	11/10/2020 19:25
2	25	60	CPU1265194	complete	11/10/2020 23:36
2	25	60	CPU1265194	delivered	11/10/2020 23:36
2	25	60	CPU1265194	inProgress	11/10/2020 19:25
2	25	60	CPU1265194	inTransit	11/10/2020 22:19
2	25	60	CPU1265194	picked	11/10/2020 22:19
2	25	60	CPU1265194	pickedUp	11/10/2020 22:19
2	25	60	CPU1265194	sourcingCourier	11/10/2020 19:25
62	9	19	CPU4702103	allocated	10/13/2020 22:27
62	9	19	CPU4702103	awaitingPickup	10/13/2020 22:27
62	9	19	CPU4702103	complete	10/14/2020 0:29
62	9	19	CPU4702103	delivered	10/14/2020 0:29
62	9	19	CPU4702103	delivered	10/14/2020 0:32
62	9	19	CPU4702103	dispatched	10/13/2020 22:40
62	9	19	CPU4702103	inProgress	10/13/2020 22:27
62	9	19	CPU4702103	inTransit	10/13/2020 23:31
62	9	19	CPU4702103	picked	10/13/2020 23:31
62	9	19	CPU4702103	pickedUp	10/13/2020 23:31
62	9	19	CPU4702103	sourcingCourier	10/13/2020 22:27

I would like it to appear as below

CustomerId

DeliveryId

DocumentId

ShortId

allocated

awaitingPickup

complete

delivered

inProgress

inTransit

picked

sourcingCourier

PickedUp

2

25

5

CPU6096178

12/3/2020 20:08

12/3/2020 20:08

12/3/2020 22:52

12/3/2020 22:52

12/3/2020 20:08

Null

12/3/2020 22:52

12/3/2020 20:08

Null

2

25

60

CPU1265194

11/10/2020 19:25

11/10/2020 19:25

11/10/2020 23:36

11/10/2020 23:36

11/10/2020 19:25

11/10/2020 22:19

11/10/2020 22:19

11/10/2020 19:25

11/10/2020 22:19

Thank you very much.

[CNENFRNL]

Simply group rows by CustomerId/DeliveryId/DocumentId/ShortId and pivot the derived table; then it's done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdO9boMwEAfwV6mYI3F3BhN7zQswlCnKgIiVWqWADGlfv0YMYEB1jDoARuh3/rg/12tE0Smi1N7G65IXHATH7Gxfyrpuq3JQdztGillMQPBGIOEc3U5/yp9SD7p55Lr6fHbBvGq/uloNyoUkU/LAu6r1tzLrFb8gdZOb9mFU3wevtrObPDBj3z5NZc/oYp9aGd+0HKYCSDxFkazbgzHCZFFIC3x206DAAssWzZSYZNxHnSYFWrdNgWvWzbspm14Pq4lJovDZucmHYNEdoDsJ2dsxH0uI8auYKiQZEAJbRwRinONJmdduIhJYYBkRS5OJgiThk05C/oky8lLdd+VQfeycVgI+7AYz8KScYC6s/SPQZ+dgHoLFurOv0J1gbnZ8+wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerId = _t, DeliveryId = _t, DocumentId = _t, ShortId = _t, StatusName = _t, StatusTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerId", Int64.Type}, {"DeliveryId", Int64.Type}, {"DocumentId", Int64.Type}, {"ShortId", type text}, {"StatusName", type text}, {"StatusTime", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CustomerId", "DeliveryId", "DocumentId", "ShortId"}, {{"ar", each Table.Pivot(_, List.Distinct(#"Changed Type"[StatusName]), "StatusName", "StatusTime")}}),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"allocated", "awaitingPickup", "complete", "delivered", "inProgress", "picked", "sourcingCourier", "inTransit", "pickedUp", "dispatched"}, {"allocated", "awaitingPickup", "complete", "delivered", "inProgress", "picked", "sourcingCourier", "inTransit", "pickedUp", "dispatched"})
in
    #"Expanded ar"

But to my knowledge, the original one-dimensional table functions well in PBI data model in most cases; such transformations sometimes are redundant.

[eWise]

Thank you @CNENFRNL  for your time. This proved to be too complex for me, still a novie in this, and instead went with pivot described below by @sayaliredij .

[sayaliredij · Accepted solution]

Hi,

You can use Pivot table transformation in the power query. 

Select column Status name and Status Time.

Click on Pivot Column

 Select Pivot table properties as follows

You will get the following results

Regards,

Sayali

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

[Prabhakarandev]

We have an option in matrics table, its very easy to transpose the coloumns. no need of complex Pivot options. just a switch button would help to achive thisHighlighted

[Marta987]

So simple, but before your post it took me hours (!) to replace this function using measures. 

[eWise]

Thank you @sayaliredij . It worked just the way I had envisioned.