Issue with Create HTML table from SQL Query

[cdebloi]

I am trying to build a flow that takes data from a SQL query (using 'Transform data using Power Query') and essentially formats it into an HTML table that I can send via an email. The flow automates sending out the email weekly with the results of the query. I think I am close but keep running into an issue with the "Create HTML table" where the error reads: "BadRequest. The property 'columns' must be specified unless the 'form' property value is an array of objects." Though I'm not quite sure what I need to do to fix it.... all the solutions I can find online seem to work for people using Get Items from a Sharepoint list but I can't find anything that comes from a SQL Query. Appreciate the help!

Ex.// My SQL Output body/value is 

[

  {

    "Run": 1308103,

    "Weeks Sales": 6,

    "Customer": "FPL",

    "Weeks Start": 10,

    "TSR": "Chris",

    "Engineers": "Jim, Joe"

  },

  {

    "Run": 1262842,

    "Weeks at Sales": 4,

    "Customer": "Farmers Inc",

    "Weeks to FA Start": 10,

    "TSR": "Paul",

    "Engineers": "Bob, Joe"

  }

]

I then Parse JSON, run through some steps in the flow, then send the email. 

The "style" is just a format style for the HTML table but maybe I've got an error in there but that isn't where the error in the flow is showing up.

<style>
table {
border: 1px solid #1C6EA4;
background-colorA: #EEEEEE;
width: 100%;
text-align: left;
border-collapse: collapse;
}
table td, table th {
border: 1px solid #AAAAAA;
padding: 3px 2px;
}
table tbody td {
font-size: 13px;
}
table thead {
background: #0602e1;
border-bottom: 2px solid #444444;
}
table thead th {
font-size: 15px;
font-weight: bold;
color: #FFFFFF;
border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
border-left: none;
}

</style>

[v-yueyun-msft · Accepted solution]

Hi , @cdebloi 

According to your description, you have got the data like this:

[
  {
    "Run": 1308103,
    "Weeks Sales": 6,
    "Customer": "FPL",
    "Weeks Start": 10,
    "TSR": "Chris",
    "Engineers": "Jim, Joe"
  },
  {
    "Run": 1262842,
    "Weeks at Sales": 4,
    "Customer": "Farmers Inc",
    "Weeks to FA Start": 10,
    "TSR": "Paul",
    "Engineers": "Bob, Joe"
  }
]

So you can just use this array in your "Create HTML Table" action:

And for your css code , you ca also put it in a Compose action and then put it in the body of the "Send an email v2" action:

Best Regards,

Yueyun Zhang

[cdebloi · Accepted solution]

After doing some re-thinking and mapping out the flow on paper, I was able to simplify it down and get the desired output I wanted. Here is the new flow for reference. The "Select" should've bene my first step to turn the JSON from the SQL Query into an array format for the HTML table rather then going through and creating an array the other way that was causing me issues with the column headers

[cdebloi]

Yep - solved my problem after I went back at it building my flow from scratch. We had very similar answers. I appreciate the reply!

[v-yueyun-msft]

Hi , @cdebloi 

Thanks for your quick response! Do you mean you have solved by yourself , Oh good news to hear that!!

If convenient , can you mark the replys which is useful for this case which can help more people!

Best Regards,

Yueyun Zhang