DISCLAIMER: This method uses the internal takeout-pa.clients6.google.com API and is thus UNSUPPORTED and BRITTLE.

This is a custom function that you can paste into advanced editor and then use to get Public Google Drive contents with just the Drive ID and without needing a) any authentication or b) a Google Cloud Project key.

tl;dr List.Contains is slow in Power Query because for each iteration, for example each row in your table you check, it scans the list from the start until it finds a match. Buffering helps so it doesn’t re-evaluate on every iteration, but it still scans each item in the list. A better approach is merging via Table.Join, or best of all (in the right scenario), turn your list into a single record to create a hash map and do near constant-time lookups with Record.Fi eldOrDefault.