Merging Queries based on time of entries

[amalm]

Hi, 

I have 2 tables whose important columns I have listed below:

Table 1 (data captured at the beginning of the workday):

• Date
• Starting Time
• Username
• Vehicle Number
• Starting kilometer reading 

Table 2 (data captured at the end of the workday):

• Date 
• Ending Time
• Username
• Ending kilometer reading

My requirement:

I need to merge both these tables so that I have a starting and ending kilometer reading for each vehicle for a particular day, in the same report. 

My current approach:

I made a custom column in the query editor concatenating the date & username, so that I have a unique value for each day per user, and used Merge Inner.

The problem:

It was working fine until now, but there are now certain users who do not work for me the entire day. They start in the morning, end after 3 hours, then start again in the evening, end after another 2 hours etc. I need to calculate their exact hours and kilometers, among other things. The tables now have to be merged with the entries matching in the correct time sequence:

In short I need to do this:

Any help would be much appreciated, thanks.

[v-frfei-msft]

Hi @amalm,

We can merge queries as a new one in PQ based on Vehicle Number or some other column. Afther that, to remove the duplicates to achieve our goal. Here is the M code for your reference.

let
    Source = Table.NestedJoin(Table1,{"Username", "Vehicle No"},Table2,{"Username", "Vehicle No"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Ending Time", "Ending kms"}, {"Table2.Ending Time", "Table2.Ending kms"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Table2", {"Table2.Ending Time"})
in
    #"Removed Duplicates"

Please check the pbix as attached for more details.

Regards,

Frank

[amalm]

@v-frfei-msft

Thanks for the reply. However, it does not work. The "remove duplicates" step removes the wrong duplicate in the second entry. 

(It should show 2:30PM to 3:30PM)

Table1:

Table2:

Merged Tables:

[amalm]

Bump... Any help? 

[amalm]

@MarcelBeug

I saw your solution to a similar problem in another thread (https://community.powerbi.com/t5/Desktop/time-difference-between-2-rows/td-p/226755); can you please help me with mine? Thanks.

[Greg_Deckler]

Hmmm, maybe @ImkeF has an idea here.