Let’s say that you are running several projects with different budgets and want to visualize your costs for each of them. That is, from this source table:
.. get something like this:
In other words, you need to spread the budget over the days of each project and get a simplified version of the project Gantt chart. Doing this with your hands is long and boring, macros are difficult, but Power Query for Excel in such a situation shows its power in all its glory.
Power Query is an add-on from Microsoft that can import data into Excel from almost any source and then transform it in a bunch of different ways. In Excel 2016, this add-in is already built-in by default, and for Excel 2010-2013 it can be downloaded from the Microsoft website and then installed on your PC.
First, let’s turn our original table into a “smart” table by choosing the command Format as a table tab Home (Home — Format as Table) or by pressing the keyboard shortcut Ctrl+T :
Then go to the tab Data (if you have Excel 2016) or on the tab Power Query (if you have Excel 2010-2013 and you installed Power Query as a separate add-in) and click the From Table / Range button. :
Our smart table is loaded into the Power Query query editor, where the first step is to set up the number formats for each column using the dropdowns in the table header:
To calculate the budget per day, you need to calculate the duration of each project. To do this, select (hold down the key Ctrl) column first Finish, and then Start and choose a team Add column – Date – Subtract days (Add Column — Date — Subtract days):
The resulting numbers are 1 less than necessary, because we are supposed to start each project on the first day in the morning and finish on the last day in the evening. Therefore, select the resulting column and add a unit to it using the command Transform – Standard – Add (Transform — Standard — Add):
Now let’s add a column where we calculate the budget per day. To do this, on the tab Add Column I don’t play Custom column (Custom Column) and in the window that appears, enter the name of the new field and the calculation formula, using the names of the columns from the list:
Now the most subtle moment – we create another calculated column with a list of dates from start to finish with a step of 1 day. To do this, again press the button Custom column (Custom Column) and use the built-in Power Query language M, which is called List.Dates:
This function has three arguments:
- start date – in our case, it is taken from the column Start
- the number of dates to be generated – in our case, this is the number of days for each project, which we counted earlier in the column Subtraction
- time step – set by design #duration(1,0,0,0), meaning in the language of M – one day, zero hours, zero minutes, zero seconds.
After clicking on OK we get a list (List) of dates, which can be expanded into new lines using the button in the table header:
… and we get:
Now all that’s left is to collapse the table, using the generated dates as the names for the new columns. The team is responsible for this. Detail column (Pivot Column) tab Convert (Transform):
After clicking on OK we get a result very close to the desired one:
Null is, in this case, an analogue of an empty cell in Excel.
It remains to remove unnecessary columns and unload the resulting table next to the original data with the command Close and load – Close and load in… (Close & Load — Close & Load to…) tab Home (Home):
We get as a result:
For greater beauty, you can customize the appearance of the resulting smart tables on the tab Constructor (Design): set a single color style, disable filter buttons, enable totals, etc. Additionally, you can select a table with dates and enable number highlighting for it using conditional formatting on the tab Home — Conditional Formatting — Color Scales (Home — Conditional Formatting — Color Scales):
And the best part is that in the future you can safely edit old ones or add new projects to the original table, and then update the right table with dates with the right mouse button – and Power Query will repeat all the actions we have done automatically.
Voilà!
- Gantt chart in Excel using conditional formatting
- Project milestone calendar
- Generating Duplicate Rows with Power Query