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