Forum Discussion
Getting a columns value comprises between two column values of another table
Hi,
I've posted a Employee_time table with the corresponding values.
I've solved with this function:
(Valore_ora) =>
let Origine = Fasce_orari,
#"Filtra righe" = Table.First(Table.SelectRows(Origine, each [Ora_min] <= Valore_ora and [Ora_max] >= Valore_ora)),
#"Fascia" = try Record.Field(#"Filtra righe", "Fascia") otherwise null
in #"Fascia"
In my employee time table in order to add a new custom column I've invoked the above custom function. I need to get the specific field after the Table.SelectRows statement.
If you can post sample tables here and explain the problem with respect to those tables, giving solution will be easier.
The Employee_times table is the A table:
ID Employee_name Date Time 1 John 20/06/2022 09:15 2 John 20/06/2022 14:05 3 John 21/06/2022 09:05 The Time_ranges table is the B table:
Range_ID Start_time End_time 1 08:30 09:30 2 13:30 14:30 I need to add to the Employee_times table the Range_ID custom column in order to produce a such situation (I need to detect the Range_ID value inside the Time_ranges table checking that Start_time <= Time <= End_time):
ID Employee_name Date Time Range_ID 1 John 20/06/2022 09:15 1 2 John 20/06/2022 14:05 2 3 John 21/06/2022 09:05 1 Thanks
Use this formula in a custom column
= Table.SelectRows(Time_ranges, (x)=> x[Start_time]<=[Time] and x[End_time]>=[Time]){0}[Range_ID]See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8gDUkYG+gZm+kYGRkZAjoGllaGpUqxOtJIRDhWGJlYGEBXGSCoMUc0AqYgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Employee_name = _t, Date = _t, Time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Employee_name", type text}, {"Date", type text}, {"Time", type time}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Range_ID", each Table.SelectRows(Time_ranges, (x)=> x[Start_time]<=[Time] and x[End_time]>=[Time]){0}[Range_ID], type number) in #"Added Custom"Hi,
I've obtained this error message:
There are not enough elements in the enumeration to complete the operation.