Forum Discussion
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 ...
Steps:
- Add a standard 0-based Index column.
- Transform - Standard - Modulo 5.
- Add a new (temporary) column "Custom" (see code below).
- Fill Down [Custom].
- 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"})
thank you, it works. Could you help? How create a "New column" from the name of comlex (red colour in column Data). Try with LOOKUPVALUE but it does't work.
| NewColumn | Data | |
| 1 | NTS | |
| 2 | NTS | Sales |
| 3 | NTS | Country |
| 4 | NTS | Manager |
| 5 | NTS | City |
| 6 | NTS_P | |
| 7 | NTS_P | Sales |
| 8 | NTS_P | Country |
| 9 | NTS_P | Manager |
| 10 | NTS_P | City |
| 11 | PRU_5 | |
| 12 | PRU_5 | Sales |
| 13 | PRU_5 | Country |
| 14 | PRU_5 | Manager |
| 15 | PRU_5 | City |
Steps:
- Add a standard 0-based Index column.
- Transform - Standard - Modulo 5.
- Add a new (temporary) column "Custom" (see code below).
- Fill Down [Custom].
- 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"})I have made everything thanks to you. My report now works, like i was planing. Thank you!