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

[JeffGrant]

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.

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"

 

[JeffGrant]

Perseverance pays off. I finally figured it out.

 

[Peter_SSs]

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.

 

[JeffGrant]

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

 

[JeffGrant]

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.

Then changing data type to time gives me this.

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.

 

[RoryA]

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.

 

[JeffGrant]

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

the query comes up as a table query:

so that when I replace the fixed time in another query, with the query name, I get this error over and over again

and yet the original query works fine in another workbook fine.

 

[JeffGrant]

Hi Rory - Hold that thought... I just checked the other query and it does not have the date component

 

[RoryA]

You'll need to drilldown at the end of the query so that it returns just a value, not a table.

 

[JeffGrant]

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.