Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there
I have an issue that I just cannot figure out. I have a table with data similar to the following:
Customer Number | Customer | Type | Date Shipped |
123 | John Doe | Package 1 | 06/01/2022 |
148 | Pearl Jones | Package 1 | 06/02/2022 |
132 | Betty Smith | Package 1 | 06/02/2022 |
148 | Pearl Jones | Package 2 | 06/06/2022 |
123 | John Doe | Package 2 | 06/05/2022 |
109 | Rob Low | Package 1 | 06/07/2022 |
132 | Betty Smith | Package 2 | 06/06/2022 |
109 | Rob Low | Package 2 | 06/12/2022 |
For the sake of simplicity, lets say each customer is shipped two packages. Package 1 ships to the customer first on any given day, and then package 2 is shipped to the customer about 5 days later.
I am trying to create a new table from this data with one line per customer number that creates new columns based on the shipping dates for each package:
Customer Number | Customer | Package 1 Shipped Date | Package 2 Shipped Date |
123 | John Doe | 06/01/2022 | 06/05/2022 |
148 | Pearl Jones | 06/02/2022 | 06/06/2022 |
132 | Betty Smith | 06/02/2022 | 06/06/2022 |
109 | Rob Low | 06/07/2022 | 06/12/2022 |
How would I do this, if possible?
Thank you!!
Solved! Go to Solution.
Try this ....
Edit the table in Power Query
Right click on the Type column / Transform / Trim (to remove the hidden space on row 3!)
Left click on the Type and on the top Transform menu select Pivot Column.
Value Column = Date Shipped
Advanced option = Don't aggregate.
The click ok.
see attached screen prints
Thanks for reaching out for help.
I have helped you, now please help me by giving kudos.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
Try this ....
Edit the table in Power Query
Right click on the Type column / Transform / Trim (to remove the hidden space on row 3!)
Left click on the Type and on the top Transform menu select Pivot Column.
Value Column = Date Shipped
Advanced option = Don't aggregate.
The click ok.
see attached screen prints
Thanks for reaching out for help.
I have helped you, now please help me by giving kudos.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
Excellent, thank you much!
Hi @E_Hutch ,
I'd do this in Power Query.
1) Go to Transform and select Pivot Column
2) then select advance options and click on Don't aggregate:
You'll end up with this:
Best,
Hi @E_Hutch
First click on transform data under the home tab -> click on the Type column and select pivot column in the transform pane.
Set the value column to date shipped and the advanced option to don't aggregate.
Result:
Hold CTRL and select both the Package 1 columns -> Right click and select merge columns
Rename the column to Package 1 Delivery Date and hit okay.
Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |