Forum Discussion

Dmitry_Chukov's avatar
Dmitry_Chukov
Frequent Visitor
9 years ago
Solved

How to insert different values in different rows in one column

Have a table with four columns (each column have about 100 rows), and i need to add a new column  which have a futher structe: 1. Form  the 1 to 4 row value  "Name_1" 2. Form  the 5 to 9 row value ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Steps:

    1. Add a standard 0-based Index column.
    2. Transform - Standard - Modulo 5.
    3. Add a new (temporary) column "Custom" (see code below).
    4. Fill Down [Custom].
    5. Add the NewColumn with null if Index = 0, otherwise the value from [Custom]

    Added to the previous code:

     

        #"Added Index2" = Table.AddIndexColumn(#"Calculated Modulo", "Index", 0, 1),
    #"Calculated Modulo1" = Table.TransformColumns(#"Added Index2", {{"Index", each Number.Mod(_, 5), type number}}),
    #"Added Custom" = Table.AddColumn(#"Calculated Modulo1", "Custom", each if [Index] = 0 then [Data] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "NewColumn", each if [Index] = 0 then null else [Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

Steps:

  1. Add a standard 0-based Index column.
  2. Transform - Standard - Modulo 5.
  3. Add a new (temporary) column "Custom" (see code below).
  4. Fill Down [Custom].
  5. Add the NewColumn with null if Index = 0, otherwise the value from [Custom]

Added to the previous code:

 

    #"Added Index2" = Table.AddIndexColumn(#"Calculated Modulo", "Index", 0, 1),
    #"Calculated Modulo1" = Table.TransformColumns(#"Added Index2", {{"Index", each Number.Mod(_, 5), type number}}),
    #"Added Custom" = Table.AddColumn(#"Calculated Modulo1", "Custom", each if [Index] = 0 then [Data] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "NewColumn", each if [Index] = 0 then null else [Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
Dmitry_Chukov's avatar
Dmitry_Chukov
Frequent Visitor
9 years ago

I have made everything thanks to you. My report now works, like i was planing. Thank you!