Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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?

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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