Forum Discussion
Power query data from a calendar worksheet
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 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.
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.
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.
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.