Inserting a sequence saves a lot of headache when mashing up queries. A common task is to insert a sequential query which repeats like 1,1,1,1,2,2,2,2,3,3,3,3 and so on.
Step 1 – Add Column tab – Index Column – From 1
Step 2 – Select the Index column – Transform tab – Standard – Divide – Put value 4. This value 4 can be changed as per your need. If you need to produce a sequence like 1,1,2,2,3,3,4,4 and so on, then put a value of 2. If you want to generate a sequence like 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4 and so on, then divide by 5 instead.
Step 3 – Select the Index column – Transform tab – Rounding – Round Up (if decimal place is asked for use 0 for decimal place).
That's all, you have a sequence of 1,1,1,1,2,2,2,2,3,3,3,3 and so on
For the people who prefer one step solution, just insert following function after you have loaded your table (You will need to change the number 4 only as per your need)
= Table.TransformColumns(Table.TransformColumns(Table.AddIndexColumn(Source, "Index", 1, 1), {{"Index", each _ / 4}}),{{"Index", Number.RoundUp}})
The Excel file related to Inserting a sequence can be downloaded from PQ_TT2 Power Query M function reference – PowerQuery M | Microsoft Docs