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
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
Helper I
Helper I

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
Solution Supplier
Solution Supplier

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