Reading start & end time from cell values to filter a table

JeffGrant

Well-known Member
Joined
Apr 7, 2021
Messages
519
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have 3 tables. one called Start Time, the second called End Time and the third which is the source data.

The connections already set up to tables Start Time and End Time
The Data type on all three tables and respective columns is 1.2
In the M code below, the start time and end time is a Filtered Row.

How can I change the filtered row:
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Time Only] >= #time(12,0,0) and [Time Only] <= #time(17, 0, 0)),

so that [Time Only] >= #time(12,0,0)) is read from the Start Time table and [Time Only] <= #time(17, 0, 0) is read from the End Table

I have done this easy enough with text and number values, unfortunately using Time and the input is causing me some considerable grief.

Basically what I need to filter will always be between Start Time and Start Time + 10 minutes. If there is a better way of removing the End Time step, because it is always a fixed value, that would be great.

Thank in advance for your help.

1700291683538.png


let
Source = Excel.CurrentWorkbook(){[Name="Todays_Start_Times"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Track", type text}, {"Race Number", Int64.Type}, {"Start Time", type number}, {"RaceID", Int64.Type}, {"Run Py Script", type any}, {"API Download Time", type number}, {"Run RaceID Time", type number}, {"Run Analysis Time", type number}, {"API Run Upload Time", type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Start Time", type datetime}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Track", "Race Number", "Start Time", "RaceID"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Time Only", each [Start Time]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Time Only", type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Start Time"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Time Only] >= #time(12,0,0) and [Time Only] <= #time(17, 0, 0)),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"Time Only", type number}})
in
#"Changed Type3"
 
Ah, so the drill down parts is also an important part of this?
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Again mate, I am amazed by your knowledge. I have spent hours on this over the last week and pulled my hair out more than once. It is now working, in less than 15 minutes with your guidance. Thank you very very much.
 
Upvote 0
Glad we could help. :)

It's a fairly steep learning curve (and I still have a long way to go) but such a timesaver!
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

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