JeffGrant
Well-known Member
- Joined
- Apr 7, 2021
- Messages
- 519
- Office Version
- 365
- Platform
- 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.
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"
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"