Jan 19 2023 08:01 PM - edited Jan 20 2023 01:32 PM
I have a calendar data that shows construction crews as headers. The row underneath categorizes each crew as either "hardscape" or "softscape". All the rows beneath shows what jobs they did in 2022. I need a power query that will show how many collective days a hardscape crew and softscape crew were at each job in the calendar. Can someone help me with this? Please see the attached worksheet I am working on. I need a table to show the job names in the worksheet B3:Q280. The names of the construction crew foremen in the header are not important. The power query needs to look something like this:
Job Name | Hardscape Crew Days | Softscape Crew Days |
Stevens | 2 | 4 |
Johnson | 3 | 1 |
Smith |
Jan 19 2023 11:36 PM
@Rex-Delson Perhaps like in the attached file?
Jan 20 2023 01:23 PM
Jan 20 2023 09:44 PM
Solution@Rex-Delson Sorry for the misunderstanding. Still not sure I understand what you mean but perhaps it's the 2nd version of the table I created. If not, please give an example using the data the is in the file.
Jan 21 2023 11:22 AM
Yes, that's it. I'm still learning about pivoting and power query. The steps I see you did is helping me alot. Much appreciated! Now, my last obstacle is creating columns that list the foreman associated with each job. Some jobs have multiple foremen. Please see the attached where I added some columns for foreman, but don't know how to query it.
Jan 21 2023 11:23 PM
@Rex-Delson Then you need to create a similar table of Jobs by Foreman and merge it with the table of Jobs by Scape type.
Note that I added the calculated columns in PQ, instead of in Excel. See attached.
Jan 26 2023 07:26 AM
@Riny_van_Eekelen Thank you so much! You've been a big help. Can I ask one other thing to help me with? I need Column H (Variance) to show the difference between Column G and F. Right now I have a simple formula, but I need H to be blank or null until data is entered in G. Right now H shows incorrect data in rows where nothing is entered in Column G. Is there a way to do this within PQ itself? Please see attached worksheet.
Jan 26 2023 07:45 AM
@Rex-Delson Not sure what you have done, but I can't edit the query, but yes you should do all these calculations (columns E, F and H) within PQ. Same goes for the rows you want to exclude. Add a filter step that removes these rows from the final table. What you load back into Excel should be clean data, ready for analysis.
Jan 26 2023 09:04 AM
@Riny_van_Eekelen Oops. I forgot I protected it. The password is rex. If that doesn't work, this file should. I would like to see how you would do these steps.
Jan 26 2023 11:41 PM
@Rex-Delson See attached. Not very elegant and I doubt if this will be very helpful in case you will be adding or deleting foremen. But if you understand the steps you can easily correct errors. Otherwise, the query needs to me made dynamic with regard to the column names for the foremen. I didn't take it that far.
Note also that I manually changed the header for "Luis" to "xx Luis". Not having the space as part of the header caused problems later on in the query.
Now, the whole thing builds on the principle that you have all (raw) data in one table and the active jobs with sales person and estimated hrs in another. The latter will be the basis for the analysis table.
Leaving the estimated hours empty will load a null value to PQ. When you then calculate Estimated minus Approximate, the null value will automatically lead to a null variance as PQ can't calculate with nulls. Zeroes are OK but nulls not!
See if you can follow.
Jan 20 2023 09:44 PM
Solution@Rex-Delson Sorry for the misunderstanding. Still not sure I understand what you mean but perhaps it's the 2nd version of the table I created. If not, please give an example using the data the is in the file.