Duplicate value in new column a certain amount of times.

[Stivl]

I have a dataset where all atributes are in one column, and all values are in a second column.

For my data to work, I now need to add a third column with the ID of those responses.

My data looks like this: (blurred for privacy)

(ID no. 2 is missing but all other numbers are sequential.)

My data has the ID of the response in the attribute field, followed by 21 lines of data before going on to the next response.

How do I create a third column of data that includes the relevant ID? How can I get this ID duplicated into a third column a certain amount of times before picking up the next ID and duplicating again (and so on.) Thank you so much! 

[TomMartens · Accepted solution]

Hey @Stivl ,

using Power Query I would do this like so, two steps

1. Create a third column that checks the if the attribute in col1 eq ID, then [value] else null
   
   
   
   
2. Use the transformation "Fill down" on the new column
   
   

Hopefully, this is what you are looking for.

Regards,

Tom