topic Re: Getting a columns value comprises between two column values of another table in Power Query

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

Anonymous — Mon, 20 Jun 2022 15:57:53 GMT

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

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

Vijay_A_Verma — Mon, 20 Jun 2022 16:20:16 GMT

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

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

Anonymous — Tue, 21 Jun 2022 07:59:23 GMT

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

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

Vijay_A_Verma — Tue, 21 Jun 2022 08:53:21 GMT

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"

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

Anonymous — Tue, 21 Jun 2022 09:42:18 GMT

Hi,

I've obtained this error message:

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

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

Vijay_A_Verma — Tue, 21 Jun 2022 09:59:13 GMT

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

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

Anonymous — Tue, 21 Jun 2022 10:12:42 GMT

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"

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

Vijay_A_Verma — Tue, 21 Jun 2022 12:05:20 GMT

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"

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

Anonymous — Tue, 21 Jun 2022 12:08:55 GMT

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

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

Vijay_A_Verma — Tue, 21 Jun 2022 12:30:44 GMT

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

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

Anonymous — Thu, 23 Jun 2022 07:06:29 GMT

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.

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

Vijay_A_Verma — Thu, 23 Jun 2022 07:46:03 GMT

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]