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"})
For the first column, in Power Query:
- Add a custom Index column starting with 4, increment 1.
- Adjust the column name in the generated code to "NewColumn_Name".
- On the Transform tab, Standard - IntegerDivide by 4.
- On the Transform tab, Format - Add Prefix "Name_".
#"Added Index" = Table.AddIndexColumn(ExistingTable, "NewColumn_Name", 4, 1),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"NewColumn_Name", each Number.IntegerDivide(_, 4), Int64.Type}}),
#"Added Prefix" = Table.TransformColumns(#"Integer-Divided Column", {{"NewColumn_Name", each "Name_" & Text.From(_, "en-US"), type text}})
You didn't mention anything about the Data column with Sales, Country, Manger, City, so I don't know what you want with that.
Just in case you want a new column for that as well, you can:
- Add a standard Index column starting with 0.
- Adjust the column name in the generated code to "Data".
- On the Transform tab, Standard - Modulo 4.
- Adjust the generated code, see below.
The first comma in the code below comes after the last line of the previous code.
,
#"Added Index1" = Table.AddIndexColumn(#"Added Prefix", "Data", 0, 1),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index1", {{"Data", each {"Sales","Country","Manager","City"}{Number.Mod(_, 4)}, type text}})
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 |