cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Solution Sage

## 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 7
Community Support

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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Super User

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

Solution Sage

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
Super User

@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.

Super User

@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

Solution Sage

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

Solution Sage

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

Announcements