Transform Data

r.anand14@live.com
Posts: 16
Joined: Wed Nov 04, 2015 6:27 am

Transform Data

Postby r.anand14@live.com » Wed Jul 26, 2017 8:04 am

Hi team,

I'm looking for a help / suggestion to transform rows to columns.

Attached is the sample data set for reference purposes.

Input Source table:
Model No. Type Project Category Count List Units
Dynamic Type1 PRJ1 Category1 25 2500 4.15
Dynamic Type1 PRJ1 Category1 50 20 3.38
Dynamic Type1 PRJ1 Category1 100 25 3.17
Dynamic Type1 PRJ1 Category1 150 30 3.17
Dynamic Type1 PRJ1 Category1 35 3.17
Dynamic Type1 PRJ1 Category1 200 45 8.19
Dynamic Type1 PRJ1 Category1 250 50 9.23

Output source:
Model No. Type Project Category Count1 List1 Units1 Count2 List2 Units2 Count1 List2 Units2 Count3 List3 Units3 Count4 List4 Units4 Count5 List5 Units5 Count6 List6 Units6
Dynamic Type1 PRJ1 Category1 25 2500 4.15 50 20 3.38 100 25 3.17 150 30 3.17 35 3.17 200 45 8.19 250 50 9.23

Thanks in advance.

Kind regards,
Anand

https://1drv.ms/f/s!Aj7kf6Fnqc5SwmD-8Jv-HKxKd9N8

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Transform Data

Postby PhilC » Wed Jul 26, 2017 2:34 pm

Hi, the attached might put you in the right direction.

1. Add an Index Column
2. Add columns for Count, List, Units that will become your column headings, concatenating "Count", "List", "Units" to the [Index]
3. Select each new column in turn (not together) and Pivot them, selecting the associated original column as the Field, and select Do Not Aggregate
4. Select all new columns and use Fill > Up
5. Keep Top Rows, one row only
6. Re-order as required

Cheers
Attachments
PPM Pivot.xlsx
(18.09 KiB) Downloaded 103 times

r.anand14@live.com
Posts: 16
Joined: Wed Nov 04, 2015 6:27 am

Re: Transform Data

Postby r.anand14@live.com » Thu Jul 27, 2017 3:16 am

Hi Phil,

Thank you for having this completed for me and also to give an idea of how to transform data. However, the steps didn't completely provide the desired results with multiple criterion's in the actual file. I solved this using the Table.column function and the delimited with special character to then join tables as one and it worked for me.

Thank you once again.

Kind regards,
A!


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 5 guests

cron