M Code - Remove rows store in new table

Power Query
1

I would like to remove certain rows from a “projects” table based on a project name suffix (_exclude). So the idea is to remove the rows. Then insert those removed rows into a new table.

I also need to remove the rows of other tables for the same projects mentioned above. These however don’t have the project name, instead they have a project id instead.

So, the only way we know which project to remove is via the suffix of the project name and this is only found in the “projects” table.

I would like to remove these rows and place them in a new table using M-Code so there’s no need for manually do so.

Hope you can help!
Thank you!

The attached file is not a representation of the real scenario. It is just something for you to work with, so assume there are thousands of rows and many more rows to remove and place in a new table.

Project.zip (159.7 KB)

2

Hi @powerbideveloper,

See if this meets your requirement.

  • First in the Projects table select rows on this condition Text.EndsWith([Project], “_exclude”, Comparer.OrdinalIgnoreCase)
  • To get all Projects that don’t end with that suffix all you have to do is add the keyword “not” in front of the condition so: not Text.EndsWith([Project], “_exclude”, Comparer.OrdinalIgnoreCase)
  • From the Projects to exclude we can now retrieve a list with ID’s: #“Projects3 excl Proj”[Project ID]
  • With that list you can filter the other tables, like so: List.Contains( #“Excl Project IDs”, [Project ID] ) again using the keyword “not” to reverse selection.

Note. Filter rows as early as possible in your queries especially when query folding is possible…

Here’s your sample file.
eDNA - Projects to exclude.pbix (141.1 KB)

I hope this is helpful

1 Like
3

This is excellent, @Melissa, thank you!

So, after setting this up, how do I ensure that the projects table is refreshed first, since the other tables would need to be able to access the excluded “Excl Project IDs” list when the data is refreshed each day?

4

Hi @powerbideveloper,

The dependency chain will take care of that :wink:

One more note, as you’ll be referencing #“Excl Project IDs” multiple times, I would wrap the last statement in that query in between: List.Buffer() that way it gets loaded fully into memory once no matter how many times you access it.

All the best.

1 Like
5

@Melissa,

It looks like the “Excl Project IDs” list is already wrapped in List.Buffer(). The last statement of which query should be wrapped in List.Buffer()?

#“Project ID1” = List.Buffer( #“Projects3 excl Proj”[Project ID] )

6

:crazy_face:
…I didn’t remember but glad to see I followed my own advice! LOL

2 Likes
7

@Melissa,

Is there a way to have this apply to all tables without having to manually setup each relevant table, perhaps somehow this can be trickled down into related tables? Just a thought!

8

Maybe you can set that up in your back end system?
There is no operation in PQ that will ‘automatically’ perform a transformation over multiple queries, even if you create a function you’ll have to invoke that in each separate query.

1 Like