Search code examples
powerbim

M query - Edit the value of cell H2 in a table

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.

Solution

  • One way would be:

    In the Power Query Editor:

    1. Add an index column starting from 1 (tab Add Column)
    2. Add a Conditional column: If [Index] = 2 then Jeep else [H] (tab Add Column)
    3. Delete Index column and [H] Column
    4. Rename Custom column to [H].