Don't feel like a goose! It's easy to think that OData is the right path for this.
I gave a talk Microsoft Focus 2020 diving deep into OData within Power Automate and I don't currently recommend its use. If you can avoid ODATA you should, for the following reasons:
1. You lose the ability for Flow Checker to catch issues before they happen in production. This means your flows can turn into burning fires more easily.
2. Because of how Power Automate is constructed, it's really hard to get access to the $metadata that you need to properly build ODATA queries. Without this, you really are just guessing at how to write each query. You just can't know what you can and can't use.
3. OData support on the API side is inconsistent. For example, you can't use odata to filter numeric comparisons on excel tables. This means if you're using a lot of connectors your knowledge doesn't transfer well. You have to know through pain what is available and what is not for each connector. (And because Power Automate doesn't actually control the underlying API, that can change without you knowing about it at any time.)
It's safer to stick to Filter Array when you can. You can depend on this to work the way you expect even if it adds another step.
Exceptions:
- D365 CRM has FetchXML Builder which will help you build queries.
- You need to use ODATA when your dataset is so large that you might hit a flow limit, or if you care about speed and it slows your flow down.
- If you're working deep with a specific connector and you can get access to the underlying metadata, odata can be a useful tool and you can develop your queries in a web browser before putting them into a flow.
