Power Query SQL ODBC API Date Last 12 months

[JamieAU1980]

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

[lbendlin]

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

[JamieAU1980]

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. 

[lbendlin]

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.

[JamieAU1980]

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.