Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 t...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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]