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.
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.
Give me your complete query if you are using your own query.
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDTByIjAyMjJR0lYyAOyCwoyAfShgZWBgZKsTr4lBhjVWICUpJTWgJWYghWEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, ID_badge = _t, Impiegato = _t, Ora = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Data", type date}, {"ID_badge", Int64.Type}, {"Impiegato", type text}, {"Ora", type time}}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "Personalizzato", each Table.SelectRows(Fasce_orari, (x)=> x[Ora_min]<=[Ora] and x[Ora_max]>=[Ora]){0}[ID_fascia]) in #"Aggiunta colonna personalizzata"