Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 tables whose important columns I have listed below:
Table 1 (data captured at the beginning of the workday):
Table 2 (data captured at the end of the workday):
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.
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
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:
Bump... Any help?
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.
Hmmm, maybe @ImkeF has an idea here.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |