Excel's Power Query & M language: Change data value in one cell in a filtered row

Occasional Contributor



In the Power Query Editor, I need to change a cost info for one application.  The cell is currently blank. 

My first attempt:  I used the Replace Values function but it ended up filling all cells with null with the same data which is incorrect.  

How would you do to change the data without replacing all cells in the same column with the same number?

Please advise.

2 Replies

@te7037 This requires some guesswork as you don't provide much information, but you could add a custom column and use the following code to check if the product column equals "x" and the cost column equals null then enter a value else take the original cost.

if [Product] = "x" and [Cost] = null then 15 else [Cost]

 "x" and 15 are just examples.


Once you have done this you may remove the original cost column, if you want.

For example: Manchester_142 has a cost of £10; Manchester_142 & Manchester_152 £20.  I need to add both costs from two different rows and assign the info to Manchester_142 & Manchester_152 only.  Final result is £30.

How do we do that?

One possible solution is to replace Manchester_142 with Manchester_142 & Manchester_152 in the data and use the Group By function.