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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.