Forum Discussion

E_Hutch's avatar
E_Hutch
Frequent Visitor
4 years ago
Solved

Create New Columns Based on Row Data

Hi there

 

I have an issue that I just cannot figure out. I have a table with data similar to the following:

 

Customer NumberCustomerTypeDate Shipped
123John DoePackage 106/01/2022
148Pearl JonesPackage 1 06/02/2022
132Betty SmithPackage 1 06/02/2022
148Pearl JonesPackage 206/06/2022
123John DoePackage 206/05/2022
109Rob LowPackage 106/07/2022
132Betty SmithPackage 206/06/2022
109Rob LowPackage 206/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 NumberCustomerPackage 1 Shipped DatePackage 2 Shipped Date
123John Doe06/01/202206/05/2022
148Pearl Jones06/02/202206/06/2022
132Betty Smith06/02/202206/06/2022
109Rob Low06/07/202206/12/2022

 

How would I do this, if possible?

 

Thank you!!

Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    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 !

     

     

     

     

     

4 Replies

  • Seanan's avatar
    Seanan
    Solution Supplier
    4 years ago

    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.

  • PDG_VL's avatar
    PDG_VL
    Helper I
    4 years ago

    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,

     

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    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 !