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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
alicek
Helper III
Helper III

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 III
Helper III

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 III
Helper III

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.