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.
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"Since, none of your time are in the range, hence there are no results, hence Error. I will be displaying null in this case. Use this in the Custom column
= try Table.SelectRows(Fasce_orari, (x)=> x[Ora_min]<=[Ora] and x[Ora_max]>=[Ora]){0}[ID_fascia] otherwise nullHence, your complete query will become
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 try Table.SelectRows(Fasce_orari, (x)=> x[Ora_min]<=[Ora] and x[Ora_max]>=[Ora]){0}[ID_fascia] otherwise null) in #"Aggiunta colonna personalizzata"All times are in a range, e.g. 09:15 is between 08:30 and 09:30.
The query which you have given to me has following times and none of them are in the range