Create a new column for shift based on datetime column and time from another tab

[Anonymous]

Hi everyone,

I'm still new to PowerBI and I got a task to create a dashboard from excel data. I got 2 sheets from an excel that I already import to PowerBI.


I want to create a column in the Machine_schedule tab that shows the Date and in what shift,

like 2021-07-01 1 or 2021-07-01 2. The 1 2 or 3 are the shift. I already search for some solutions in this forum but still can't find the best way to do this. I still need to learn more about PowerBI for sure.

So is there a way so I can get this done from Power Query? or is it possible to do?

Thank you.

[Fowmy · Accepted solution]

@Anonymous 

You can add a new column to your Machine table as follows:

Column = 
VAR _S = TIME(HOUR(Machine[Start]),MINUTE(Machine[Start]),0)
VAR _E = TIME(HOUR(Machine[End]),MINUTE(Machine[End]),0)
RETURN
MAXX(
    FILTER(
        Shift,
        Shift[Start] >= _S && Shift[End] <= _E 
    ),
    Shift[Shift]
)

File attached below my signature:

[Anonymous]

Hello @Fowmy 
Thank you for your reply.

I just try the solution you give, but I got a blank result. Did I do something wrong?

I'm trying to do this from Power Query Editor but I can't find a way to compare the DateTime from tab Machine_Schedule to Shift tab, this might be a different topic from what I ask here. 

[Fowmy]

@Anonymous 

Can you share some dummy data in Power BI file.

[Anonymous]

@Fowmy 
I did fix the reason why the result is blank, but some are blank and the rest are just 3. 

I can't upload the pbix here. It says pbix is not supported