Forum Discussion
Auto replace all values in all columns with specific value
Hello,
I have the following table loaded in Power Query which is updated regularly with changing number of columns in the source Excel spreadsheet. I am trying to find a dynamic way to replace all values in the entire table where it is 0 to null. Is this possible?
Currently, this is the step used but it is not dynamic when the columns change:
= Table.ReplaceValue(Source,"0",null,Replacer.ReplaceValue,{"Week number", "Stock", "Sold", "Returns"})
| Week number | Stock | Sold | Returns |
| 1 | 121 | 89 | 32 |
| 2 | 212 | 200 | 12 |
| 3 | 142 | 0 | 0 |
| 4 | 846 | 845 | 1 |
| 5 | 0 | 500 | 52 |
| 6 | 231 | 0 | 200 |
| 7 | 514 | 51 | 0 |
| 8 | 874 | 551 | 323 |
| 9 | 0 | 84 | 464 |
| 10 | 152 | 54 | 0 |
| 11 | 560 | 0 | 360 |
| 12 | 614 | 614 | 0 |
You can replace
{"Week number", "Stock", "Sold", "Returns"}with
Table.ColumnNames(Source)
1 Reply
You can replace
{"Week number", "Stock", "Sold", "Returns"}with
Table.ColumnNames(Source)