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.
- 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
- 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/yyyydates, and the outputs are the matching days of the week, and will apply the corresponding transformation to the rest of the column.
- Click Submit.