Based on the example that you will input, the Magic Fill function
also has the ability to recognize and format data that corresponds to certain semantic
types: countries, emails, URLs, US postal codes and states, as well as
dates.
Let's take the example of a small retail company with several stores across the
city. The following dataset shows how the total number of sales is recorded with the
corresponding date everyday. The format used to enter dates is dd/MM/yyyy
, but you would like to also display the corresponding day of
the week, in order to make some statistical analysis and try to determine which days are
the most profitable. This scenario will show of you can use the Magic Fill function to
extract this information.
Procedure
-
Click the header of the date column in order to select its content.
-
In the functions panel, type Magic
fill and click the result to display the options of the
associated function.
-
In the Input 1 field,
enter one of the values from the date
column that you would like to transform, 17/01/2018 for example.
-
In the Output 1 field,
enter the day of the week that you know is the corresponding one: Wednesday.
For the function to work, you need to enter at least two
complete examples of the transformation you want to apply. You can then add up
to three other examples. Examples can be taken from your dataset, or you can use
other valid date/day couples. The more examples you input, the more accurately
the transformation will be identified by the function.
-
Enter more before and after examples in the remaining
fields:
-
02/11/2018 as
Input 2 and Friday as Output 2
-
06/12/2018 as
Input 3 and Thursday as Output 3
-
22/09/2019 as
Input 4 and Sunday as Output 4
-
13/07/2019 as
Input 5 and Saturday as Output 5
Based on these examples, the function will understand that
the all the inputs you entered are dd/MM/yyyy
dates, and the outputs are the matching days of the
week, and will apply the corresponding transformation to the rest of the
column.
-
Click Submit.
Results
A new column is created, displaying
the day of the week for each date of your initial column. You can already see that most
of the sales seem to be generated on Wednesdays and Saturdays, which is an information
that was not available before. From there, you can easily filter on any of the weekdays,
or try some aggregation via the charts panel to get an average of the sales for each
weekday.