cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
E_Hutch
Frequent Visitor

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!!

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

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 !

 

 

speedramps_0-1656536794727.png

 

speedramps_1-1656536814442.png

 

 

View solution in original post

4 REPLIES 4
speedramps
Super User
Super User

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 !

 

 

speedramps_0-1656536794727.png

 

speedramps_1-1656536814442.png

 

 

Excellent, thank you much!

PDG_VL
Regular Visitor

Hi @E_Hutch ,

 

I'd do this in Power Query.

1) Go to Transform and select Pivot Column

PDG_VL_0-1656536585557.png

2) then select advance options and click on Don't aggregate:

PDG_VL_1-1656536649424.png

 

You'll end up with this:

PDG_VL_2-1656536677891.png

 

Best,

 

Seanan
Super User
Super User

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.

NVIDIA_Share_CdGhRe4Mgd.png

 

 

 

 

 

 

 

 

 

 

 

Result: 

NVIDIA_Share_YaPhDph0Vp.png

 

 

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.

NVIDIA_Share_jYgyTgbRei.png

 

 

 

Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors