Simulate Excel bulk updates in Query Editor

Learned colleagues,

I have for example an excel spreadsheet (bank transaction export).
Say the file contains 5 columns; Date, Tx Details, Amount, Category, Sub-Category

If for example the ‘Tx Details’ column contained the text either ‘Coles’ or ‘Woolworths’
How can I bulk update
the ‘Category’ column with the text ‘Expenses’ and
the ‘Sub-Category’ column with the text '‘Groceries’

or …… is this type of update better handled elsewhere in PBI?

TIA john

Hi @jmaikido,

This sounds like something a Power Query if-then-else statement should be able to sort easily. This article shows just that in Excel, you’ll find the ribbon looks a bit different but the options are all there in the Power BI Query Editor as well.

https://www.myonlinetraininghub.com/power-query-if-statements

I hope this is helpful.

Hi jmaikido,

Hope you are doing great.

For the above question, you can add custom column for Category and Sub-Category.

Below is just a highlevel idea on how to get the result.

Category: if [Tx Details] = “Coles” or [Tx Details] = “Woolworths” then “Expenses” else null
Sub-Category: if [Tx Details] = “Coles” or [Tx Details] = “Woolworths” then “Groceries” else null

Folks,
Thanks for your collective code and reference.
Regards, John