get hourly data from database using ODBC|Power Query|Excel Forum|My Online Training Hub

Hello from France

I am brand new on this forum, and also new on power query.  I have an issue, 2 ideas to solve it, but none of them works properly.

I have an Oracle database connected to a factory supervision, collecting data every 1 to 5 seconds. Due to the large amount of data, connecting directly to the tables without filtering is too long.

So I would like to get hourly data (pick one data every hour or average one hour data) between a start date and a end date, and of course do this directly in the query not to load the full amount of data.

My first method is :

when connecting with an ODBC connection, I directly enter my SQL query , using an aggregate table.

select

                name,  ts, max, avg

from

                aggregates

where

                (name='4.REAC1.K+.PPM.PV')

and

                ts between '01-JAN-18 00:00:00.0' and '24-OCT-18 00:00:00.0'

and

                period=1:00

I created 2 tables linked to Excel cells, to be able to replace the dates by parameters. But when I replace one of the dates by my parameter as below,

let

    Source = Odbc.Query("dsn=IP21 64bits", "select name,  ts, max, avg from aggregates where (name='4.REAC1.K+.PPM.PV') and ts between '" & datedebut &"' and '24-OCT-18 00:00:00.0' and period=1:00 and STEPPED = 1")

in

    Source

 I've got an error.

Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

Strangely, the SQL chain looks the same when using both methods, I do not understand why it doesn't work...

2nd method

directly use Language M to perform the query. But I honestly do not know how to do it, as my know ledge in SQL and in this language is very poor.

I know how to add start date and end date (not done in the code below), but I don't know how to get only hourly data, and not load all the data to transform them after. My basic code is

let

    Source = Odbc.DataSource("dsn=IP21 64bits", [HierarchicalNavigation=true]),

    IP_AIDef_1_Table = Source{[Name="IP_AIDef_1",Kind="Table"]}[Data]

in

    IP_AIDef_1_Table

So any assistance for one of this method is welcome !!

Hope I was clear in my explanations.

regards

Michael