Forum Discussion

bmacman's avatar
bmacman
Frequent Visitor
3 years ago

Create new column based on value from another column with a specific date range and matching ID

Hi all,

Trying to create a new column in a table. This column needs get data from another table with a time +/- 30 seconds from Table1.

Table1 already has a minute for every minute of the day column

 

In the end I am looking to fill table1 with the IF table2 mode2 has a value at the same time as Table1 then use Table2 data, if not Table2 vlaue exists use Table1 data.

I was thinking the IF statement would work for this but it seems to not recognise the column in Table2.
I cant work in the M query zone as these 2 mode columns are based on IF states already.
I tried the below and it did not work.
NewColumn1 =
IF
('Table2'[minutesAddedToStartDate]>='Table1[minuteAddedToStartDate]+#duration(0,0,0,30)
&&
'Table2'[minutesAddedToStartDate]<'Table1[minuteAddedToStartDate]-#duration(0,0,0,30),
        'Table2[mode2],'Table1[mode1]
        )
 
Any thoughts on what I am doing wrong or how to fix this?
Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi bmacman ，

     

    Suppose we have:

    Table1:

    Table2:

    Please try the follow steps:

    1.Group and Add index column and Merge queries:

    How to create group index with Power Query

    = Table.TransformColumns(#"Grouped Rows",{{"Data",each Table.AddIndexColumn(_,"Index",1,1)}})

    2. expand table2 columns:

     

    3. add a custom column:

     

    if [minutesAddedToStartDate]-[minutesAddedToStartDate.1]<=#duration(0,0,0,30)
or 
[minutesAddedToStartDate]-[minutesAddedToStartDate.1]>=-#duration(0,0,0,30)
then [mode2]
else [mode1]

     

    if [minutesAddedToStartDate]-[minutesAddedToStartDate.1]<=#duration(0,0,0,30)
or 
[minutesAddedToStartDate]-[minutesAddedToStartDate.1]>=-#duration(0,0,0,30)
then [mode2]
else [mode1]

    4. remove columns and the result:

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • bmacman's avatar
      bmacman
      Frequent Visitor
      3 years ago

      Thanks Anonymous  have started this process, moved the IF columns from DAX to M and can start to work this through. For some reason though I am getting an error on the merge

      Not sure what this value is, I have no columns in either query called training anything.

       

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    bmacman , a new column

     

    =

    var _max = maxx(Filter(Table2, 'Table2'[minutesAddedToStartDate] >= 'Table1'[minuteAddedToStartDate]+ #duration(0,0,0,30) && 'Table2'[minutesAddedToStartDate] <'Table1'[minuteAddedToStartDate]-#duration(0,0,0,30) ),
    'Table2'[mode2])

     

    return

    if(isblank(_max), 'Table1'[mode1],_max)

     


    refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
    https://www.youtube.com/watch?v=Wu1mWxR23jU
    https://www.youtube.com/watch?v=czNHt7UXIe8

    • bmacman's avatar
      bmacman
      Frequent Visitor
      3 years ago

      Hi amitchandak, Thanks for the response

      Tried this, gives me this error. which is the # at the start of the #duration

       

      • bmacman's avatar
        bmacman
        Frequent Visitor
        3 years ago

        It actually worked first run then tried to change the name of the merge query table and this error appeared