Contents
Many business processes (and even entire businesses) in this life involve the fulfillment of orders by a limited number of performers by a given deadline. Planning in such cases occurs, as they say, “from the calendar” and often there is a need to transfer the events planned in it (orders, meetings, deliveries) to Microsoft Excel – for further analysis by formulas, pivot tables, charting, etc.
Of course, I would like to implement such a transfer not by stupid copying (which is just not difficult), but with automatic updating of data so that in the future all changes made to the calendar and new orders on the fly would be displayed in Excel. You can implement such an import in a matter of minutes using the Power Query add-in built into Microsoft Excel, starting from the 2016 version (for Excel 2010-2013, it can be downloaded from the Microsoft website and installed separately from the link).
Suppose we use the free Google Calendar for planning, in which I, for convenience, created a separate calendar (the button with a plus sign in the lower right corner next to Other calendars) with the title Work. Here we enter all orders that need to be completed and delivered to customers at their addresses:
By double-clicking any order, you can view or edit its details:
Note that:
- The name of the event is managerwho fulfills this order (Elena) and Order number
- Indicated address delivery
- The note contains (in separate lines, but in any order) the order parameters: payment type, amount, customer name, etc. in the format Parameter=Value.
For clarity, the orders of each manager are highlighted in their own color, although this is not necessary.
Step 1. Get a link to Google Calendar
First we need to get a web link to our order calendar. To do this, click on the button with three dots Calendar Options Work next to the name of the calendar and select the command Settings and Sharing:
In the window that opens, you can, if desired, make the calendar public or open access to it for individual users. We also need a link for private access to the calendar in iCal format:
Step 2. Load data from the calendar into Power Query
Now open Excel and on the tab Data (if you have Excel 2010-2013, then on the tab Power Query) choose a command From the Internet (Data — From Internet). Then paste the copied path to the calendar and click OK.
The iCal Power Query does not recognize the format, but it is easy to help. Essentially, iCal is a plain text file with a colon as a delimiter, and inside it looks something like this:
So you can just right-click on the icon of the downloaded file and select the format that is closest in meaning CSV – and our data about all orders will be loaded into the Power Query query editor and divided into two columns by colon:
If you look closely, you can clearly see that:
- Information about each event (order) is grouped into a block starting with the word BEGIN and ending with END.
- The start and end datetimes are stored in strings labeled DTSTART and DTEND.
- The shipping address is LOCATION.
- Order note – DESCRIPTION field.
- Event name (manager name and order number) — SUMMARY field.
It remains to extract this useful information and transform it into a convenient table.
Step 3. Convert to Normal View
To do this, perform the following chain of actions:
- Let’s delete the top 7 lines we don’t need before the first BEGIN command Home — Delete Rows — Delete Top Rows (Home — Remove rows — Remove top rows).
- Filter by column Column1 lines containing the fields we need: DTSTART, DTEND, DESCRIPTION, LOCATION and SUMMARY.
- On the Advanced tab Adding a column choose Index column (Add column — Index column)to add a row number column to our data.
- Right there on the tab. Adding a column choose a team Conditional column (Add column — Conditional column) and at the beginning of each block (order) we display the value of the index:
- Fill in the empty cells in the resulting column Blockby right-clicking on its title and selecting the command Fill – Down (Fill — Down).
- Remove unnecessary column Index.
- Select a column Column1 and perform a convolution of the data from the column Column2 using the command Transform – Pivot Column (Transform — Pivot column). Be sure to select in the options Do not aggregate (Don’t aggregate)so that no math function is applied to the data:
- In the resulting two-dimensional (cross) table, clear the backslashes in the address column (right-click on the column header – Replacing values) and remove the unnecessary column Block.
- To turn the contents of the columns DTSTART и DTEND in a full date-time, highlighting them, select on the tab Transform – Date – Run Analysis (Transform — Date — Parse). Then we correct the code in the formula bar by replacing the function Date.From on DateTime.Fromso as not to lose time values:
- Then, by right-clicking on the header, we split the column DESCRIPTION with order parameters by separator – symbol n, but at the same time, in the parameters, we will select the division into rows, and not into columns:
- Once again, we divide the resulting column into two separate ones – the parameter and the value, but by the equals sign.
- Selecting a column DESCRIPTION.1 perform the convolution, as we did earlier, with the command Transform – Pivot Column (Transform — Pivot column). The value column in this case will be the column with parameter values − DESCRIPTION.2 Be sure to select a function in the parameters Do not aggregate (Don’t aggregate):
- It remains to set the formats for all columns and rename them as desired. And you can upload the results back to Excel with the command Home — Close and Load — Close and Load in… (Home — Close&Load — Close&Load to…)
And here is our list of orders loaded into Excel from Google Calendar:
In the future, when changing or adding new orders to the calendar, it will only be enough to update our request with the command Data – Refresh All (Data — Refresh All).
- Factory calendar in Excel updated from internet via Power Query
- Transforming a column into a table
- Create a database in Excel