ינהאַלט
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 אַרבעט. 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:
באמערק אז:
- The name of the event is פאַרוואַלטערwho fulfills this order (Elena) and אָרדערנומבער
- אנגעוויזן אַדרעס עקספּרעס
- 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 דאַטע (if you have Excel 2010-2013, then on the tab מאַכט קווערי) choose a command פון די אינטערנעט (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 קסוו – 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 — Remove rows — Remove top rows).
- Filter by column קאָלומנקסנומקס lines containing the fields we need: DTSTART, DTEND, DESCRIPTION, LOCATION and SUMMARY.
- אויף די אַוואַנסירטע קוויטל אַדינג אַ זייַל קלייַבן Index column (לייג זייַל — אינדעקס זייַל)to add a row number column to our data.
- Right there on the tab. אַדינג אַ זייַל קלייַבן אַ קאָלעקטיוו קאַנדישאַנאַל זייַל (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 בלאָקדורך רעכט-קליקינג אויף זייַן טיטל און סעלעקטירן דעם באַפֿעל פּלאָמבירן - אַראָפּ (פילן - אַראָפּ).
- Remove unnecessary column ינדעקס.
- Select a column קאָלומנקסנומקס and perform a convolution of the data from the column קאָלומנקסנומקס ניצן דעם באַפֿעל יבערמאַכן - דרייפּונקט זייַל (Transform — Pivot column). Be sure to select in the options צי ניט אַגגרעגירן (נישט צוזאַמענגעקומען)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 – ריפּלייסינג וואַלועס) and remove the unnecessary column בלאָק.
- 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 באַשרייַבונג 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.
- סעלעקטינג אַ זייַל DESCRIPTION.1 perform the convolution, as we did earlier, with the command יבערמאַכן - דרייפּונקט זייַל (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 צי ניט אַגגרעגירן (נישט צוזאַמענגעקומען):
- 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 היים - נאָענט און לאָדן - נאָענט און לאָדן אין ... (היים - נאָענט & לאָדן - נאָענט & לאָדן צו ...)
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 דאַטן - דערפרישן אַלע (דאַטאַ - דערפרישן אַלע).
- Factory calendar in Excel updated from internet via Power Query
- יבערמאַכן אַ זייַל אין אַ טיש
- שאַפֿן אַ דאַטאַבייס אין עקססעל