• 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

Data required in a specific format|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Data required in a specific format|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 QueryData required in a specific format
sp_PrintTopic sp_TopicIcon
Data required in a specific format
Avatar
Gopalakrishnan M
Member
Members
Level 0
Forum Posts: 18
Member Since:
April 2, 2021
sp_UserOfflineSmall Offline
1
April 3, 2021 - 11:33 pm
sp_Permalink sp_Print

Dear Mynda,

Thank you for your continued support.

As suggested, attaching the sample file with the source file in itself as against the data from .csv file.  Hope this is what you meant :).

Source sheet contains all the data for all the symbols for the market hours (India) in 15 minute time intervals starting from 9:15 to 15:30 hours.
What I need is to extract values for the specific time units, specified in the OHLC sheet, from the Source data for the previous day and the current day using power query, which later can be populated under OHLC tab.

Let me know if the file is in order.

Warm Regards
GK

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
April 4, 2021 - 2:35 pm
sp_Permalink sp_Print sp_EditHistory

Hi GK,

Thanks for sharing your file. It's perfect. 

You can add a conditional column that tags the data with "Yesterday&time" and "Today&Time" which you can then use to filter out the data you don't want. From there you can create a PivotTable to extract the data in the layout you want. See file attached.

Note: I've hard coded the dates in the conditional column with #date(... ) but you can replace the hard coded date for today with Date.From(DateTime.LocalNow()) and for yesterday's date use Date.From(DateTime.LocalNow())-1 like so:

=if [Date] = Date.From(DateTime.LocalNow()) then "Today"&Time.ToText([Time]) else if [Date] = Date.From(DateTime.LocalNow())-1 then "Yesterday"&Time.ToText([Time]) else "Prior"

 

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Gopalakrishnan M
Member
Members
Level 0
Forum Posts: 18
Member Since:
April 2, 2021
sp_UserOfflineSmall Offline
3
April 5, 2021 - 3:19 am
sp_Permalink sp_Print

Hello Mynda,

That saved a lot of time Mynda and not to mention the headache that came with it.  So a big thank you for not only resolving but giving it your priority even on a week end, for which I am much obliged.

But I have a query (as always) :).   

In the query, you had hardcoded the date as "each if [Date] = #date(2021, 4, 1) then "Today"&Time.ToText([Time]) else if [Date] = #date(2021, 3, 31).....".  Since my source will be dynamically updated as and when a new day starts, can we change the above code to check only data for the current day and the previous day, as against specifically adding dates.

Thank you Mynda for all your help.

Cheers
GK

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
April 5, 2021 - 3:23 pm
sp_Permalink sp_Print

Hi GK,

I answered this question in the 'note' in my reply.

Mynda

Avatar
Gopalakrishnan M
Member
Members
Level 0
Forum Posts: 18
Member Since:
April 2, 2021
sp_UserOfflineSmall Offline
5
April 5, 2021 - 3:25 pm
sp_Permalink sp_Print

Damn, my bad, Mynda, I was so focused on the excel sheet missed this dearly.

My apologies, Mynda.

That clears the air, so all is well.

Thank you dearly,
~GK

Avatar
Gopalakrishnan M
Member
Members
Level 0
Forum Posts: 18
Member Since:
April 2, 2021
sp_UserOfflineSmall Offline
6
April 7, 2021 - 12:00 am
sp_Permalink sp_Print sp_EditHistory

Hi Mynda,

The pivot that you created requires auto-refresh.  While we have auto-refresh for the query at any specified intervals, to refresh a pivot, from what I understand from the internet, certain lines of codes needs to be added as a VB Module.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

I did try a few other VB Codes as well, that doesnt seem to refresh and always requires manual refresh.  

FYI - I have moved the pivot to a separate sheet now (Pivot), thought that could have been the problem, but still no go.

So I am back to the expert for assistance.

Cheers
GK

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
April 7, 2021 - 12:44 pm
sp_Permalink sp_Print

Hi GK,

You can set PivotTables to refresh at set intervals via the Connection Properties:

Data tab > Queries & Connections > Connections pane > right-click > Properties > Usage tab.

If you require VBA, please start a new question specific to the VBA you require in the VBA forum group.

Mynda

Avatar
Gopalakrishnan M
Member
Members
Level 0
Forum Posts: 18
Member Since:
April 2, 2021
sp_UserOfflineSmall Offline
8
April 7, 2021 - 12:58 pm
sp_Permalink sp_Print

Thanks Mynda.

This only refreshes the power query but I am looking for the pivot that you had created.

Believe VB is the only answer, will create a new thread.

Warm Regards
GK

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
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
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
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.