We can get the following raw data from our project management software:
| Month | Project | Billable | Time |
|---|---|---|---|
| Jan 2022 | Project 1 | Yes | 100 |
| Jan 2022 | Project 1 | No | 10 |
| Feb 2022 | Project 1 | Yes | 80 |
| Feb 2022 | Project 1 | No | 30 |
| Jan 2022 | Project 2 | Yes | 60 |
| Jan 2022 | Project 2 | No | 5 |
| Feb 2022 | Project 2 | Yes | 90 |
| Feb 2022 | Project 2 | No | 15 |
I need to change this data to the following format:
| Month | Project | Billable Time | Non-Billable Time | Total Time |
|---|---|---|---|---|
| Jan 2022 | Project 1 | 100 | 10 | 110 |
| Feb 2022 | Project 1 | 80 | 30 | 110 |
| Jan 2022 | Project 2 | 60 | 5 | 65 |
| Feb 2022 | Project 2 | 90 | 15 | 105 |
Putting the raw data into a Google Sheet I thought this might be possible using Google Query. I started with this:
=QUERY(dataRange,"SELECT Month,Project,SUM(Time) GROUP BY Month, Project")
But I can't work out how to separate Billable & Non-Billable time & include this on a single row with the SUM of both times. Is this even possible using =QUERY?
If =QUERY can be used, what syntax should I use?
If =QUERY can't be used, what method should I use instead?
CodePudding user response：
Use the QUERY pivot clause like this (assuming your source table is in A1:D9 of your sheet):
=query(query({A1:D9},"select Col1,Col2,sum(Col4) group by Col1,Col2 pivot Col3 order by Col2",1),"select Col1,Col2,Col4,Col3,Col3 Col4 label Col3 'Billable time',Col4 'Non-billable time',Col3 Col4 'Total time'",1)
CodePudding user response：
You can also try:
={{A1:B1,C1&" "&D1,"Non- "&C1&" "&D1,"Total "&D1};INDEX(BYROW(UNIQUE(FILTER(A2:A&"|"&B2:B,A2:A<>"")),LAMBDA(ax,{SPLIT(ax,"|",0,0),SUMIFS(D:D,C:C,"Yes",A:A&"|"&B:B,ax),SUMIFS(D:D,C:C,"No",A:A&"|"&B:B,ax),SUMIFS(D:D,A:A&"|"&B:B,ax)})))}
