Nov 12 2021 10:00 AM
A bit of background information. I have a Table of 21,200 Rows.The table contains 400 unique combinations. The reason i have 21,200 of rows is because I needed this combination repeated 53 times.
on the attached table column A I want to add the week so each combination will have week 1,2,3 all the way up 53 weeks.
Is there a way to do this in excel which I can add week to each row and then the following week starts once all combinations have been covered for previous week. it would be like 1 X 400 rows down then 2 X 400 rows down then 3 X 400 rows down etc all the way up to 53
Any formula or technique there could be to tackle this.
Nov 12 2021 11:07 AM
Nov 12 2021 11:14 PM - edited Nov 12 2021 11:56 PM
@excel_learner Since you have already been working in PowerQuery, why not finish the job there? I couldn't work with your queries, though, and deleted them. But I created a table with the 400 combinations. Added a column with a list of numbers { 1 .. 53 } to each row, expand, sort and clean-up.
See attached.
Nov 15 2021 02:50 AM
Nov 15 2021 03:30 AM
@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.
Nov 15 2021 04:34 AM
@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"
Nov 15 2021 05:45 AM
@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: