power query | replace value in 1 column based on content in another column - 1

Hi Nik ,

The first one you want to replace is [Item] as this depends on [Category] being "School Fees", so:

-1- Right-click on a null value in the [Item] column and select 'Replace Values...'. In the 'Replace With' box, type "School Fees". This will auto-generate code for you like this:

-2- Adjust the highlighed section above so it looks like this instead:

-3- Now you've done this bit, you can just right-click on a "School Fees" value in your [Category] column, select 'Replace Values...' again, and replace with "Fees".

Output:

Full example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7OyM/PUXBLTS1W0lFSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Item = _t]),
    repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Item"}),

// Relevant steps from here ---->
    repItem = Table.ReplaceValue(repBlankNull, null, each if [Category] = "School Fees" then "School Fees" else null, Replacer.ReplaceValue,{"Item"}),
    repCategory = Table.ReplaceValue(repItem,"School Fees","Fees",Replacer.ReplaceText,{"Category"})

in
    repCategory

Pete