New index based on another index

[Anonymous]

Hello,

I have a table that is similar to this one but with more rows (in the thousands), I am trying to create a new index from a previous index as follows. The old index and category is data i have and the new index is the column I am trying to generate:

old index	Category	new index
1	FIFO1_1	1
2	FIFO1_1	2
3	FIFO1_1	3
4	FIFO1_1	13
5	FIFO1_1	14
6	FIFO1_1	15
1	FIFO1_2	4
2	FIFO1_2	5
3	FIFO1_2	6
4	FIFO1_2	16
5	FIFO1_2	17
6	FIFO1_2	18
1	FIFO1_3	7
2	FIFO1_3	8
3	FIFO1_3	9
4	FIFO1_3	19
5	FIFO1_3	20
6	FIFO1_3	21
1	FIFO1_4	10
2	FIFO1_4	11
3	FIFO1_4	12
4	FIFO1_4	22
5	FIFO1_4	23
6	FIFO1_4	24

So, the logic here is that I want to have a window of a specific number (in this example it's 3) and then starting from the first category FIFO1_1 I want to start the index at 1 until 3. Then I will jump to the second category which is FIFO1_2 with 4,5,6 and then to FIFO1_3 and so on. When I reach the last category FIFO1_4 , I want to start again at the first category FIFO1_1 with the last value I reached which was 13 and repeat the same process. In the real data I have 7 categories and each have 11520 rows. I will appreciate any help.

[Ahmedx]

pls try in dax

[Anonymous]

I mean my data has 7 different categories (instead of 4) and for every category there are 11520 rows (instead of 6) and i want the window to be 16 (instead of 3). How can I change the numbers to follow your logic?

[Ahmedx]

you need to change the condition in the flag column and in the "column" column)
post an example I'll show you it can be done

[Anonymous]

Can I ask you how did you get the 13 and 12 in the dax?

[Ahmedx]

pls try cod in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxDYAwEATBXj524ruDEiwRUcDLov8uIEPacJLprlmj1rXu+czao0uw4cAHfML/L/zCL/zCL/zCb/zGb/zGb/zGH/zBH/zBH/yf9ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"old index" = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"old index", Int64.Type}, {"Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Flag", each if [old index]<=3 then 1 else 13),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Flag"}, {{"Count", (x) => if x[Flag]{0}=1 then  Table.AddIndexColumn(x,"rx",1,1) else Table.AddIndexColumn(x,"rx",13,1)

}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"old index", "Category", "Index", "Flag", "rx"}, {"old index", "Category", "Index", "Flag", "rx"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Flag"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

[HotChilli]

Duplicate the query twice. 

In the first one, filter to keep 'old index' = 1 or 2 or 3 (from the column header)

Then add index column.

In the second one, filter to keep 'old index' = 4 or 5 or 6 (from the column header)

Then add index column (starting at 13, - it's a parameter in the function

--

Append the 2 queries together