Years Not Formatting to Correct Date Format

Question

Fairly new to vega/vega-lite and javascript in general so trying my hand at this and it is not working the way I'd like.

My main issue at the moment is that my years are whole numbers as primitive values. I've tried parsing them to show as temporal units in the format of %Y but I just keep getting decimal places which doesn't work.

{
  "data": {"name": "dataset"},
  "width": 500,
  "height": 500,
  "layer": [
    {
      "mark": {
        "type": "line",
        "point": true
        },
      "encoding": {
        "y": {"aggregate": "mean","field": "Index Score", "type": "quantitative", "axis": {"format": ".1e"}, "title": "Economic Exposure Score"},
        "x": {"field": "Year","type": "quantitative", "title": "Years"},
        "color": {"field": "Country", "type": "nominal", "legend": null}
      }
    },
    {
      "mark": {"type": "text", "align": "center", "dy": 15, "fontWeight": "bold", "fontSize": 14},
      "encoding": {
        "x": {
          "field": "Year",
          "type": "quantitative"
        },
        "y": {
          "field": "Index Score",
          "type": "quantitative"
        },
        "text": {"field": "Event Name", "type": "nominal"},
        "color": {"value": "black"}
      }
    }
  ]
}

Here is where I'm at right now: v1 index score over years. Yes there are a lot of lines. I have a dynamic card that will show the chart only once a country has been selected from a slicer in the dashboard so no worries there. I plan on adding labels to the lines.

Summary of troubleshooting:

have tried formatting this at the "Data" level as well as within the mark/line/encoding/y but I either get a whole number representation or a decimal...

Can you supply a .pbix and make it clear what your end result should look like as it isn't clear from your description. — Davide Bacci, Nov 18 '22 at 18:01
@DavidBacci thx, will try. The doc is linked to private sharepoint and has sensitive info so I will strip it and generate it as a table in PBI. But essentially, I can't format the vega-lite y-axis to show me Years in proper years format. They consistently show up as 'quantitative' values and when I try type = 'temporal', it gives me a different type of whole number that is still not a year. If I attempt to change the data type at the data level in PBI, it gives me the wrong information all together in column so trying to assess where it needs to be formatted to show correctly as year. — vzap, Nov 18 '22 at 19:38
But your image shows years on the x axis already? Is that not what you want? — Davide Bacci, Nov 18 '22 at 20:16
If you look closely, it is 2,005 2,007 2,010 (so it is treating them as whole numbers rather than traditional years such as 2005 2007 2010) — vzap, Nov 18 '22 at 20:18
That is just the formatting because you're not supplying a proper date field. Either change the axis format, supply a proper date field or try changing the type to ordinal. — Davide Bacci, Nov 18 '22 at 20:31
Thanks, yes was trying to figure out how to do that... got it working with a custom column in Power Query. https://community.powerbi.com/t5/Desktop/Year-imported-as-Text-but-Date-change-is-also-incorrect/m-p/2915859#M1001195 — vzap, Nov 25 '22 at 15:07

score 1 · Answer 1 · answered Dec 07 '22 at 13:56

Option 1: Create the date in Power Query

As you say in the comments, you can easily create the date on the Power BI side and pass it in. I would have your month and year columns as text and concatenate them like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Index Score", type number}, {"Month", type text}, {"Event Name", type any}, {"Year", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each [Month] & " 1, " & [Year], type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}}),
    #"Calculated End of Month" = Table.TransformColumns(#"Changed Type1",{{"Date", Date.EndOfMonth, type date}})
in
    #"Calculated End of Month"

If you want start of month, remove the #"Calculated End of Month" step.

Option 2: Formatting in Vega-lite

You could also leave the year as you have it, and simply add formatting in the x-axis encoding in Vega:

      "encoding": {
        "x": {
          ...
          "axis": {"format": "0000"}
        },

Open the Chart in the Vega Editor

Years Not Formatting to Correct Date Format

1 Answers1