Power Query -- New column fills down every Nth row of existing column

[gxm204]

Hi, I am working with this dataset that comes in this one-column/unstacked-type format, as shown on left in photo. I think my first step should be to split out a separate column listing location, as shown on right.

Basically that means I need to tell Power Query to fill down based on every third row of an existing column, but I'm not sure how to do that. Any ideas?

Thanks in advance,
George

 

[Alex Blakenburg]

I am sure there are multiple ways to do this but I started with adding a Conditional Column per the images below.

After adding the column, my next steps were.

• Fill down the Site information.
• using the Website column - filter using does not begin with Site
• Move the Site column to the left
• Rename Website to Metric
• Close and Load to

 

[gxm204]

@Alex Blakenburg That is fantastic, thanks so much for the detailed steps.

 

[Alex Blakenburg]

Glad I could help. Appreciate you letting me know it worked for you.

 

[gxm204]

Hi @Alex Blakenburg / thread, I may have spoken too soon about the solution ?... so the problem is that the actual data doesn't start with "Site," they are regular geographic names. Since using that delimiter won't work, I feel like what I need is an index that repeats itself in this case every three rows. Do you have any thoughts? Thanks all in advance.

 

[Alex Blakenburg]

I won't have access to a computer until later today, so I can only tell you what to look for until then.
You should be able to add an "Index" column.
Assuming it let's you pick a starting number pick it so the first Site name is 0.
Then add a column that calculates mod 3 of the index.
If the 1st site was zero and the row numbering is consistent, each site should now have a 0 against it. (Depending on the start no, it could also be 1 or 2, it doesn't matter as long as it's consistent)

 

[gxm204]

Thank you @Alex Blakenburg for pointing me in the right direction.

Yes, I created an index column and was able to take a floor division to create a "grouped" index number. Will remark this one as resolved. Thanks very much for reasoning through it.

 

[Alex Blakenburg]

Glad you got it working. I am in Sydney Australia and couldn't resist taking a peak at 5am and wanted to give you enough to keep you going.

I think you ended it up doing it slightly differently but here is one way of doing.
I haven't done the clean up steps (Fill down, Delete temporary Index columns, Rearrange the 2 remaining columns, Close and Load to)
Hopefully the below is not too small.

IndexGrouping formula was:- = Number.Mod([Index],3)

 

[gxm204]

@Alex Blakenburg Oh now _that_ is clever. Thank you so much. This is more complete that my solution because it actually encodes that new column with the Website information rather than just an index number.

 

[Alex Blakenburg]

Thank you for the feedback, glad that it was of interest.