Forum Discussion
HELP! How can I apply rule based filtering and row deletion in Power Query
In the attached file you can enter data in the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The Query deletes all the rows that contain "Compname" in the "Brand" column. The filter can be expanded in order to delete rows that contain "Compname 1", "Compname 2" or "Compname 3" in the "Brand" column.
The Query adds "Doll" in the "Category added" column if "Barbie" is the sub-category and if the category is null. This is currently done with an IF THEN ELSE statement which can be adapted for 20 sub-categories.
The Query adds the brand Alpha, Beta, Gamma, Delta, Lambda, Epsilon or Zeta in the "Brand completed" column if there is a partial match in the "Product name" column and if the "Brand" column is empty in the blue table. This is currently done with a nested IF THEN ELSE IF statement and can be adapted if there are e.g 30 brands. However there should be a more suitable solution if you have e.g. 400 brands.
In the attached file you can enter data in the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The Query deletes all the rows that contain "Compname" in the "Brand" column. The filter can be expanded in order to delete rows that contain "Compname 1", "Compname 2" or "Compname 3" in the "Brand" column.
The Query adds "Doll" in the "Category added" column if "Barbie" is the sub-category and if the category is null. This is currently done with an IF THEN ELSE statement which can be adapted for 20 sub-categories.
The Query adds the brand Alpha, Beta, Gamma, Delta, Lambda, Epsilon or Zeta in the "Brand completed" column if there is a partial match in the "Product name" column and if the "Brand" column is empty in the blue table. This is currently done with a nested IF THEN ELSE IF statement and can be adapted if there are e.g 30 brands. However there should be a more suitable solution if you have e.g. 400 brands.
- Wow thank you so much, you've saved me days of messing around this is brilliant 😊 I have managed to adapt and custom fit it to my data and it is working perfectly.
Thank you so much!
