How to extract first data entry for each day from time stamped data

D

dci1966

New Member
Joined
Jul 27, 2018
Messages
8
I have a very large amount of data which shows meter reads for every few seconds throughout each day and what I want to do is to extract the first (earliest) data line for each day into a separate table. Someone suggested using the Group by MIN function in Power BI but I can't get it to work properly. Can anyone help please?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Show us some sample data that we can manipulate. Use the XL2BB function on this site to upload your sample data. Do not post a picture as we cannot manipulate data in a picture.
 
Upvote 0
Meter Reads.xlsx
ABCDEFGHIJKLMNOP
1CHP Overall Heat TimeCHP Overall Heat ValueYHZI Total Heat TimeHZI Total Heat ValueYBoiler total gas used TimeBoiler total gas used ValueYPasteuriser heat valve energy TimePasteuriser heat valve energy ValueYPot Ale heat valve TimePot Ale heat valve ValueYD1 heat valve TimeD1 heat valve ValueYD2 heat valve TimeD2 heat valve ValueYD3 heat valve TimeD3 heat valve ValueY
201/02/2022 00:00345701/02/2022 00:001301/02/2022 00:0017450701/02/2022 00:0043729001/02/2022 00:004177201/02/2022 00:0073656301/02/2022 00:0067731201/02/2022 00:00707414
301/02/2022 00:00345701/02/2022 00:011301/02/2022 00:0017450701/02/2022 00:0143729101/02/2022 00:014177301/02/2022 00:0173656301/02/2022 00:0167731201/02/2022 00:01707416
401/02/2022 00:00345701/02/2022 00:021301/02/2022 00:0017450701/02/2022 00:0243729101/02/2022 00:024177301/02/2022 00:0273656301/02/2022 00:0267731201/02/2022 00:02707418
501/02/2022 00:00345701/02/2022 00:031301/02/2022 00:0017450701/02/2022 00:0343729201/02/2022 00:034177401/02/2022 00:0373656401/02/2022 00:0367731201/02/2022 00:03707420
601/02/2022 00:00345701/02/2022 00:041301/02/2022 00:0017450701/02/2022 00:0443729301/02/2022 00:044177401/02/2022 00:0473656401/02/2022 00:0467731201/02/2022 00:04707422
701/02/2022 00:00345701/02/2022 00:051301/02/2022 00:0017450801/02/2022 00:0543729301/02/2022 00:054177501/02/2022 00:0573656401/02/2022 00:0567731201/02/2022 00:05707424
801/02/2022 00:00345701/02/2022 00:061301/02/2022 00:0017450801/02/2022 00:0643729301/02/2022 00:064177501/02/2022 00:0673656401/02/2022 00:0667731201/02/2022 00:06707425
901/02/2022 00:00345701/02/2022 00:071301/02/2022 00:0017450801/02/2022 00:0743729301/02/2022 00:074177601/02/2022 00:0773656401/02/2022 00:0767731201/02/2022 00:07707427
1001/02/2022 00:00345701/02/2022 00:081301/02/2022 00:0017450801/02/2022 00:0843729401/02/2022 00:084177601/02/2022 00:0873656401/02/2022 00:0867731301/02/2022 00:08707429
1101/02/2022 00:00345701/02/2022 00:091301/02/2022 00:0017450801/02/2022 00:0943729401/02/2022 00:094177701/02/2022 00:0973656501/02/2022 00:0967731301/02/2022 00:09707431
1201/02/2022 00:00345701/02/2022 00:101301/02/2022 00:0017450801/02/2022 00:1043729401/02/2022 00:104177701/02/2022 00:1073656501/02/2022 00:1067731301/02/2022 00:10707433
1301/02/2022 00:00345701/02/2022 00:111301/02/2022 00:0017450801/02/2022 00:1143729501/02/2022 00:114177801/02/2022 00:1173656501/02/2022 00:1167731301/02/2022 00:11707435
1401/02/2022 00:01345701/02/2022 00:121301/02/2022 00:0117450801/02/2022 00:1243729501/02/2022 00:124177801/02/2022 00:1273656501/02/2022 00:1267731301/02/2022 00:12707437
1501/02/2022 00:01345701/02/2022 00:131301/02/2022 00:0117450801/02/2022 00:1343729501/02/2022 00:134177901/02/2022 00:1373656501/02/2022 00:1367731301/02/2022 00:13707439
1601/02/2022 00:01345701/02/2022 00:141301/02/2022 00:0117450801/02/2022 00:1443729501/02/2022 00:144177901/02/2022 00:1473656501/02/2022 00:1467731301/02/2022 00:14707440
1701/02/2022 00:01345701/02/2022 00:151301/02/2022 00:0117450801/02/2022 00:1543729501/02/2022 00:154177901/02/2022 00:1573656501/02/2022 00:1567731301/02/2022 00:15707442
1801/02/2022 00:01345701/02/2022 00:161301/02/2022 00:0117450801/02/2022 00:1643729601/02/2022 00:164177901/02/2022 00:1673656501/02/2022 00:1667731301/02/2022 00:16707444
1901/02/2022 00:01345701/02/2022 00:171301/02/2022 00:0117450801/02/2022 00:1743729601/02/2022 00:174177901/02/2022 00:1773656501/02/2022 00:1767731301/02/2022 00:17707446
2001/02/2022 00:01345701/02/2022 00:181301/02/2022 00:0117450801/02/2022 00:1843729701/02/2022 00:184177901/02/2022 00:1873656501/02/2022 00:1867731301/02/2022 00:18707448
2101/02/2022 00:01345701/02/2022 00:191301/02/2022 00:0117450801/02/2022 00:1943729701/02/2022 00:194178001/02/2022 00:1973656501/02/2022 00:1967731401/02/2022 00:19707450
2201/02/2022 00:01345701/02/2022 00:201301/02/2022 00:0117450801/02/2022 00:2043729701/02/2022 00:204178001/02/2022 00:2073656501/02/2022 00:2067731401/02/2022 00:20707452
2301/02/2022 00:01345701/02/2022 00:211301/02/2022 00:0117450801/02/2022 00:2143729801/02/2022 00:214178001/02/2022 00:2173656601/02/2022 00:2167731401/02/2022 00:21707453
2401/02/2022 00:01345701/02/2022 00:221301/02/2022 00:0117450801/02/2022 00:2243729801/02/2022 00:224178001/02/2022 00:2273656601/02/2022 00:2267731401/02/2022 00:22707455
2501/02/2022 00:01345701/02/2022 00:231301/02/2022 00:0117450801/02/2022 00:2343729801/02/2022 00:234178001/02/2022 00:2373656601/02/2022 00:2367731401/02/2022 00:23707457
2601/02/2022 00:02345701/02/2022 00:241301/02/2022 00:0217450801/02/2022 00:2443729901/02/2022 00:244178001/02/2022 00:2473656601/02/2022 00:2467731401/02/2022 00:24707459
2701/02/2022 00:02345701/02/2022 00:251301/02/2022 00:0217450801/02/2022 00:2543729901/02/2022 00:254178001/02/2022 00:2573656601/02/2022 00:2567731401/02/2022 00:25707461
2801/02/2022 00:02345701/02/2022 00:261301/02/2022 00:0217450801/02/2022 00:2643729901/02/2022 00:264178001/02/2022 00:2673656601/02/2022 00:2667731401/02/2022 00:26707463
2901/02/2022 00:02345701/02/2022 00:271301/02/2022 00:0217450801/02/2022 00:2743730001/02/2022 00:274178001/02/2022 00:2773656601/02/2022 00:2767731401/02/2022 00:27707465
3001/02/2022 00:02345701/02/2022 00:281301/02/2022 00:0217450901/02/2022 00:2843730101/02/2022 00:284178101/02/2022 00:2873656601/02/2022 00:2867731401/02/2022 00:28707467
3101/02/2022 00:02345701/02/2022 00:291301/02/2022 00:0217450901/02/2022 00:2943730101/02/2022 00:294178101/02/2022 00:2973656601/02/2022 00:2967731501/02/2022 00:29707469
3201/02/2022 00:02345701/02/2022 00:301301/02/2022 00:0217450901/02/2022 00:3043730101/02/2022 00:304178201/02/2022 00:3073656601/02/2022 00:3067731501/02/2022 00:30707471
3301/02/2022 00:02345701/02/2022 00:311301/02/2022 00:0217450901/02/2022 00:3143730201/02/2022 00:314178201/02/2022 00:3173656601/02/2022 00:3167731501/02/2022 00:31707473
3401/02/2022 00:02345701/02/2022 00:321301/02/2022 00:0217450901/02/2022 00:3243730201/02/2022 00:324178301/02/2022 00:3273656701/02/2022 00:3267731501/02/2022 00:32707474
3501/02/2022 00:02345701/02/2022 00:331301/02/2022 00:0217450901/02/2022 00:3343730201/02/2022 00:334178301/02/2022 00:3373656701/02/2022 00:3367731501/02/2022 00:33707476
3601/02/2022 00:02345701/02/2022 00:341301/02/2022 00:0217450901/02/2022 00:3443730201/02/2022 00:344178401/02/2022 00:3473656701/02/2022 00:3467731501/02/2022 00:34707478
Feb - April
 
Upvote 0
Are you looking to do this for each type of Heat Value or just one in particular?
 
Upvote 0
Here is a Power Query (Mcode) solution. Two queries are created and then merged.
The first Query
l
Power Query: 
et
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CHP Overall Heat Time", type datetime}, {"HZI Total Heat Time", type datetime}, {"Boiler total gas used Time", type datetime}, {"Pasteuriser heat valve energy Time", type datetime}, {"Pot Ale heat valve Time", type datetime}, {"D1 heat valve Time", type datetime}, {"D2 heat valve Time", type datetime}, {"D3 heat valve Time", type datetime}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"CHP Overall Heat Time", "CHP Overall Heat ValueY", "HZI Total Heat Time", "HZI Total Heat ValueY", "Boiler total gas used Time", "Boiler total gas used ValueY", "Pasteuriser heat valve energy Time", "Pasteuriser heat valve energy ValueY", "Pot Ale heat valve Time", "Pot Ale heat valve ValueY", "D1 heat valve Time", "D1 heat valve ValueY", "D2 heat valve Time", "D2 heat valve ValueY", "D3 heat valve Time", "D3 heat valve ValueY"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if Value.Is([Value],type number) 
then "Number"
else "Text"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = "Number" then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Up",{{"Custom.1", "Data"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Custom] = "Text")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Value", "Time"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns1", {"Attribute"}, {{"Min Value", each List.Min([Time]), type datetime}})
in
    #"Grouped Rows"

The second query is duplicated from the first but remove the last step

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CHP Overall Heat Time", type datetime}, {"HZI Total Heat Time", type datetime}, {"Boiler total gas used Time", type datetime}, {"Pasteuriser heat valve energy Time", type datetime}, {"Pot Ale heat valve Time", type datetime}, {"D1 heat valve Time", type datetime}, {"D2 heat valve Time", type datetime}, {"D3 heat valve Time", type datetime}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"CHP Overall Heat Time", "CHP Overall Heat ValueY", "HZI Total Heat Time", "HZI Total Heat ValueY", "Boiler total gas used Time", "Boiler total gas used ValueY", "Pasteuriser heat valve energy Time", "Pasteuriser heat valve energy ValueY", "Pot Ale heat valve Time", "Pot Ale heat valve ValueY", "D1 heat valve Time", "D1 heat valve ValueY", "D2 heat valve Time", "D2 heat valve ValueY", "D3 heat valve Time", "D3 heat valve ValueY"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if Value.Is([Value],type number) 
then "Number"
else "Text"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = "Number" then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Up",{{"Custom.1", "Data"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Custom] = "Text")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"
Result of this action

PQ Heat .xlsx
ABC
1AttributeMin ValueData
2CHP Overall Heat Time2/1/2022 0:00:013457
3HZI Total Heat Time2/1/2022 0:00:2113
4Boiler total gas used Time2/1/2022 0:00:01174507
5Pasteuriser heat valve energy Time2/1/2022 0:00:21437290
6Pot Ale heat valve Time2/1/2022 0:00:2141772
7D1 heat valve Time2/1/2022 0:00:21736563
8D2 heat valve Time2/1/2022 0:00:21677312
9D3 heat valve Time2/1/2022 0:00:21707414
Merge1
 
Upvote 0
alansidman said:
Here is a Power Query (Mcode) solution. Two queries are created and then merged.
The first Query
l
Power Query: 
et
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CHP Overall Heat Time", type datetime}, {"HZI Total Heat Time", type datetime}, {"Boiler total gas used Time", type datetime}, {"Pasteuriser heat valve energy Time", type datetime}, {"Pot Ale heat valve Time", type datetime}, {"D1 heat valve Time", type datetime}, {"D2 heat valve Time", type datetime}, {"D3 heat valve Time", type datetime}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"CHP Overall Heat Time", "CHP Overall Heat ValueY", "HZI Total Heat Time", "HZI Total Heat ValueY", "Boiler total gas used Time", "Boiler total gas used ValueY", "Pasteuriser heat valve energy Time", "Pasteuriser heat valve energy ValueY", "Pot Ale heat valve Time", "Pot Ale heat valve ValueY", "D1 heat valve Time", "D1 heat valve ValueY", "D2 heat valve Time", "D2 heat valve ValueY", "D3 heat valve Time", "D3 heat valve ValueY"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if Value.Is([Value],type number)
then "Number"
else "Text"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = "Number" then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Up",{{"Custom.1", "Data"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Custom] = "Text")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Value", "Time"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns1", {"Attribute"}, {{"Min Value", each List.Min([Time]), type datetime}})
in
    #"Grouped Rows"

The second query is duplicated from the first but remove the last step

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CHP Overall Heat Time", type datetime}, {"HZI Total Heat Time", type datetime}, {"Boiler total gas used Time", type datetime}, {"Pasteuriser heat valve energy Time", type datetime}, {"Pot Ale heat valve Time", type datetime}, {"D1 heat valve Time", type datetime}, {"D2 heat valve Time", type datetime}, {"D3 heat valve Time", type datetime}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"CHP Overall Heat Time", "CHP Overall Heat ValueY", "HZI Total Heat Time", "HZI Total Heat ValueY", "Boiler total gas used Time", "Boiler total gas used ValueY", "Pasteuriser heat valve energy Time", "Pasteuriser heat valve energy ValueY", "Pot Ale heat valve Time", "Pot Ale heat valve ValueY", "D1 heat valve Time", "D1 heat valve ValueY", "D2 heat valve Time", "D2 heat valve ValueY", "D3 heat valve Time", "D3 heat valve ValueY"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if Value.Is([Value],type number)
then "Number"
else "Text"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = "Number" then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Up",{{"Custom.1", "Data"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Custom] = "Text")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"
Result of this action

PQ Heat .xlsx
ABC
1AttributeMin ValueData
2CHP Overall Heat Time2/1/2022 0:00:013457
3HZI Total Heat Time2/1/2022 0:00:2113
4Boiler total gas used Time2/1/2022 0:00:01174507
5Pasteuriser heat valve energy Time2/1/2022 0:00:21437290
6Pot Ale heat valve Time2/1/2022 0:00:2141772
7D1 heat valve Time2/1/2022 0:00:21736563
8D2 heat valve Time2/1/2022 0:00:21677312
9D3 heat valve Time2/1/2022 0:00:21707414
Merge1
Click to expand...
Thank you.
When I tried this it only gave me the values for one day and not the whole range?
 
Upvote 0
Your sample data only showed one day. Suggest you update your sample to show additional days so that a solution may be tested with data that actually is representative of your actual data. The solution I provided does exactly what you asked for with the data provided. Remember, we only can work with what you provide us. We cannot see your entire workbook, so you need to be fully transparent in your request in the future.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
DAX median in pivot table for an "almost empty" column
Replies
1
Views
206
Katchi
K
B
  • Question Question
Relation column depends on condition (Power Pivot)
Replies
7
Views
673
jdellasala
J
S
  • Question Question
First Try with Power Query
Replies
2
Views
442
alansidman
A
RICH937
  • Question Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
436
RICH937
RICH937
S
  • Solved
Comparing time stamp values to remove 'duplicate' time entries (rows).
Replies
7
Views
305
felixstraube
F

Forum statistics

Threads
1,223,669
Messages
6,173,696
Members
452,527
Latest member
ineedexcelhelptoday

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top