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
DennisSchlein
Helper II
Helper II

How to pivot

Hi 


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

 

tracking_numbershipment_referencecarrier_namesender_country_coderecipient_postal_coderecipient_country_codeEventTextevent_date_timeOriginCountryOperationalStatus
00057028800001389353nullGLS GermanyDKLOUTDUNAAIEProceeding to carrier facility D1128-12-2022 06:45:00DKCMR_Close
00057028800001389353nullGLS GermanyDKLOUTDUNAAIESuccessfully delivered H1004-01-2023 11:11:08DKNetwork completion
00057028800001389353nullGLS GermanyDKLOUTDUNAAIEArrived at sorting center E1104-01-2023 06:11:34DKTransit
00657028800054010527nullDHL Parcel NetherlandsNL8601 CCNLProceeding to carrier facility D1131-05-2023 15:02:00NLCMR_Close
00657028800054010527nullDHL Parcel NetherlandsNL8601 CCNLParcel data submitted to carrier C1001-06-2023 00:19:20NLData
00657028800054010527nullDHL Parcel NetherlandsNL8601 CCNLSuccessfully delivered H1001-06-2023 09:29:47NLNetwork completion
00657028800054010527nullDHL Parcel NetherlandsNL8601 CCNLArrived at Parcel Locker G3101-06-2023 09:29:47NLService completion
00657028800054010527nullDHL Parcel NetherlandsNL8601 CCNLHanded Over to Delivery Partner E2401-06-2023 09:11:52NL

Transit

 

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

So it lands like this:

 

tracking_numbershipment_referencecarrier_namesender_country_coderecipient_postal_coderecipient_country_codeOriginCountryOperationalStatusCMR_Close TextCMR_CloseNetwork completion TextNetwork completionTransit TextTransitData TextDataService completion TextService completion
00057028800001389353nullGLS GermanyDKLOUTDUNAAIEDKCMR_CloseProceeding to carrier facility D1128-12-2022 06:45:00Successfully delivered H1004-01-2023 11:11:08Arrived at sorting center E1104-01-2023 06:11:34    
00657028800054010527nullDHL Parcel NetherlandsNL8601 CCNLNLCMR_CloseProceeding to carrier facility D1131-05-2023 15:02:00Successfully delivered H1001-06-2023 09:29:47Handed Over to Delivery Partner E2401-06-2023 09:11:52Parcel data submitted to carrier C1001-06-2023 00:19:20Arrived at Parcel Locker G3101-06-2023 09:29:47



Can anyone help?

3 REPLIES 3
DennisSchlein
Helper II
Helper II

Hi Bob

It wont really, unfurnenately 😕

I have a dataset like this:

DennisSchlein_0-1685907124742.png



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

DennisSchlein_1-1685907153687.png

 

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
Frequent Visitor

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.

 

BobSled_0-1685721636447.png

 

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