Skip to main content Skip to complementary content

Leveraging semantic awareness to convert dates to day of the week using Magic Fill

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.

Dataset containing number of sales recorded.

Procedure

  1. Click the header of the date column in order to select its content.
  2. In the functions panel, type Magic fill and click the result to display the options of the associated function.
  3. 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.
  4. 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.
  5. 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.

  6. 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.
Dataset containing number of sales recorded with a new column.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here