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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
paulfink
Post Patron
Post Patron

Group lines together with dates and time

hi guys,

 

need a hand with figuring out how to fix my table.

 

i got an excel in with thousands of lines of data detailing timings but the way its formatting is messing up my report.

its structured like this:

Project - Date - Time 

 

the date column has listed out every date (its just how the system works) so if there are dates from 01/02/2022 to 10/02/2022 then there will be 10 different lines which is a big problem.

 

how can i make it so that these lines will group up and be structured like this:

Project - Start - End - Total Time.

 

any ideas?

 

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

Hi @paulfink ,

 

Whether the advice given by @AllisonKennedy  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly.

 

If not, please give specific questions and provide screenshots of the desired results. Looking forward to your feedback.


Best Regards,
Henry

AllisonKennedy
Super User
Super User

There's probably quite a few ways you could do this, one would be to use Power Query to Group By and add a column for Min Date, and Max Date and rename those as start and end date for the project.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy this has partly worked, however, i didn't mention that there could be multiples of the same project against different times in the year. 

 

could you still do this?

@paulfink  In that case you may need to try something a bit more complex, find the previous date for each project and if there's a gap, then flag it as a start date, if not, check if it's an end date and flag that, then add an index or a count and then group by? 

 

https://www.myonlinetraininghub.com/referencing-next-row-power-query

 

Or you could find this in DAX using variables and MAXX function. Does that make enough sense or do you need more help?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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