Forum Discussion
Replace value from row number
Dear all,
If I have a column below:
Col 1
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 2 |
| 2 |
| 2 |
And I want to replace 2 to 10 but only apply from row 3, the result will be like:
Col 1
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 10 |
| 10 |
| 10 |
Value 2 at row number 2 still keeping, only replace for row number greater than 3. Can I do that with Power Query? If yes, please help me explain it. Thank you.
P/s: I knew can do that with add a custom column with conditional. But I want to replace the value to this column because in real data, I have a looooottttss of columns need to do same thing.
I found the solution. Add the index column, then write below code:
= Table.ReplaceValue(#"Added Index",each [Column1], each if [Column1] = 2 and [Index] > 3 then 10 else [Column1],Replacer.ReplaceValue,{"Column1"})
3 Replies
Right click your column > replace values
A dailog box will pop up just enter 2 in the "Values to find" and 10 in the "Replace with"this action will replace all value 2 to 10. My expecting is keeping value 2 at row number 2, only replace from row number 3 to end of column
I found the solution. Add the index column, then write below code:
= Table.ReplaceValue(#"Added Index",each [Column1], each if [Column1] = 2 and [Index] > 3 then 10 else [Column1],Replacer.ReplaceValue,{"Column1"})
