Hevitra ato Anatiny
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:
Mariho fa:
- The name of the event is mpitantanawho fulfills this order (Elena) and Nomeraon'ny kaomandy
- nilaza adiresy fanaterana
- 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 Fanontaniana momba ny herinaratra) choose a command Avy amin'ny 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.
- Amin'ny tabilao mandroso Manampy tsanganana mifidy Index column (Ampio tsanganana - tsanganana fanondro)to add a row number column to our data.
- Right there on the tab. Manampy tsanganana mifidy ekipa Andry misy fepetra (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 Blockamin'ny fipihana havanana amin'ny lohateniny ary mifidy ny baiko Fenoy - Midina (Feno - Midina).
- Remove unnecessary column Index.
- Select a column Column1 and perform a convolution of the data from the column Column2 mampiasa ny baiko Transform – Tsanganana Pivot (Transform — Pivot column). Be sure to select in the options Aza atambatra (Aza atambatra)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 – Fanoloana soatoavina) 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.
- Misafidiana tsanganana DESCRIPTION.1 perform the convolution, as we did earlier, with the command Transform – Tsanganana Pivot (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 Aza atambatra (Aza atambatra):
- 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 — Akatona sy Ampidiro — Akatona ary Ampidiro ao… (Ao an-trano — Akatona&Ampidiro — Akatona&Ampidiro…)
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 - Havaozy daholo (Angona - Havaozy daholo).
- Factory calendar in Excel updated from internet via Power Query
- Manova tsanganana ho latabatra
- Mamorona tahiry ao amin'ny Excel