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

[hood2media]

hi,

following is a situation i'm in

1. i hv 2 columns called 'Category' & 'Item';

2. i have a situation where for each condition where Category-column contains "School Fees", each corresponding Item-column ietm is blank;

3. so, i need to replace each blanks in the Item-column with "School Fees"  for each cases where Category-column contains "School Fees";

4. lastly, i need for each cases where Category-column contains "School Fees", it will be replaced with "Fees"

5. finally, you won't have any blanks in the Item-column since they have been replaced with "School Fees" + for the Category column, there won't b "School Fees" anymore since it has also been replaced with "Fees".

i'd appreciate any help to write a power query for this.
tks & krgds, -nik

[hood2media]

many tks, @BA_Pete.

as there r also similar instances where Category = Donations & Items is blank, i have managed to use & modify that power query script you gave & correct the previous errors succesfully.

tks & krgds, -nik

[BA_Pete · Accepted solution]

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