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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
JamieAU1980
Frequent Visitor

Power Query SQL ODBC API Date Last 12 months

Hi all

 

Wondering if the community can help me with a query?

 

I am relatively new to using Power BI / Power Query, but have a reasonable knowledge utlising ODBC Microsoft Queries, and creating models / reports from these.

 

I have a large table of data (purchase orders for our company going back 15+ years), and I am trying to import only orders created in the last 12 months, field name is: purchase_order.po_order_date.

 

I have already searched the forum and google, but have not yet found an answer that has work for me. I keep finding references to using GETDATE(), however I haven't had any luck using this in my query. I am wondering if this is because my connnection is via ODBC API specific format like { d 'yyyy-mm-dd' }?

 

If I use a specific date in my query (31/12/2019 in the below), I am able to extract the data table, however I am just unsure of how to amend the details of the date field to allow me to specify last 12 months.

 

SELECT purchase_order.po_order_no, purchase_order.backorder_flag, purchase_order.po_order_date

FROM pronto.purchase_order purchase_order

WHERE (purchase_order.po_order_date>{d '2019-12-31'})

 

I appreciate in advance any assistance anyone can provide

 

Thanks

 

Jamie

4 REPLIES 4
lbendlin
Super User
Super User

Does it have to be ODBC?  If you use the native SQL connector you get all this for free, via query folding.

Thanks for the reply. 

It probably doesn't have to be through ODBC, it's just that that's the connection I know how to use. I don't have more than a basic understanding of sql queries, and so it'll initially be quite a learning curve to get up to speed with this. 

What I've been doing up to this point is seeing up my query through Microsoft query, which then allows me to copy the SQL query string to use in the power query ODBC sql connection. 

yeah, try not to do that. Native connection is quite a bit faster, and with query folding you may not even need to muck about with the SQL - let Power Query do that for you.

Ok, thanks for the suggestion. 

 

I've attempted to set it up just now, and although I get a connection, I suspect there's a permission or something on the server side that is restricting access (see image below). 

 

I will have a chat to our IT team and see if it's something they can resolve.

 

JamieAU1980_0-1628169014303.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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