Forum Discussion
Merge Rows
I have a table for a listing of employees where it captures a timestamp and event type (start shift, start break, end break, end shift) as different rows. so one employee may have event ID 1 with a time stamp of 08:00AM and eventstatus=1 (Start Shift) then the next row is a timestamp of 12:00PM and eventstatus 2 for start break and so on throughout the day. 1 employee could have anywhere from 4 to 8 rows depending on how many breaks they have. I want to show this on a table that managers can filter but where an employee would have only 1 row with individual columns for Start Shift, Start Break, End Shift End Break, etc. Is there a way to do this in Power BI?
Hey Anonymous
I'm making some presumptions here, so if I am way off the mark I apologize.
I think you want to use 'transpose.' I use this a lot more in Excel, but it does the same thing in power query. Here is what my data looked like when I started (I'm modeling mine based on how I understood your description). I have columns for my employees. I then have to use headers as first row. After doing that, I can use transpose to use the columns as rows.
The below is just a screenshot of me demoting the headers.
At this point, I can click 'transpose' & the data should switch. The last step would then be to promote my headers & I think I have the data where you need it to be. The 'transpose' btn is directly below the 'transform' tab.
Hope this helps!
5 Replies
Perhaps, I can think of several possibilities. Can you provide sample/example data in text? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
Do you have an employee ID as part of the data, I presume? Any other fields/columns?
thanks for the link to the article! that helps for sure.
so I have an event table that has an EventID (PK) | EmployeeID (FK) | CompanyID (FK) | WorkDate | TimeStamp | EventTypeCode fields. has a few more but not relevant to what I am trying to do. So the Data would look something like
EventID (PK) | EmployeeID (FK) | CompanyID (FK) | WorkDate | TimeStamp | EventTypeCode (FK) | SupervisorID (FK) | LateFlag
1 | 1234 | ABC123 | 03/12/2019 | 08:00:00 | 1 | 5678 | 0
2 | 1234 | ABC123 | 03/12/2019 | 12:00:00 | 2 | 5678 | 0
3 | 1234 | ABC123 | 03/12/2019 | 13:15:00 | 3 | 5678 | 1
4 | 1234 | ABC123 | 03/12/2019 | 17:00:00 | 4 | 5678 | 0
Where EventTypeCode 1, 2, 3, 4 = StartShift, StartBreak, EndBreak, EndShift respectively.
the Entity relationships are as follows:
EventTable *:1 EventTypeTable
EventTable *:1 EmployeeTable
EventTable *:1 SubsidiaryTable
The end result I'm trying to get to is to be able to flag an alert for the supervisor if an employee forgets to punch out at the end of the day or if they are late (using LateEntryFlag) via email. I was planning on Using Microsoft Flow for the email portion but need to capture the alert someway in power BI to act as the trigger for the flow.
Hey Anonymous
I'm making some presumptions here, so if I am way off the mark I apologize.
I think you want to use 'transpose.' I use this a lot more in Excel, but it does the same thing in power query. Here is what my data looked like when I started (I'm modeling mine based on how I understood your description). I have columns for my employees. I then have to use headers as first row. After doing that, I can use transpose to use the columns as rows.
The below is just a screenshot of me demoting the headers.
At this point, I can click 'transpose' & the data should switch. The last step would then be to promote my headers & I think I have the data where you need it to be. The 'transpose' btn is directly below the 'transform' tab.
Hope this helps!
Thanks for the Reply! I don't know if that will work in my case since my data doesn't quite look like your example. I may be able to figure something out with a custom table and transpose it though if there isn't an easier way. I posted an example of my data in a reply. I realized now that I neglected to do that.
I appreciate the quick reply though.
