Forum Discussion
copying down rows
Could you explain how you managed to get the numbers 1 to 53, how you made each unique combination have a separate number?
excel_learner Well, the answer is actually included in the file. You can look at each of the applied steps.
First add an Index column that numbers each row from 0 to 399. Then add a custom column (I called it WeekNum) with a formula = {1 .. 53 }. Now you can expand the WeekNum column to new rows. This will create 53 rows for each of the 400 original rows.
Now sort the table. First by WeekNum, then by Index. Remove collumns that are no longer needed and reorder the remaining columns to your liking.
Riny_van_Eekelen Apologies I'm new to Power Query please excuse me, This is the steps I see when I open Advanced Editor, I cant see the steps you mentioned in the query. Did you apply those steps before going into Power Query?
let
Source = Excel.CurrentWorkbook(){[Name="tblALB_2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WeekNum", Int64.Type}, {"Area", type text}, {"LDP", type text}, {"Banding", type text}})
in
#"Changed Type"excel_learner No apologies needed. PQ isn't difficult but it takes some time to get used to.
The "Applied steps" are also on the right-hand side of the screen.
Everything is done by clicking on the correct buttons, except for the "Added Custom" step. Perhaps wise to start get familiar with PQ with the following material:
- Thank you mate, this really helped me.
