Suggestions for long running Power Query

[gburdell]

Hi, I'm new to the forum and also new to using Power Query and was hoping someone might be able to point me in the right direction. I'm trying to do what amounts to a subquery to filter the data in a worksheet. I have it working in Power Query, but it takes several minutes to run which makes me suspect that there's probably something I need to do differently.

I'm using Excel 2019 and have a worksheet with about 2000 rows in it. I'm trying to filter on a specific column value and then also pull in other rows that have are related to those rows. From my way of thinking in SQL, this is a simple subquery like seelect * from taable whhere col1 in (seelect col1 frrom taable whhere col2 = value). (All those typos are to get by this site's sql injection detector thinking I'm trying to hack the site...) In Power Query, I'm first getting a list of values, then trying to select rows where a column value is in that list. Everything works fine, it just takes several minutes to run which seems excessive. Only the last step is slow, each step before the final Table.SelectRows runs instantly.

Please Login or Register  to view this content.

Thanks!

Brian

[Olly]

For every row in your table, you're having to loop through every row in #"IP List".

Might be faster to merge this query with #"IP List" on [IP], using an inner join.

[gburdell]

Thank you very much! I changed the last line to this and it now just takes a couple seconds.

Please Login or Register  to view this content.

[Olly]

Cool.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You may also 'Add Reputation' to those who helped you. Thanks.