Feb 24 2019 09:26 AM
Dear all,
I have a sample excell Sheet 1 in wich I have a bunch of data collected by an aquisition system. The date a represented as :
In A column: date and time of the sample
In B column value of the sample
I have 1000 records displays. The goal is that within this 1000 records, I need to display in Sheet 2 :
- Column A the date and time of sample at T0+60second of Shhet 1
- Column B the Value of sample at T0+60second of Shhet 1
In other words from the whole set of the 1000 records wich I have collected I need to extract one record every 60s
How cqn I do that with a macor, never doen this before and never use but if you provide sample I could understand
Thanks for help
regards
Feb 24 2019 04:19 PM
Feb 25 2019 01:01 AM
Thnaks for your reply.
Will I have to be able to repeat that steps for all my sample each time ?
the final approach is that :
- I have an acquisition system which run for 30mn and collect a big bunch of data
- For this acquisition set I need to extract every 30s sample measure
Then I need to run the same process for an other set of 30mn acquisition and need to extract the same thing.
At the end I can compare each extracted values for each acquisition set.
I was thinking of using MACRO because the same operation will be done many time.
Does your approach with Power Querry run in similar , meaning that as soon it is created once I can apply it for other set of data without recreating it ?
regards
Feb 25 2019 11:15 AM
Feb 26 2019 04:15 AM
Feb 26 2019 06:36 AM
Hello stephen
I have try what you sugest, I convert my data to table.
my data first column contains hh:mn:ss
how can I apply filter which takes every 30s sample interval ?
at the end I should keep data from 30s, 1mn, 1mn30s, 2mn,.... until the end of recorded data
Thnaks for help
Feb 26 2019 11:54 AM
Add a new column that uses the Text.End(DateTime.ToTExt([Date]),5)) function that returns something like 00 AM, 10 AM, 20 AM, 30 AM etc then filter by 00 AM or 30 AM or 00 PM or 30 PM then remove the column.
I've attached an example
Feb 26 2019 12:02 PM
Feb 26 2019 12:02 PM
Here is another version with a parameter for selecting the table ... hopefully you get the idea
Feb 26 2019 11:54 PM
Hi stephan,
I have download your first sample but how did you generate the sheet2 from sheet1 ?
I cannot see the formular you have applied, can I see it somewhere or how to reproduce ?
Sorry for basic stuff but not so familar with this
Q : I I have a second set of aquisition data how can I apply same filter by default all time without rewriting it ?
Thanks for help
Feb 27 2019 10:57 AM
Try the Second spreadsheet, it allows you to select the table you want, using the parameter.
The Power Query generates the sheet automatically, so all you have to do is query the first table and it will generate the second.
On the right hand side there is a list of actions, each one is applied one after another ... click the cog next to them and it will open up their actions.
Alternately you can look at the whole query by using the Advanced Editor (on home tab, 3rd across in the middle) to see all the actions. One of the great things about open the Advanced Editor is you can copy this into another spreadsheet and paste into a blank query much like code. You would then need to change a couple of things, like the Table name and then click "Close & Load" and the new sheet will be generated.