Vlookup True equivalent in Power BI

[Lobs]

Hi All, 

For some reason, this has always been a challenge for me and have been bothered about this ever since. Basically i am trying to group a column containing the time we get interactions and i am trying to group them based on a set of intervals list. 

Intervals - this would run until 11:30:59 PM

And here is the column containing the time, that i would like to group based on the intervals shown above. 

Basically, its the vlookup True equivalent in excel. I was able to come up with this but kept on getting an error that it does not support date types. 

Interval (i) =
VAR _Interaction =
    MAX ( NPS[Interaction Time (CST] )
VAR _Interval =
    MAXX (
        FILTER (
            Intervals,
            NPS[Interaction Time (CST] = _Interaction >= Intervals[From]
                && _Interaction <= Intervals[To]
        ),
        [Interaction Time (CST]
    )
RETURN
    _Interval

[Ashish_Mathur]

Hi,

Please share some data and share the expected result.

[mahoneypat · Accepted solution]

You can use List.PositionOf to get XLOOKUP behavior in power query, but in your case it would be simpler to round your times to the nearest half hour with a formula like this.  You could use Number.RoundUp or down if needed too.

= Time.From(Number.Round(Number.From([TimeColumn])*24*2)/(24*2))

Pat

[Lobs]

Thank you, this worked perfectly and just need to round down. Appreciate it. 

Cheers