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 colum...
Need Help
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.