Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
alicek
Helper II
Helper II

Using SQL query to pre-filter what Socrata OData feed pulls in not working

Hi all, 

 

I want to connect to this Open Data Portal run by Socrata from the EDD: https://data.edd.ca.gov/Industry-Information-/Quarterly-Census-of-Employment-and-Wages-QCEW-/fisq-v9....

 

However, it is so large that I want to specify in the query a filter (?$filter=year >2020). 

 

When I try and use the SODA API endpoint even eithout any filtering language, I get the error below - it won't recognize it as a valid source/connection.

 

alicek_1-1649205574213.png

 

alicek_0-1649205525191.png

When I try and connect using the OData V2 endpoint, it works when I don't add any filtering language and simply add the link as the connection. However, when I try and add the query language to filter, it shows the error below.

alicek_2-1649205634111.png

alicek_3-1649205655130.png


Can anyone help me with this? 

Thank you,

Alice

1 ACCEPTED SOLUTION
alicek
Helper II
Helper II

Oh man, I solved my own problem by learning more SQL than I ever knew! For anyone who searches the same key words because they're having the same problem.... here is the solution:

Connect to the V2 endpoint of the OData feed. Then use the right SQL language (I was not using it!). See bolded below:
= OData.Feed("https://data.edd.ca.gov/OData.svc/fisq-v939?$filter=year gt 2020 and area_name eq 'San Francisco County'", null, [Implementation="2.0"])

View solution in original post

1 REPLY 1
alicek
Helper II
Helper II

Oh man, I solved my own problem by learning more SQL than I ever knew! For anyone who searches the same key words because they're having the same problem.... here is the solution:

Connect to the V2 endpoint of the OData feed. Then use the right SQL language (I was not using it!). See bolded below:
= OData.Feed("https://data.edd.ca.gov/OData.svc/fisq-v939?$filter=year gt 2020 and area_name eq 'San Francisco County'", null, [Implementation="2.0"])

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.