SOLVED

Power query data from a calendar worksheet

Occasional Contributor

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 NameHardscape Crew DaysSoftscape Crew Days 
Stevens2

4

Johnson31
Smith  

 

9 Replies

@Rex-Delson Perhaps like in the attached file?

Screenshot 2023-01-20 at 08.36.05.png

Thank you for responding, but the names of the foremen are not important. I need column A in your table to list all the jobs in the worksheet range of B3:Q280. This is the difficult part for me. Do you know how to do this?
best response confirmed by Rex-Delson (Occasional Contributor)
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. 

@Riny_van_Eekelen 

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.

@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.

 

@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.

Labor Hours example.jpg

@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.

 

@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.

@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.