Forum Discussion
Merging two rows
Dear all ,
I have a data table .
In which I have fields like Name , City ,
Start date , start time , end date , end time and some measures to calculate their percentage of travelling according to allocated distance .
Formula is
Percent_travel = [Travel] / Related('Variant'[Allocated_distance])
Now what I want to do is , if the difference of two consecutive rows is within 20 mins .
Then it should club those rows and show as only one in visual .
For eg
Row 1 -
Name - Sujit
City - Pune
Start date - 9/10/2020
Start time - 11:20 Am
End date - 9/10/2020
End time - 11:22 Am
[Travel] - 0.3 km
Row 2-
Name - Sujit
City - Pune
Start date - 9/10/2020
Start time - 11:28 Am
End date - 9/10/2020
End time - 11:38Am
[Travel] - 0.5 km
I want
Name - Sujit
Start date - 9/10/2020
Start time - 11:20 Am
End date - 9/10/2020
End time - 11:38 Am
[Travel] - 0.8 Km
7 Replies
Sujit_Thakur , Can you share sample data and sample output in a table format?
Dear amitchandak sir ,
Following is my dummy raw data
Table 1 - Raw Data TableName City Start Date Start Time End Date End time Distance(KM) Sujit Pune 11-09-20 2:00 AM 11-09-20 2:04 AM 0.2 Sujit Pune 11-09-20 2:10 AM 11-09-20 2:15 AM 0.3 Sujit Pune 11-09-20 02:30 11-09-20 03:00 4 Table 2 : Allocated Distance
name Date allocated_Distance Sujit 11-09-20 45
Now when now i am using raw data table into table visual ,
it is showing as it is shown above in table 1 ,
but what i want is while showing in table visual , it must merge two rows which are recorded within 20 mins of time stamp
desired outputName City Start Date Start Time End Date End time Distance Sujit Pune 11-09-20 2:00 AM 11-09-20 2:15 AM 0.5 Sujit Pune 11-09-20 02:30 11-09-20 03:00 4 amitchandak sir ,
Do you suggest making a new table in model , using Calculate table ???
Please help,
Hi Sujit_Thakur
If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!
If not, please kindly elaborate more.
