Getting a columns value comprises between two column values of another table

[Anonymous]

Hi,
inside a PQ query I've two tables, A and B.

Inside the table A I need to add a custom column with the ID of the table B when a certain column of the table A is comprised between the values of two columns of the table B.
I think to use Table.SelectRows and Table.SelectColumns for the custom column to add to table A, but I've a syntax error
when I refer to the column of the table A (e.g. A.[column_to_match]).

Perhaps, I need to use another formulas.

Any suggests to me, please? Thanks

[Vijay_A_Verma]

If you can post sample tables here and explain the problem with respect to those tables, giving solution will be easier. 

[Anonymous]

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

[Vijay_A_Verma]

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"

[Anonymous]

Hi,

I've obtained this error message:

There are not enough elements in the enumeration to complete the operation.

[Vijay_A_Verma]

Give me your complete query if you are using your own query. 

[Anonymous]

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"

[Vijay_A_Verma]

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 null

 Hence, 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"

[Anonymous]

All times are in a range, e.g. 09:15 is between 08:30 and 09:30.

[Vijay_A_Verma]

The query which you have given to me has following times and none of them are in the range

[Anonymous · Accepted solution]

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.

[Vijay_A_Verma]

Great!!! You can use a single statement by merging last two into one.

Table.SelectRows(Origine, each [Ora_min] <= Valore_ora and [Ora_max] >= Valore_ora){0}[Fascia]