Merging two rows

[Sujit_Thakur]

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

[v-diye-msft]

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.

[amitchandak]

@Sujit_Thakur , Can you share sample data and sample output in a table format?

[Sujit_Thakur]

Dear @amitchandak  sir ,
Following is my dummy raw data 
Table 1 - Raw Data Table 

Name	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 output 

Name	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]

@Sujit_Thakur , I would approach this like Continuous Streak with 20 Min. But as 2-3 are exactly 20 Min part, so is it 19 Min?

See if you can try with this approach

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

If this approach can work and you are not able to get a solution, I will try out.

[amitchandak]

@Sujit_Thakur , Please find the file attached after the signature . You might need to add a few filters.

Create a new table, But you can manage with measure

[Sujit_Thakur]

@amitchandak  ,
I have a very little knowledge of M language ,
Is there any work around to do this with DAX ?

[Sujit_Thakur]

@amitchandak  sir ,
Do you suggest making a new table in model , using Calculate table ???
Please help,