• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / get hourly data from database using ODBC|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower Queryget hourly data from database using…
sp_PrintTopic sp_TopicIcon
get hourly data from database using ODBC
Avatar
michael BOUVIER

New Member
Members
Level 0
Forum Posts: 2
Member Since:
November 26, 2018
sp_UserOfflineSmall Offline
1
November 26, 2018 - 5:18 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
November 26, 2018 - 1:38 pm
sp_Permalink sp_Print

Hi Michael,

Great to see you're giving Power Query a try.

I recommend method 2 because this will be more efficient for Power Query. Once you have loaded your SQL table into Power Query (it only loads a preview) you can click on the time column filter buttons and filter for time between specific days/hours. Note: you may need to set the data type (Home tab) for the columns containing your dates and or time to date/time first.

Once you have your data filtered you can 'Group By' (Home tab) the days/hours to get an average before loading the final query into Excel or the Data Model.

Mynda

Avatar
michael BOUVIER

New Member
Members
Level 0
Forum Posts: 2
Member Since:
November 26, 2018
sp_UserOfflineSmall Offline
3
November 27, 2018 - 1:58 am
sp_Permalink sp_Print

Hi Mynda

and thank you very much for your help and quick answer. I managed to get the preview as you explained, and then filter with the dates. At that point, here is my first question : this will automatically decrease the number of data retrieved by the query, right ? Query will not retrieve all the data and then filter them, but only pick the desired data ?

Then I tried to make the "group by" command. But I had an error, "error occurred but no ODBC error information was available". I also didn't see any criteria for the "group by" : how the system will know that I want to group data by hour, or minutes, or days ? Sorry for my silly questions, as I said I'm a newbie in SQL...

But I understand the principles of what you explained, which is already excellent ! Thanks !

 

regards

Michael

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
November 27, 2018 - 7:56 am
sp_Permalink sp_Print sp_EditHistory

Hi Michael,

Correct, Power Query only imports the data that meets the filter criteria. i.e. you're reducing the data you bring into Excel so that only the data you need to work with is imported.

In this post I use the Group By tool, so that will show you what the 'Group by' dialog box should look like, but it sounds like it's time to learn Power Query properly. The forum is a place to help you if you get stuck, as opposed to teaching you step by step how to use Power Query.

It really is an amazing tool and quite quick to learn. You don't need to know or learn SQL to use Power Query. 99% of the features can be achieved using the GUI. If you're interested I have a Power Query course here.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Nada Perovic, Andrew Er
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27279

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.