Forum Discussion
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 "Name_2"
3. and so on
| NewColumn_Name | Data |
| Name_1 | Sales |
| Name_1 | Country |
| Name_1 | Manager |
| Name_1 | City |
| Name_2 | Sales |
| Name_2 | Country |
| Name_2 | Manager |
| Name_2 | 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"})
4 Replies
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 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"})
