Generate Rows based off a number field for a owner

Question

I have this data from google. It lists me the owner name and file name and then all the permissions for that file but in a column format. which means that if a file has 345 people its shared to - i have that many columns in my dataset. I simply want to transpose sort but i want it like below:

Owner        File Name.                     permission 1 email.       role

Owner        File Name.                     permission 2 email        role

Owner        File Name.                     permission 3 email        role

Owner2      Next File Name.             permission 1 Email       role

I dont need the domain.

if someone can please help me out - i will attach a sample dataset.

on the data set i only have three permissions but the columns can go all the way up in the hundred depending on file.

Thanks ALL

drivedata122.csv

echuong1 · Answer

I believe this is what you're looking for.

I first used a dynamic select to remove any columns related to domain. From there, I used the transpose to get all of the data into a vertical format, with a group by on owner, name, and permissions. I then had each item of the category in the name and value fields.

I used a text to columns to parse the field names, so I could get both the record ID (in this case 0-2) and the goal field name (email address and role). Once I had both of those, I used a cross-tab to pivot my data to have the email address and role as fields.

I used a select to remove the record ID field. You can also remove permissions if you don't need that.

Hope this helps!

New Workflow1.yxzp

cdata995 · Answer

So there will be a file that has 6 rows with the same file name but different person its shared to ( instead of columns as it is now) and I only need the owner, file name, permission email, permission role

cdata995 · Answer

sorry i dont think im typing it right but this is what i want my file output to look like.

Book3.xlsx