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
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
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
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
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
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
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
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
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
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.
