Forum Discussion
power query | replace value in 1 column based on content in another column - 1
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
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 repCategoryPete
2 Replies
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 repCategoryPete
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
