Forum Discussion
Getting a columns value comprises between two column values of another table
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
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.
11 Replies
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"
