Iterating through a JSON array of an HTTP GET

[RBIAC]

Hello everyone,

I am building a flow that handles email responses that need to be modified based on if it's a statutory holiday.

As these dates tend to shift, I had the masochistic idea of referencing the alberta government website https://www.alberta.ca/alberta-general-holidays.aspx and extracting the table General holidays in Alberta.

I want to take this table data and pass it into excel, but it parses in a very unfriendly way into JSON data.

My intention is to check for the current year, take the dates for statutory holidays from the column that matches our current year, and save them into an array (or excel sheet so I don't have to mess around getting one flow to talk to another)

I'll then use that intermediary spreadsheet to pass those dates into a condition to check if today's date matches any of the dates in the table. if it does, do xyz.

What's making my head burst is trying to map the JSON data and pass it into a table.

My flow so far:

My extracted JSON looks like this:

{
  "table": {
    "thead": {
      "tr": {
        "th": [
          "General holiday",
          "Definition of holiday",
          {
            "@class": "goa-table-20",
            "#text": "2022"
          },
          {
            "@class": "goa-table-20",
            "#text": "2023"
          },
          {
            "@class": "goa-table-20",
            "#text": "2024"
          }
        ]
      }
    },
    "tbody": {
      "tr": [
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-new-years-day",
                "#text": "New Year’s Day"
              }
            },
            "January 1",
            "January 1",
            "January 1",
            "January 1"
          ]
        },
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-family-day",
                "#text": "Alberta Family Day"
              }
            },
            "Third Monday in February",
            "February 21",
            "February 20",
            "February 19"
          ]
        },
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-good-friday",
                "#text": "Good Friday"
              }
            },
            "Friday before Easter",
            "April 15",
            "April 7",
            "March 29"
          ]
        },
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-victoria-day",
                "#text": "Victoria Day"
              }
            },
            "Monday before May 25",
            "May 23",
            "May 22",
            "May 20"
          ]
        },
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-canada-day",
                "#text": "Canada Day"
              }
            },
            "July 1, except when it falls on a Sunday, then it is July 2",
            "July 1",
            "July 1",
            "July 1"
          ]
        },
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-labour-day",
                "#text": "Labour Day"
              }
            },
            "First Monday in September",
            "September 5",
            "September 4",
            "September 2"
          ]
        },
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-thanksgiving-day",
                "#text": "Thanksgiving Day"
              }
            },
            "Second Monday in October",
            "October 10",
            "October 9",
            "October 14"
          ]
        },
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-remembrance-day",
                "#text": "Remembrance Day"
              }
            },
            "November 11",
            "November 11",
            "November 11",
            "November 11"
          ]
        },
        {
          "td": [
            {
              "a": {
                "@href": "#general-holidays-christmas-day",
                "#text": "Christmas Day"
              }
            },
            "December 25",
            "December 25",
            "December 25",
            "December 25"
          ]
        }
      ]
    }
  }
}

As a complete JSON rookie I have bitten off more than I can chew.

[Scott_Parker]

You'll want to use the Select action on the ['table']['trbody']['tr'] to pull out the values you want. The Select action is a good way to flat the array in the way you are thinking of.

[RBIAC]

Hi Steve Scott (sorry),

My Select breaks because parts of that JSON are objects, and others are arrays. I'm a little lost on how best to construct this.

[RBIAC]

If any of the good folk on here could assist with this I'd be hugely appreciative. I've tried for many hours to build it and am still no further to getting it going 😞

[RBIAC · Accepted solution]

Just closing the loop on this.

I revised my approach and used PowerBI PowerQuery to pull the tables from the website and to isolate the column matching this year only.

I then used a DAX query with the PowerBI connector to pull my data into a much simpler JSON array, which I successfully queried.

It's less elegant than doing it all within the flow, but it gets the job done.

[Scott_Parker]

I think that is great way of doing it. Transforming data with Power Automate's limited set of functions is pretty painful and having the data available in Power BI makes a lot of sense.

[RBIAC]

Yeah, in PowerBi it was -MUCH- easier to isolate the table matching the current year, and do so dynamically.

So now my power automate can query the stat holiday dates as they shift year to year, and self-update itself. Meaning my users don't have to go behind the curtain to tinker with my IT sorcery.

Huzzah!