Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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"
Hi,
I've obtained this error message:
There are not enough elements in the enumeration to complete the operation.
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 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"
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
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.
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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.