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

JeffGrant

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"
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Good to hear you figured it out.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
 
Upvote 0
Very good point. I wont do that again. However, I was a bit over anxious at the weekend to say I found the solution. Works beautifully in a new work book where the connection queries are determined by PQ to be Time scripts, but in my model, the connections are determined to be Table scripts, which then throws an error of not allowing <= operator on tables. I am pretty sure that is everything to do with the Connection script data being miss-interpreted by excel as a table and not as time. Any suggestions on how to force PQ to determine the cell data type as Time Only?

Thanks Peter
 
Upvote 0
I hope some body can help me on this..This is really confusing me..

In a small table I have this it loads into PQ easy enough.
1700468385707.png

Then changing data type to time gives me this.
1700468452675.png


The M code this creates is:

let
Source = Excel.CurrentWorkbook(){[Name="RaceEndTime"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"End Time", type time}})
in
#"Changed Type"

This works fine in one workbook, but not in another.

Any and all assistance is greatly appreciated.
 
Upvote 0
Your time value has a date part to it (which is why the number is bigger than one). You'd need either to amend the cell to only include the time or extract the time part in your query.
 
Upvote 0
Hi Rory, I have done that so many times over the weekend with...
let
Source = Excel.CurrentWorkbook(){[Name="RaceEndTime"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"End Time", type datetime}}),
#"Inserted Time" = Table.AddColumn(#"Changed Type", "Time", each DateTime.Time([End Time]), type time),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Time",{"End Time"})
in
#"Removed Columns"

to get
1700474650215.png


the query comes up as a table query:
1700474744108.png
so that when I replace the fixed time in another query, with the query name, I get this error over and over again

1700474866729.png


and yet the original query works fine in another workbook fine.
 
Upvote 0
Hi Rory - Hold that thought... I just checked the other query and it does not have the date component
 
Upvote 0
You'll need to drilldown at the end of the query so that it returns just a value, not a table.
 
Upvote 0
Hi Rory, it looks like, that I stripped the date out of the datetime data type in a previous table. so the table that works on in the other workbook only has the time component, which is exactly what you said. I will go back to the drawing board on this one and as usual, be guided by you guys.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Need M-code help, trying to get data from a table online that changes
Replies
4
Views
208
AustSportingPix
A
earthworm
  • Question
Exporting Data from Power Query into Multiple Sheet and merging steps into one.
Replies
3
Views
477
Matt Allington
M
L
  • Question
Help win unpivot super slow query
2
Replies
15
Views
689
labello27
L
D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
546
Matt Allington
M
tbablue
  • Question
Help required understanding how to implement NullSteps
Replies
0
Views
289
tbablue
tbablue

Forum statistics

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

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