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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rename Id within groups based on a third column

I have a dataset as following, the columns are GroupA, GroupBId, Datetime, TimeDiff, Marker from left to right. This is a nested data. The data is grouped by [GroupA] and then [GroupBId], after that, it is sorted by [Datetime] (ascending).

Capture.JPG

 

[TimeDiff] is calculated by having [Datetime] subtract the previous row. I need to break [GroupB] to two whenever [TimeDiff] is longer than 15 min. I have created [Marker] to help me identify where to break the group. Basically starting from [Marker] = 'Break' to [Marker] = 'Start' (but not include that row), I will need to assign [GroupBId] a different name, and it will be a combination as [GroupBId] + '-2'. Like the following. 

tempsnip.png

 

Is it possible to be done in Query M, or Power BI at all?

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

If you already have the Marker Column working as expected, then just select the Marker column in Power Query and Fill down (Transform tab in the ribbon).

After that, you can click into the Add Column tab and use a Conditional column to add a new GroupBid IF Marker= Break, then Merge "-2"

Finally, Add Column tab to Merge the old GroupBid and the Conditional Column.

You can do this in fewer steps by writing custom M code, but the above will work just fine. To edit the conditional column code manually turn on the formula bar in the View tab.

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

View solution in original post

2 REPLIES 2
AllisonKennedy
Super User
Super User

If you already have the Marker Column working as expected, then just select the Marker column in Power Query and Fill down (Transform tab in the ribbon).

After that, you can click into the Add Column tab and use a Conditional column to add a new GroupBid IF Marker= Break, then Merge "-2"

Finally, Add Column tab to Merge the old GroupBid and the Conditional Column.

You can do this in fewer steps by writing custom M code, but the above will work just fine. To edit the conditional column code manually turn on the formula bar in the View tab.

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

Anonymous
Not applicable

It worked perfectly. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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