Forum Discussion
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).
[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.
Is it possible to be done in Query M, or Power BI at all?
- 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.
2 Replies
- AllisonKennedy
Community Champion
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.