Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table of products and the times they were being produced between (Changeover Table). I want to link this with an Output Table to pull in the product that was being produced at the time of the timestamp. However the timestamps in the second table do not typically match any of the timestamps in the Changeover Table.
Some example data is below. I would like to have the Product column populated with 'A'.
Table 1: Changeovers
Product | StartDateTime | EndDateDate |
A | 01/03/2021 09:32:30 | 04/03/2021 21:39:42 |
B | 04/03/2021 21:39:43 | 21/03/2021 00:42:11 |
C | 21/03/2021 00:42:12 | 03/04/2021 20:32:12 |
Table 2: Output
Timestamp | Output since last reading | Product |
02/03/2021 00:10:00 | 10 |
|
02/03/2021 00:20:00 | 13 |
|
02/03/2021 00:30:00 | 9 |
|
Solved! Go to Solution.
Hi @a100
Is it possible to return more than 1 product? Say Timestamp happens to be between Start and End for more than 1 product? In your case, just A, otherwise combine them?
= Table.AddColumn(yourPreviousStep, "Product", each [a=[Timestamp],
b=Table.SelectRows(Changeovers, each [StartDateTime]<=a and [EndDateDate]>=a),
c=Text.Combine(b[Product],",")][c])
Hi @a100
Is it possible to return more than 1 product? Say Timestamp happens to be between Start and End for more than 1 product? In your case, just A, otherwise combine them?
= Table.AddColumn(yourPreviousStep, "Product", each [a=[Timestamp],
b=Table.SelectRows(Changeovers, each [StartDateTime]<=a and [EndDateDate]>=a),
c=Text.Combine(b[Product],",")][c])