Power Query with 4 date/time columns

%3CLINGO-SUB%20id%3D%22lingo-sub-3074248%22%20slang%3D%22en-US%22%3EPower%20Query%20with%204%20date%2Ftime%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074248%22%20slang%3D%22en-US%22%3EI'm%20using%20Excel%20because%20I%20have%20to%20send%20this%20workbook%20to%20several%20people%20each%20day%20and%20they%20don't%20have%20access%20to%20Power%20BI.%20Also%2C%20I%20don't%20think%20Power%20BI%20would%20be%20useful%20here.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20huge%20Excel%20workbook%20I%20pull%20two%20reports%20into%20daily.%20Both%20reports%20contain%20two%20date%2Ftime%20columns%20to%20represent%20time%20in%20and%20out.%20The%20problem%20is%20one%20is%20a%20solid%20block%20of%20time%2C%20say%2010%20hours%2C%20and%20the%20other%20might%20have%20one%20block%20for%20the%20whole%2010%20hours%20or%20five%202%20hour%20blocks%20within%20those%20two%20hours.%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20can%20I%20do%20to%20connect%20these%20times%20together%20in%20a%20meaningful%20way%3F%20I%20am%20merging%20the%20two%20reports%20by%20date%2C%20user%20id%20number%2C%20and%20user%20last%20name.%20I%20can't%20use%20time%20because%20part%20of%20what%20I%20am%20trying%20to%20build%20is%20a%20way%20to%20look%20for%20discrepancies%20including%20missing%20one%20or%20the%20other.%20They%20have%20to%20have%20both.%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20ideas%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3074248%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor
I'm using Excel because I have to send this workbook to several people each day and they don't have access to Power BI. Also, I don't think Power BI would be useful here.

I have a huge Excel workbook I pull two reports into daily. Both reports contain two date/time columns to represent time in and out. The problem is one is a solid block of time, say 10 hours, and the other might have one block for the whole 10 hours or five 2 hour blocks within those two hours.

What can I do to connect these times together in a meaningful way? I am merging the two reports by date, user id number, and user last name. I can't use time because part of what I am trying to build is a way to look for discrepancies including missing one or the other. They have to have both.

Any ideas?
0 Replies