Forum Discussion
Solved
Joing tables with conditions using Power Query is not working as it should.
Hello, experts! I hope you can help me with this issue. Please excuse any language mistakes. I am working with two tables in Power Query. The first table contains assignment activity data f...
give this a try
let assignment = Table.FromList( List.Combine( Table.ToList( assignment_data, (x) => {{x{0}, x{1}, -1, x{3}}} & {{x{0}, x{2}, 100, "none"}} // speeds -1 and 100 are for sorting ) ), (x) => x, Table.ColumnNames(telemetric_data) & {"assignment_id"} ), combine_sort = Table.Sort(telemetric_data & assignment, {"equipment_id(Int64)", "timestamp(Datetime)", "speed"}), fd = Table.FillDown(combine_sort, {"assignment_id"}), filter = Table.SelectRows(fd, (x) => x[speed] <> -1 and x[speed] <> 100) in filterIdea is to combine tables, sort them and fill down assignment_id.
Omid_Motamedise
Super User
Super UserUse Table.Buffer as bellow to improve the execution time of your and also make the filtering condition simple code.
let
X=Table.Buffer(assignment_data),
AddReferenceColumn =
Table.AddColumn(
telemetric_data,
"assignment_id",
(row) =>
Table.First(Table.SelectRows(
X,
each ([equipment_id] = row[equipment_id]
and [start_date] <= row[timestamp]
and [end_date] >= row[timestamp])
))
)
in
AddReferenceColumn
if all the column of table assignment_id are not requiered, beffor using it into Table.Buffer, remove the extra column to save more place for the memory.
If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!
Unfortunately, it didn't work. The speed of loading is at most 9 rows/s, even when buffering the two tables. I'm loading the data from a Dataflow.
