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 reached in column 1 in next step, column 2 starts counting from 1 to 3 and index should increase by 1 while column 1 remain 3.

When 3 is reached in column 2 in next step, column 1 restarts counting from 1 to 3 and index should increase by 1 while column 2 remain 3.

Process continues alternating. Steps are not always from 1 to 3, it could vary for example from 1 - 151 or from 1 - 150 aswell.

 

Please find below the corresponding table:

 

Column 1Column 2Index
101
201
301
312
322
332
133
233
333

 

Anyone got a suggestion for this problem?

Looking forward to any support.

Need Help

2 Replies

  • 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