Unpivot issue

[GarryFarrell]

Hi,

I have files that have different data on the rows. See original file format below. I have multiple files loaded in my PBX file so far. I have created a Category column which I want to use as the column headings. My issues is that when I unpivot I never get whay I need. I required the format on the right 'Required Format'.

[Capstone · Accepted solution]

In the Query editor select the Category column, then under the Transform->Any Column sub menu click Pivot Column.

In the dialog box that appears change the Values Column to "Column1" from the drop down box

Click on advanced options and choose "don't aggrgate" from the Aggregate Value Function drop down box.

You should get the desired output.

Abhi

[Capstone · Accepted solution]

In the Query editor select the Category column, then under the Transform->Any Column sub menu click Pivot Column.

In the dialog box that appears change the Values Column to "Column1" from the drop down box

Click on advanced options and choose "don't aggrgate" from the Aggregate Value Function drop down box.

You should get the desired output.

Abhi

[GarryFarrell]

Thank you.

I was using the right click pop-up menu and I could not find the pivot functionality which I needed. However, you answer showed me that it is on the Transform menu and not in the pop-up menu.

[Capstone]

Have edited my post to include the location of the Pivot Column option. Cheers.

[GarryFarrell]

I got the pivot to work. However when there are two sets of data in a file then I get an error "There were too many elements in the enumeration to complete the operation". This happens because there are now duplicates in the Source.Name and the Category columns. I only get the error for file1. I have tried to add an index column which removes the error but the data is then on different rows. I was thinking if instead of an index column maybe a row number for each file might help.

[Capstone · Accepted solution]

Hi,

I think I have solved your problem. Made a video, let me know if it works,

http://www.filedropper.com/groupfrommultipletextfiles

[Anonymous]

Hi,

Could you please share the link (Report Directly). The video link what you sent is not visible.

[GarryFarrell]

Wow. Nice one. Thanks you saved me. Perfect.