You’re offline. This is a read only version of the page.
33
Hi All,
I have flow to create HTML Table from Initialize Variable Output.
Is there any way to create total/sum/average at the end of HTML Row?
Below is my "Initial Variable" output :
_________________________________________________________________________________
_________________________________________________________________________________
and HTML Output as below :
Last row of this HTML Table is the result that i want. (Column2 - Sum, Column3 - Average, Column4 - Sum and Column5 - Average)
Thank You.
Hi Falhuddin,
We need to do a lot of data operations here so math content warning 😉
Let me present the flow logic first:
I am not certain how your data is gathered by I initialized a variable to store the array of data you pasted here. The manual trigger is just for me to test the flow so don't worry about it.
Here is the test run result if you want to confirm:
Now for the details, the initialized data variable is simply the array you pasted:
For the other 4 variables, initialize the sum of yesterday and sum of today as integers of value 0. Initialize the sum of ratio1 and ratio2 as floats (meaning they are decimal numbers) of value 0
For the steps within 'Apply to each row', we need 4 calculation steps to aggregate the values for those 4 variables, the action we are using here is 'Increment variable':
When adding to the sum of yesterday or the sum of today, you need the following expressions, respectively:
int(items('Apply_to_each_row')?['Yesterday'])
int(items('Apply_to_each_row')?['Today'])
Notice that we are using a math express 'int()'. Because the values we have in the datasets are all string values, we need to convert them into proper data formats to be able to do calculations. For values under "Yesterday" and "Today" columns, we need int(). For values under those two percentage columns, we need float() to transform the string values into decimal values, as follows:
float(items('Apply_to_each_row')?['CompletionRatio1'])
float(items('Apply_to_each_row')?['CompletionRatio2'])
Once the summations are complete, we need to get the total number of datasets available in the array. For this step, you can initialize another variable and store the value or simply use the action 'Compose':
The expression is pretty simple. We are using the 'length()' function to get the number of items in an array:
length(variables('testArr'))
Notice that variables('testArr') is pointing to the data variable I initialized, you might need to modify it for your case.
Now with the length available, we can do a final calculation and append the calculated values to the original data array. Action used here is 'Append to array variable':
For the calculated variables, here are the expressions:
string(variables('yesterdaySum'))
concat(string(div(variables('ratio1'),outputs('get_total_number_of_rows'))),'%')
string(variables('todaySum'))
concat(string(div(variables('ratio2'),outputs('get_total_number_of_rows'))),'%')
For sum of yesterday and today, we just need to convert the summed integer value back to string so it is consistent with the existing dataset values.
For averaged percentages, first we need to use 'div()' to divide the summed percentage by the number of datasets. Then we convert the calculated decimal value to string. After that, we use 'concat()' function to concatenate the string value with the percentage sign.
Once the array is finalized, we create an HTML table out of it in the last step:
The logic is pretty straightforward but we need to do some calculations and data type conversions here using some Power Automate functions. If you are interested in those functions, here is a document you can dig into (it is super long!): Power Automate Functions Reference
That was a lot of typing!
Hope this helps 🙂
Share
Report
All responses (
Answers (
