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,2,3,4,1,2,3,4,1,2,3,4…. and so on.
Step 1 – Add Column tab – Index Column – From 0
Step 2 – Select Index column – Transform tab – Standard – Modulo – put a value of 4 (if you want to generate a sequence of 1,2,1,2,1,2 and so on, put a value of 2)
Step 3 – Select Index column – Transform tab – Standard – Add – put a value of 1
For persons who prefer one step solution, they can use following function after loading the data (only thing you will need to change will be number 4 here)
=Table.TransformColumns(Table.TransformColumns(Table.AddIndexColumn(Source, "Index", 0, 1), {{"Index", each Number.Mod(_, 4)}}), {{"Index", each _ + 1}})
The Excel file related to this Inserting a Sequence can be downloaded from PQ_TT3. Power Query M function reference – PowerQuery M | Microsoft Docs