Forum Discussion
Remove rows based on category column
Hello, guys.
Need help with some ETL stuff.
I have a table that comes to me bringing the following data:
| Store | Product Department | Store Category | Values |
| 1 | Alpha | A | 12345 |
| 1 | Beta | A | 12345 |
| 2 | Alpha | B | 12345 |
| 2 | Beta | B | 12345 |
| 2 | Caos | B | 12345 |
| 3 | Alpha | C | 12345 |
| 3 | Beta | C | 12345 |
I get this info from a cube that cotains data on all departments for every store, and put it on excel. I cannot do anything with the data before it comes to excel.
I then add it to Power BI and do the ETL process to fix it up.
However, the client is requesting that I filter out "dead" departments in each store category, because it is tainting the whole picture with bad numbers.
How could I do something like this:
DELETE ROW WHERE [Store Category] = B AND [Product Department] IN ("Beta", "Caos")with Power Query? I am trying to figure it out using the documentation, but I am having a lot of difficulty finding the basic functions for Power Query on the microsoft site.
The easiest way is to reformulate your selection criteria to select rows where [Store Category] <> "B" or ([Product Department] <> "Beta" and [Product Department] <> "Caos").
First select [Store Category] <> "B" to create base code:
then adjust the generated code (in the red rectangle in the picture) to:
= Table.SelectRows(#"Changed Type", each [Store Category] <> "B" or ([Product Department] <> "Beta" and [Product Department] <> "Caos"))
3 Replies
The easiest way is to reformulate your selection criteria to select rows where [Store Category] <> "B" or ([Product Department] <> "Beta" and [Product Department] <> "Caos").
First select [Store Category] <> "B" to create base code:
then adjust the generated code (in the red rectangle in the picture) to:
= Table.SelectRows(#"Changed Type", each [Store Category] <> "B" or ([Product Department] <> "Beta" and [Product Department] <> "Caos"))
How do you delete the rows you don't want?
This did filtering the table but when counting rows using measure, it is counting all the rows even those filtered out rows.
