Forum Discussion

SimonSchoutz's avatar
SimonSchoutz
Helper I
2 years ago

Conditional index column

Hello everyone,   i want to add a index column with following conditions:   I have 2 different columns counting upwards, first column 1 counts to from 1 to 3, while column 2 remains 0. When 3 is...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

SimonSchoutz , To achieve this you can use Power Query

Add an Index Column:

In the Power Query Editor, add an index column by clicking on Add Column > Index Column > From 1.
Create Custom Columns:

Create custom columns for Column 1, Column 2, and Index using the following M code.

Column1 =
let
    MaxValue = 3,  -- Change this to your desired maximum value
    Index = [Index] - 1,
    Cycle = Number.IntegerDivide(Index, MaxValue * 2),
    Position = Number.Mod(Index, MaxValue * 2),
    Value = if Position < MaxValue then Position + 1 else MaxValue
in
    Value
 
Column2 =
let
    MaxValue = 3,  -- Change this to your desired maximum value
    Index = [Index] - 1,
    Cycle = Number.IntegerDivide(Index, MaxValue * 2),
    Position = Number.Mod(Index, MaxValue * 2),
    Value = if Position < MaxValue then 0 else Position - MaxValue + 1
in
    Value
 
IndexColumn =
let
    MaxValue = 3,  -- Change this to your desired maximum value
    Index = [Index] - 1,
    Cycle = Number.IntegerDivide(Index, MaxValue * 2)
in
    Cycle + 1
SimonSchoutz's avatar
SimonSchoutz
Helper I
2 years ago

bhanu_gautam :

 

EDIT:

Oh sorry, i think there was a missunderstanding on my side.

I don´t want to create column 1 and 2, they are already existing machine data.

The maximum value in column 1 and 2 can vary depending how long the machine was running.

 

EDIT2:

Okay, i understood, where i have to insert M-Code, but problem still exist.

I don´t want to create a new column 1 and 2, they already exist.

Here is an example of actual machine data:

 

2,1154,9
7,8154,9
12,2154,9
16,6154,9
21154,9
25,4154,9
29,9154,9
34,3154,9
38,7154,9
43,1154,9
47,5154,9
51,9154,9
56,3154,9
60,7154,9
65,1154,9
69,5154,9
73,9154,9
78,3154,9
82,7154,9
87,1154,9
91,5154,9
95,9154,9
100,3154,9
104,7154,9
109,1154,9
113,4154,9
117,9154,9
122,3154,9
126,7154,9
131,1154,9
135,5154,9
139,9154,9
144,3154,9
148,7154,9
153154,9
154,92,3
154,96,9
154,911,4
154,915,9
154,920,3
154,924,7
154,929
154,933,5
154,937,8
154,942,3
154,946,6
154,951,2
154,955,6
154,960
154,964,4
154,968,7
154,973,2
154,977,5
154,982
154,986,3
154,990,8
154,995,2
154,999,6
154,9104,1
154,9108,4
154,9112,9
154,9117,2
154,9121,7
154,9126
154,9130,5
154,9134,9
154,9139,2
154,9143,7
154,9148
154,9152,5
0,3154,6
6,6154,6
10,9154,6
15,4154,6
19,9154,6
24,2154,6
28,7154,6
33154,6
37,5154,6
41,8154,6
46,3154,6
50,7154,6
55154,6
59,4154,6
63,8154,6
68,3154,6
72,6154,6
77,1154,6
81,4154,6
85,9154,6
90,3154,6
94,6154,6
99154,6
103,4154,6
107,9154,6
112,2154,6
116,7154,6
121154,6
125,4154,6
129,8154,6
134,1154,6
138,6154,6
142,9154,6
147,4154,6
151,7154,6
154,80
154,85,7
154,810,1
154,814,6
154,818,9
154,823,4
154,827,8
154,832,3
154,836,6
154,841
154,845,5
154,849,8
154,854,3
154,858,6
154,863,1
154,867,5
154,871,9
154,876,3
154,880,7
154,885,1
154,889,5
154,893,9
154,898,3
154,8102,7
154,8107,1
154,8111,5
154,8115,9
154,8120,3
154,8124,7
154,8129,1
154,8133,5
154,8137,9
154,8142,3
154,8146,7
154,8151,1
154,8154,5
5,4154,5
9,8154,5

 

Kind regards,

Simon