Formatting a date with "transform"

[valeriabreveglieri]

Hello,
I am trying to do a seemingly easy task, formatting a date inside a “transform”, but I can’t seem to make it work.
My date reads as an ex.

and I would like to have it in a format like:
24-Aug-2023 18:24
I wanted to do it in the “transform” block as I am then adding the date to more text to make a custom string.
If I use the “format” function as below, it does not work at all and makes the whole chart disappear (no matter the parameters of the format function)
“transform”: [
{
“calculate”: “format(datum[‘START_DATE_TIME’],xxxxxxxxxxxxxxxxxx)”,
“as”: “formatted_date”
}]

Is there something very obvious I am missing?

Thanks!
Kind regards
Valeria

[Greg]

Hi @valeriabreveglieri

I believe the “format” function only works when data is typed as “temporal”. Here’s an alternative using individual component values in the transform block

  "transform": [
    {
      "calculate": "'sample text ' + date(datum['Created']) + '-' + (month(datum['Created']) + 1) + '-' + year(datum['Created']) + ' ' + hours(datum['Created']) + ':' + minutes(datum['Created'])",
      
      "as": "_formatted_created"
    }
  ]

Full Code:

{
  "title": {
    "anchor": "start",
    "align": "left",
    "text": "eDNA Forum - Date Formatting using Deneb",
    "font": "Verdana",
    "fontSize": 20,
    "fontWeight": "bold",
    "fontStyle": "normal"
  },
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "'sample text ' + date(datum['Created']) + '-' + (month(datum['Created']) + 1) + '-' + year(datum['Created']) + ' ' + hours(datum['Created']) + ':' + minutes(datum['Created'])",
      
      "as": "_formatted_created"
    }
  ],
  "encoding": {
    "y": {
      "field": "Issue id",
      "type": "nominal",
      "axis": null
    }
  },
  "layer": [
    {
      "mark": {
        "type": "text",
        "align": "left",
        "xOffset": -280
      },
      "encoding": {
        "text": {
          "field": "Issue id",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "align": "left",
        "xOffset": -160
      },
      "encoding": {
        "text": {
          "field": "Created",
          "type": "temporal",
          "format": "%d-%b-%Y %H:%M"
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "align": "left",
        "xOffset": -40
      },
      "encoding": {
        "text": {
          "field": "_formatted_created",
          "type": "nominal"
        }
      }
    }
  ]
}

Notes:

• I used ~10 records of the sample JIRA data provided for eDNA Challenge #8 as a test case
• see the “Date-Time Functions” section of https://vega.github.io/vega/docs/expressions/ for additional functions
• the “date” function doesn’t zero-pad day-of-month values (e.g., should be “06” instead of “6”, etc.)
• the “month” function returns zero-based month number instead of month abbreviation (e.g., “11” instead of “Nov”, etc.)

Hope this helps.
Greg
eDNA Forum - Date Formatting using Deneb.pbix (1.4 MB)

[Greg]

After researching string and array operations a bit, here’s revised “transform” code with padding (day-of-month, hours, minutes) and abbreviations (month) added.

  "transform": [
    {
      "calculate": "'sample text ' + date(datum['Created']) + '-' + (month(datum['Created']) + 1) + '-' + year(datum['Created']) + ' ' + hours(datum['Created']) + ':' + minutes(datum['Created'])",
      "as": "_formatted_created"
    },
    {
      "calculate": "'sample text ' + pad(date(datum['Created']), 2, '0', 'left') + '-' + slice( ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'], month( datum['Created'] ), month( datum['Created'] ) + 1) + '-' + year(datum['Created']) + ' ' + pad(hours(datum['Created']), 2, '0', 'left') + ':' + pad(minutes(datum['Created']), 2, '0', 'left')",
      "as": "_formatted_created2"
    }
  ]

Hope it helps.
Greg
eDNA Forum - Date Formatting using Deneb - V2.pbix (1.4 MB)

[valeriabreveglieri]

Hi @Greg , thank you!!! This looks awesome
I have again learned something new today!
Kind regards
Valeria

[dm-p]

If you prefer working with Power BI format strings, you can also use pbiFormat with a bit less effort:

{
  ...
  "transform": [
    {
      "calculate": "pbiFormat(datum['Created'], 'dd-MMM-yyyy HH:mm')",
      "as": "_pbiFormatted_created"
    },
    {
      "calculate": "'sample text ' + pbiFormat(datum['Created'], 'dd-MMM-yyyy HH:mm')",
      "as": "_pbiFormatted_created2"
    }
  ],
  ...
}

DM-P

eDNA Forum - Date Formatting using Deneb - V2a.pbix (1.4 MB)

[Greg]

Terrific! It hadn’t occurred to me to use Power BI format strings outside of the encoding block before, but it’s more concise and improves readability. Thanks!
Greg

[valeriabreveglieri]

@dm-p thank you Daniel! Very easy and straightforward