cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Kudoed Authors