Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Sujit_Thakur
Solution Sage
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
v-diye-msft
Community Support
Community Support

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.

 

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

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

Name CityStart DateStart TimeEnd Date End timeDistance(KM) 
SujitPune11-09-202:00 AM11-09-202:04 AM0.2
SujitPune11-09-202:10 AM11-09-202:15 AM0.3
SujitPune11-09-2002:3011-09-2003:004

 

Table 2 : Allocated Distance 

nameDate allocated_Distance 
Sujit11-09-2045

 



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 CityStart DateStart TimeEnd Date End timeDistance 
SujitPune11-09-202:00 AM11-09-202:15 AM0.5
SujitPune11-09-2002:3011-09-2003:004

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

@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

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.