COmbine 2 table and summarize it

[dading]

Hi..

Good afternoon,

I'm new in power bi.

i hope someone can help me out on this one.

i have 2 table. the first one is equipment record table

TimeStart	TimeEnd	Eqmt
18/03/2020 7:30:00	18/03/2020 15:00:00	S1

and second is employee login at equipment

TimeStamp_Login	Employee	Eqmt
18/03/2020 7:30:00	A	S1
18/03/2020 9:30:00	A	S1
18/03/2020 9:40:00	B	S1
18/03/2020 13:00:00	A	S1
18/03/2020 14:00:00	C	S1

i want to combine those table into one table as shown below

TimeStart	TimeEnd	Eqmt	Employee
18/03/2020 7:30:00	18/03/2020 9:40:00	S1	A
18/03/2020 9:40:00	18/03/2020 13:00:00	S1	B
18/03/2020 13:00:00	18/03/2020 14:00:00	S1	A
18/03/2020 14:00:00	18/03/2020 15:00:00	S1	C

It would be great if anyone can suggest how I can achive this.

thank you

[Mariusz · Accepted solution]

Hi @dading 

Please see the attached file with a solution in Power Query

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[dading]

Hi @Mariusz 

Wow..this is amazing!

this is what i want

by the way

i see a step of "grouped rows" in your file, how to do that actually?

is there a button in power query editor to do that? or we must write that formula?

[Mariusz]

Hi @dading 

Yes, please see the below 

The only thing that I have added is Group Kind as per below, this is to make sure it groups only values that are the same and next to each other and not alltogether

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[dading]

Ow Ok
I understand now
Thank you very much👍👍👍

[amitchandak]

Try like

summarize(calculatetable(table2,table2[TimeStamp_Login]>=min(Table1[start_time]) && table2[TimeStamp_Login]>=max(Table1[end_time])
&& table2[Eqmt]>=min(Table1[Eqmt])),table2[Employee],table2[Eqmt],Table1[start_time],Table1[end_time])