How to pivot

[DennisSchlein]

Hi 


I'm new to power query, and I have a dataset where I have 2 columns I want to pivot and align.

tracking_number	shipment_reference	carrier_name	sender_country_code	recipient_postal_code	recipient_country_code	EventText	event_date_time	OriginCountry	OperationalStatus
00057028800001389353	null	GLS Germany	DK	LOUTDUNAA	IE	Proceeding to carrier facility D11	28-12-2022 06:45:00	DK	CMR_Close
00057028800001389353	null	GLS Germany	DK	LOUTDUNAA	IE	Successfully delivered H10	04-01-2023 11:11:08	DK	Network completion
00057028800001389353	null	GLS Germany	DK	LOUTDUNAA	IE	Arrived at sorting center E11	04-01-2023 06:11:34	DK	Transit
00657028800054010527	null	DHL Parcel Netherlands	NL	8601 CC	NL	Proceeding to carrier facility D11	31-05-2023 15:02:00	NL	CMR_Close
00657028800054010527	null	DHL Parcel Netherlands	NL	8601 CC	NL	Parcel data submitted to carrier C10	01-06-2023 00:19:20	NL	Data
00657028800054010527	null	DHL Parcel Netherlands	NL	8601 CC	NL	Successfully delivered H10	01-06-2023 09:29:47	NL	Network completion
00657028800054010527	null	DHL Parcel Netherlands	NL	8601 CC	NL	Arrived at Parcel Locker G31	01-06-2023 09:29:47	NL	Service completion
00657028800054010527	null	DHL Parcel Netherlands	NL	8601 CC	NL	Handed Over to Delivery Partner E24	01-06-2023 09:11:52	NL	Transit

And I want to pivot Operational Status and EventDateTime (min)
And Operational Satus and EventText

So it lands like this:

tracking_number

shipment_reference

carrier_name

sender_country_code

recipient_postal_code

recipient_country_code

OriginCountry

OperationalStatus

CMR_Close Text

CMR_Close

Network completion Text

Network completion

Transit Text

Transit

Data Text

Data

Service completion Text

Service completion

00057028800001389353

null

GLS Germany

DK

LOUTDUNAA

IE

DK

CMR_Close

Proceeding to carrier facility D11

28-12-2022 06:45:00

Successfully delivered H10

04-01-2023 11:11:08

Arrived at sorting center E11

04-01-2023 06:11:34

00657028800054010527

null

DHL Parcel Netherlands

NL

8601 CC

NL

NL

CMR_Close

Proceeding to carrier facility D11

31-05-2023 15:02:00

Successfully delivered H10

01-06-2023 09:29:47

Handed Over to Delivery Partner E24

01-06-2023 09:11:52

Parcel data submitted to carrier C10

01-06-2023 00:19:20

Arrived at Parcel Locker G31

01-06-2023 09:29:47

Can anyone help?

[DennisSchlein]

Hi Bob

It wont really, unfurnenately 😕

I have a dataset like this:

And I would like it to only be 1 line pr shipment id, with the 1. event_Date_time for each operationnal status:

[BobSled]

I see what you mean.  Based on the data set you have its possible to get it down just to 1 line per shipment_id and filter to the latest status change using the data and time field you have, but you are looking to transpose the data....thats a little more tricky.

That said, Transpose might be the key word your looking for! try googling how to transpose a data set or column in power bi see if the brings anything up

i dont think this link is exactly what you need but i think its heading towards the right direction.

https://databear.com/power-bi-pivot-and-unpivot-columns/#:~:text=Pivot%20and%20Unpivot%20walkthrough....

Good Luck!

[BobSled]

Not sure if this answers your question....try using the "matrix" option instead of the "table" option.

if you are just looking to pivot like you would on an excel that will do the trick works exactly the same.