Forum Discussion

cousinitt13's avatar
cousinitt13
Icon for Helper I rankHelper I
2 years ago
Solved

Power Query: Add column from another table where date is between beg and end time

Hello,
I struggle with the following code

 

= Table.AddColumn(
    fact_mach_speed_TEST, 
    "stoptransid", 
    (Data) => Table.SelectRows(
        fact_downtime, 
        each [sk_pk_machine_rewinder] = Data[sk_pk_machine_rewinder] 
             and [begtime] <= Data[reptime] 
             and [endtime] >= Data[reptime]
    ), 
    Value.Type(fact_downtime)
)

 

I have two tables, first one is a speedtable with "reptime" and "sk_pk_machine_rewinder" - key

second one is a downtime table with ame "sk_pk_machine_rewinder" - key and "begtime" an "endtime" 



I'd like to add a column [stoptransid] (from the downtime table) in the speedtable, where the [reptime] is between the "begtime" and "endtime" from the downtime table and the key is the same. 

who can help me ? 🙂

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    Hello, cousinitt13 

     

        Table.AddColumn(
       fact_mach_speed_TEST, "stoptransid",
       (x) => Table.SelectRows(
           fact_downtime, 
           (w) => 
            w[sk_pk_machine_rewinder] = x[sk_pk_machine_rewinder] and 
            x[reptime] >= w[begtime] and 
            x[reptime] <= w[endtime]
       ){0}[stoptransid]?
    )

     

1 Reply

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    Hello, cousinitt13 

     

        Table.AddColumn(
       fact_mach_speed_TEST, "stoptransid",
       (x) => Table.SelectRows(
           fact_downtime, 
           (w) => 
            w[sk_pk_machine_rewinder] = x[sk_pk_machine_rewinder] and 
            x[reptime] >= w[begtime] and 
            x[reptime] <= w[endtime]
       ){0}[stoptransid]?
    )