power query not returning data preview

[Anonymous]

I have the following PBI file:

https://drive.google.com/file/d/1W7WZTOeFmjQsCsCzONxpJ_pBVroC1qBa/view?usp=sharing

The data is from the CDC (using an OData connector).  The grain is county + report date.  In the data load into PBI, it needs to be filtered by county and report date.  In particular, county needs to be filtered according to some list (i.e., counties in the 'offices' query), and report date needs to be filtered to be within 14 days from the newest report date.  In my final query ('OData call filtered to in-scope counties (2)'), the last transformation step ('Expanded lookup') is not resolving (i.e., no data is returning in the preview).  How can I resolve this?

Solutions that I have tried are:

-using a Web connector (i.e., API call) instead of an OData connector

-duplicating queries (instead of referencing them)

-re-ordering the filter steps (e.g., report data filter before county filter)

[HotChilli · Accepted solution]

I've given it a test (and it's not quick!)

---

I was able to get data back from the 'OData call filtered to in-scope counties (2)' by removing the 'get 14 days ago' query from the load. (disable load)

I suggest hardcoding the date in the (2) query for testing before making a final decision on how to work out the 14 days date.

It's an Odata source and it looks like Power Query is making multiple calls to it (especially with the '14 days query' in there)

--

So, in summary, it's using the right connector but there's too many internal load attempts of the Odata source.