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

[bmacman]

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?

[v-cgao-msft]

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]

Thanks @v-cgao-msft  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]

@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]

Hi @amitchandak, Thanks for the response

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

[bmacman]

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