locked
Edit the value of cell H2 in a table. RRS feed

  • Question

  • Hello,

    Having imported an excel sheet into power query, I need to tidy it up by changing the value of a particular cell.

    At the moment that cell has a null value, but there are other null values in the same column that I do not want to change. – So I cannot replace all of the null values in that column with another value.

    I also cannot correct that particular cell in the source excel file (there are hundreds of them, which were created before I arrived).

    I basically need some syntax for example that sets the value of cell H2 to “Jeep”, but not to change any other cells.

    Very grateful for any insight.

    Wednesday, October 3, 2018 1:09 PM

Answers

  • Hi there. Using M, you can conditionally replace values based on other values in the same row. If you first add an index column to the table, you can use the index or row number to do the replacement, if that's what's needed.

    • First, add an Index Column
    • Next, do Replace Values on the column containing the null, and replace null with Jeep (note that this will replace all nulls in the column)
    • Modify the M code of the Replace step to be conditional. Something like:
    • = Table.ReplaceValue(#"Added Index", null, each if [Index] = 3 then "Jeep" else [MyColumn], Replacer.ReplaceValue, {"MyColumn"})

    Let me know if you need any help doing this.

    Ehren


    Thursday, October 4, 2018 6:06 PM