Unanswered

Power Automate Array logic

(0) Share

Report

Posted on by SP14

HI, I have an array in the below format and want to the get it in a table format. Using Power automate to send email as a tabular structure.

Input format:

[
{
"Name": "User1",
"Category": "General",
"Hours": "32.0000000000000"
},
{
"Name": "User2",
"Category": "General",
"Hours": "8.00000000000000"
},
{
"Name": "User3",
"Category": "Stage 7 - Develop Project Tools & Templates",
"Hours": "3.00000000000000"
},
{
"Name": "User4",
"Category": "Stage 7 - Develop Project Tools & Templates",
"Hours": "3.00000000000000"
},
{
"Name": "User1",
"Category": "Stage 7 - Develop Project Tools & Templates",
"Hours": "4.00000000000000"
},
{
"Name": "User2",
"Category": "General",
"Hours": "5.00000000000000"
},
{
"Name": "User3",
"Category": "General",
"Hours": "5.00000000000000"
},
{
"Name": "User4",
"Category": "Stage 2 - Develop Draft Frameworks",
"Hours": "7.00000000000000"
}
]

------------------------

OutPut format:

UserName	Category	TotalHours
User1	General	3.5
User1	Stage 2 - Develop Draft Frameworks	8
User1	Stage 3 - Change Management Plan	14.5
User1	Stage 4 - Project Assurance Framework	5.5
User1	Stage 6 -Project Process Mapping	10
User1	Stage 7 - Develop Project Tools & Templates	8.5
Total Hours	50
User2	General	2.5
User2	Stage 4 - Project Assurance Framework	2
User2	Stage 5 - Data Mapping - Define reporting criteria	6.5
Total Hours	11
User3	Stage 2 - Develop Draft Frameworks	38
User3	Stage 6 - Project Process Mapping	35
Total Hours	73
User4	General	1
User4	Stage 7 - Develop Project Tools & Templates	15
Total Hours	16

Need help in achieveing that output, Community please help

Thanks

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (0)

Chriddle 8,515 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Use action "Create HTML table"

Was this reply helpful? Yes No
SP14 19 on at

Like (0)

Report
Copy link

Link copied!

Hi Chriddle,
I have used HTML action however it is giving a table but inserting a row for each item in List. I want to consilidate for each user based on the category and count number of hours.

Thanks

Was this reply helpful? Yes No
Chriddle 8,515 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

So your problem is not about creating a table, but restructuring/combining the object array.
Let's summarize:
You want to add all hours per user and category (I don't get why e.g. User2 | General adds up to 2.5, in general your input and the output table don't match), sort this by user and sum the total hours per user.
Is this correct?

Was this reply helpful? Yes No
SP14 19 on at

Like (0)

Report
Copy link

Link copied!

Hi Chriddle,
yeah that's true sort by the user, Get categroy and then hours associated to that category.

In Tabular form

Thanks

Was this reply helpful? Yes No

Chriddle

8,515 Super User 2026 Season 1 on at

Like (1)

Report

Copy link

Link copied!

Took some effort

Input of Apply to each User:

sort(
	union(
		xpath(
			xml(json(concat('{"root":{"User":', outputs('Users'),'}}'))),
			'//Name/text()'
		),
		json('[]')
	)
)

ProjectsByUser: Filter action

From: @outputs('Users')

Filter:

@equals(item()['Name'], items('Apply_to_each_User'))

ProjectsCombined: Select action

From:

sort(
	union(
		xpath(
			xml(json(concat('{"root":{"ProjectName":', outputs('ProjectsByUser'),'}}'))),
			'//Category/text()'
		),
		json('[]')
	)
)

Map:

{

"Name": @{items('Apply_to_each_User')},

"Category": @{item()},

"Hours": seebelow

}

xpath(
	xml(json(concat('{"root":{"User":', outputs('Users'),'}}'))),
	concat('sum(//User[Name/text()="',items('Apply_to_each_User'),'" and Category/text()="', item(),'"]/Hours/text())')
)

Input of Apply to each Project: @{body('ProjectsCombined')}

Append to array variable

Name: resultObject

Value: @item()

Append to array variable 2

Name: resultObject

Value:

{"Name": "Total Hours", "Category": "", "Hours": @{xpath(
	xml(json(concat('{"root":{"User":', outputs('Users'),'}}'))),
	concat('sum(//User[Name/text()="',items('Apply_to_each_User'),'"]/Hours/text())')
)}}

Create HTML table Output:

Name Category Hours

User1	General	32
User1	Stage 7 - Develop Project Tools & Templates	4
Total Hours		36
User2	General	13
Total Hours		13
User3	General	5
User3	Stage 7 - Develop Project Tools & Templates	3
Total Hours		8
User4	Stage 2 - Develop Draft Frameworks	7
User4	Stage 7 - Develop Project Tools & Templates	3
Total Hours		10

Was this reply helpful? Yes No

SP14 19 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your input, I'll try to replicate the same at my end and let you know if I need any further assistance

Thanks

Was this reply helpful? Yes No