• 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

Need only the last row for the query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Need only the last row for the query|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 QueryNeed only the last row for the quer…
sp_PrintTopic sp_TopicIcon
Need only the last row for the query
Avatar
Gopalakrishnan M
Member
Members
Level 0
Forum Posts: 18
Member Since:
April 2, 2021
sp_UserOfflineSmall Offline
1
April 11, 2021 - 4:45 pm
sp_Permalink sp_Print sp_EditHistory

Good day Mynda,

Have one more query or help.

Reference is made to the sheet that you had created in the second post of the earlier thread, where I had required the data to be available for specific time units which is so useful.

On the same sheet where you have modified the Query code (if I may call it, in advanced editor), I would want to know how to get the last line of data for each of the symbols (scrips) at any point of time.

I want to see the last update or in other words, I would like to see the latest data only for each of these symbols in power query.  For e.g. if the current time is 9:30 AM, then in the power query I should see the data as below :

SymbolDateTimeOpenHighLowClose

ACC 09-04-2021 09:30:00 1991.45 2022.85 1987.2 2010.95
ADANIENT 09-04-2021 09:30:00 1172.05 1192 1171.5 1181.1
ADANIPORTS 09-04-2021 09:30:00 828.85 835.95 827 830.65
AMARAJABAT 09-04-2021 09:30:00 825.6 826.1 819.3 824
AMBUJACEM 09-04-2021 09:30:00 324.2 329.9 322.6 327.6
APOLLOHOSP 09-04-2021 09:30:00 3132.4 3132.4 3115.4 3122.45
APOLLOTYRE 09-04-2021 09:30:00 225.8 226.15 225 225.95
ARVIND 09-04-2021 09:30:00 67.85 68.3 67.65 67.9
ASHOKLEY 09-04-2021 09:30:00 123.4 124.2 123.1 123.65
ASIANPAINT 09-04-2021 09:30:00 2624.25 2630 2617 2625.05
AUROPHARMA 09-04-2021 09:30:00 917.4 920.45 915.3 917.3
AXISBANK 09-04-2021 09:30:00 676.05 679.95 674.7 679.7
BAJAJ-AUTO 09-04-2021 09:30:00 3669 3674.1 3655.1 3667.1
BAJAJFINSV 09-04-2021 09:30:00 9637.2 9672 9615.2 9623.95
BAJFINANCE 09-04-2021 09:30:00 4942.55 4954 4935 4940.3
BALKRISIND 09-04-2021 09:30:00 1656.65 1666.35 1652.15 1663.6

 

Here is the code you had put in the query :

let
Source = Excel.CurrentWorkbook(){[Name="Data_Between_Dates"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Symbol", type text}, {"Date", type date}, {"Time", type time}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Day", each if [Date] = #date(2021, 4, 1) then "Today"&Time.ToText([Time]) else if [Date] = #date(2021, 3, 31) then "Yesterday"&Time.ToText([Time]) else "Prior"),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Day] = "Today9:15 AM" or [Day] = "Today9:30 AM" or [Day] = "Today9:45 AM" or [Day] = "Yesterday3:15 PM" or [Day] = "Yesterday3:30 PM")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Day"})
in
#"Removed Columns"

Attaching the same sheet that you had created in the earlier thread.

Thank you in advance,

Warm Regards
GK

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
April 12, 2021 - 8:53 am
sp_Permalink sp_Print

Hi GK,

You can extract the Latest Date from the Data_Between_Dates query and then use that to filter the data in the Data_Latest_Date table. See file attached.

Mynda

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

Hello Mynda,

I guess I probably failed explaining myself clearly.

The rows keep changing throughout the day during the market hours from 9:15 AM to 3:30 PM.  What I need is the last row to be displayed in the power query for each of the symbols (scrips) at any point of time.

For e.g. if the time is 9:45 AM, I want to display only the 9:45 AM row for each symbol as against 9:15 AM, 9:30 AM & 9:45 AM.  So if the time is 11:30 AM generally it displays all time units (15 minute intervals) starting from 9:15 AM, 9:30 AM, 9:45 AM, 10:00 AM... until 11:30 AM, but I just need to see only 11:30 AM data in the power query against each symbol as per the snapshot in my earlier note.

Let me know and thanks for your quick response.

Regards
~GK

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
April 12, 2021 - 6:03 pm
sp_Permalink sp_Print

I believe my answer solves this problem because it finds the last date and time in the data set and then filters based on that. As you refresh the data, the last date and time will also update accordingly. Please test it.

Mynda

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

Thanks Mynda, I believe as before I just glanced at the excel sheet and replied back without checking the queries window.

My apologies.  I shall check it and revert to you soon.

Many thanks for your continued assistance.

 

Warm Regards
~GK

Avatar
Gopalakrishnan M
Member
Members
Level 0
Forum Posts: 18
Member Since:
April 2, 2021
sp_UserOfflineSmall Offline
6
April 13, 2021 - 1:43 am
sp_Permalink sp_Print

Hi Mynda,

Just to clarify, I am looking for specific time rows (date will always be current day). So on the given day, at any point in time, I should always get the last row (which would be the latest time for the day) displayed against each symbol/scrip.  I believe you are looking at the date/day, which always will remain the current day.

I am attaching a new sheet to avoid this confusion.  I am quoting from the previous thread on what I am looking for

Quote

The rows keep changing throughout the day during the market hours from 9:15 AM to 3:30 PM.  What I need is the last row to be displayed in the power query for each of the symbols (scrips) at any point of time.  For e.g. if the time is 9:45 AM, I want to display only the 9:45 AM row for each symbol as against 9:15 AM, 9:30 AM & 9:45 AM.  So if the time is 11:30 AM generally it displays all time units (15 minute intervals) starting from 9:15 AM, 9:30 AM, 9:45 AM, 10:00 AM... until 11:30 AM, but I just need to see only 11:30 AM data in the power query against each symbol as per the snapshot in my earlier note.

Unquote

For e.g. today that is 12th April at 10:45 AM I would normally be looking at the data like this :

SymbolDateTimeOpenHighLowClose

ACC 12-04-2021 09:15:00 1934.95 1934.95 1878 1886.5
ACC 12-04-2021 09:30:00 1886.4 1895 1860.35 1895
ACC 12-04-2021 09:45:00 1895 1909.95 1892.1 1906.75
ACC 12-04-2021 10:00:00 1905.5 1909.8 1898 1904.45
ACC 12-04-2021 10:15:00 1904.45 1907.55 1900.05 1904.8
ACC 12-04-2021 10:30:00 1904.8 1910.1 1900 1905
ACC 12-04-2021 10:45:00 1905 1918.5 1900 1917
ADANIENT 12-04-2021 09:15:00 1177.4 1177.4 1121.15 1129
ADANIENT 12-04-2021 09:30:00 1128.3 1150 1123.2 1147.55
ADANIENT 12-04-2021 09:45:00 1147.7 1153.05 1140.2 1148.2
ADANIENT 12-04-2021 10:00:00 1148.45 1149.2 1134 1143.25
ADANIENT 12-04-2021 10:15:00 1144.05 1144.25 1130.1 1132.1
ADANIENT 12-04-2021 10:30:00 1132.95 1133 1115.05 1121.4
ADANIENT 12-04-2021 10:45:00 1120.8 1132 1113.8 1131.95
ADANIPORTS 12-04-2021 09:15:00 809.9 809.9 770 777.1
ADANIPORTS 12-04-2021 09:30:00 776.5 785 772 782
ADANIPORTS 12-04-2021 09:45:00 782.35 788.4 780.75 786.9
ADANIPORTS 12-04-2021 10:00:00 786.95 788.15 778.95 783.9
ADANIPORTS 12-04-2021 10:15:00 784 784 775.95 778.9
ADANIPORTS 12-04-2021 10:30:00 779 779 767.3 772.5
ADANIPORTS 12-04-2021 10:45:00 772.3 777.4 770.2 773.75
AMARAJABAT 12-04-2021 09:15:00 810 811 796.2 804.35
AMARAJABAT 12-04-2021 09:30:00 804.35 806.8 798.05 803.15
AMARAJABAT 12-04-2021 09:45:00 803.6 813 802.45 811.9
AMARAJABAT 12-04-2021 10:00:00 811.95 813.8 806 809.45
AMARAJABAT 12-04-2021 10:15:00 809.6 809.6 805 805.2
AMARAJABAT 12-04-2021 10:30:00 805.8 808.2 800.6 804.05
AMARAJABAT 12-04-2021 10:45:00 804.05 807 800.5 805.95
AMBUJACEM 12-04-2021 09:15:00 310.9 310.9 303.65 304.95
AMBUJACEM 12-04-2021 09:30:00 304.7 306.4 299.55 306.35
AMBUJACEM 12-04-2021 09:45:00 306.5 308.5 306.25 308.3
AMBUJACEM 12-04-2021 10:00:00 308.15 309.5 306.7 309
AMBUJACEM 12-04-2021 10:15:00 308.95 309.45 307.65 309.35
AMBUJACEM 12-04-2021 10:30:00 309.2 310.8 308.85 310.2
AMBUJACEM 12-04-2021 10:45:00 310.2 312.2 308.25 311.8

What I want to see is this (at 10:45 AM) :

SymbolDateTimeOpenHighLowClose

ACC 12-04-2021 10:45:00 1905 1918.5 1900 1917
ADANIENT 12-04-2021 10:45:00 1120.8 1132 1113.8 1131.95
ADANIPORTS 12-04-2021 10:45:00 772.3 777.4 770.2 773.75
AMARAJABAT 12-04-2021 10:45:00 804.05 807 800.5 805.95
AMBUJACEM 12-04-2021 10:45:00 310.2 312.2 308.25 311.8

Let me know Mynda, and sorry for the repeated notes from my end.

~GK

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
April 14, 2021 - 9:20 am
sp_Permalink sp_Print

Hi GK,

I don't think you've tested my solution. If you have and it's returning something different to what you want, then please provide a file illustrating this.

The Latest Date query finds the maximum date and time from your data. It is not connected to today's date or time. Therefore I believe it does what you want.

Mynda

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

Hi Mynda,

I understand, but some strange reason it is fizzling me out, not able to get what you did in the sheet.

Attaching the same sheet that you had created, only thing I did was to load the source data in the sheet which was not present earlier. Rest all was untouched.

Ideally I should get 13th April 2021 15:15 hours data, but not seeing it.

Hope you will figure out where I am going wrong with all this.

Appreciate your patience here Mynda.

Regards,
~GK

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
April 14, 2021 - 7:39 pm
sp_Permalink sp_Print

Hi GK,

I think what isn't clear is whether you want a separate query that isn't filtered to only include the times you originally requested i.e. 9:15 AM, 9:30 AM, 9:45 AM, 3:15 PM and 3:30 PM. If that's the case, then simply delete the Filtered Rows step from the Data_Latest_Date query. It will then return the latest date/time from the whole dataset.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Gopalakrishnan M
Member
Members
Level 0
Forum Posts: 18
Member Since:
April 2, 2021
sp_UserOfflineSmall Offline
10
April 15, 2021 - 6:56 pm
sp_Permalink sp_Print

Beautiful Mynda... been banging my head for days now to get this sorted... finally got it working as i am working on a completely separate sheet and that is where the whole confusion was as the data everything else is different.... guess I have gotten into analysis paralysis syndrome... anyways... all is good... thanks to you... Probably they should include rating system... straight 5 stars to you... thank you and god bless.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Scott Miller, Roy Lutke, Jeff Krueger, Kylara Papenfuss, Ivica Cvetkovski
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

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