Answered

Condense a Single Array Across Multiple Fields with a Key Value

(0) Share

Report

Posted on by Trent92

Hello all,

I am trying to replicate the feel of a Power BI Matrix table via a flow as Automate doesn't currently accept Matrix DAX queries, only Table DAX queries.

To that end I have created a flow that runs an Apply to Each dataset query which creates the the individual columns (in this case managers of projects) compared to the Statuses of those projects down the rows. This array is appended within the loop and finally turned into a HTML table to be sent as an email. However the interaction between the appended array and the apply to each loops results in a table like looks like this:

Development Status Manager One Manager Two Manager Three

	1
Completed	2
Draft One	1
Final Pass	1
In Progress	11
New	18
On Hold	2
Rejected	2
		1
Draft One		1
Final Pass		1
In Progress		8
New		1
In Progress			9
New			2
On Hold			3

While the cascading waterfall looks nice it's not really a good table to be sent out.

Is there a way to create an array that would condense the values down into a single row for each Status, with the columns working across it by Manager?

I have already tried to Union() the array to itself but as each item is different nothing seemed to happen.

Thanks in advance

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

efialttes 14,756 on at

Like (0)

Report
Copy link

Link copied!

Hi @Trent92 !
Can you provide an screenshot from your current flow design, so we can see both the most relevant action blocks and also their current inputs?
The more info you provide, the fast you will get a useful response!
Thanx!

Was this reply helpful? Yes No
Verified answer

Chriddle 8,685 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Reorganizing data structures works fine with xPath:

Data is a Compose action with the combined datasets (I assume it looks similar to this 😉)

[ {"Status": "Completed", "One": 2}, {"Status": "Draft", "One": 2}, {"Status": "Final", "One": 2}, {"Status": "Progress", "One": 2}, {"Status": "New", "One": 2}, {"Status": "Hold", "One": 2}, {"Status": "Rejected", "One": 2}, {"Status": "Completed", "Two": 1}, {"Status": "Draft", "Two": 1}, {"Status": "Final", "Two": 1}, {"Status": "Progress", "Two": 8}, {"Status": "New", "Two": 1}, {"Status": "Progress", "Three": 9}, {"Status": "New", "Three": 2}, {"Status": "Hold", "Three": 1} ]

Select:

From:

["Completed", "Draft", "Final", "Progress", "New", "Hold", "Rejected", "Completed"]

Map Status:

item()

Map One:

first( xpath( xml(json(concat('{"root":{"item":', outputs('Data'),'}}'))), concat('//item[Status="',item(),'"]/One/text()') ) )

Map Two:

first( xpath( xml(json(concat('{"root":{"item":', outputs('Data'),'}}'))), concat('//item[Status="',item(),'"]/Two/text()') ) )

Map Three:

first( xpath( xml(json(concat('{"root":{"item":', outputs('Data'),'}}'))), concat('//item[Status="',item(),'"]/Three/text()') ) )

Create HTML table

From: @{body('Select')}

Result:

Was this reply helpful? Yes No
Trent92 13 on at

Like (0)

Report
Copy link

Link copied!

I'm trying to action this but it keeps telling me that I have an invalid template, although I'm not sure where I have gone wrong compared to your example:

My Compose 2 action displays the data the same as yours:
Spoiler (Highlight to read)
[
  {"Status": "", "Manager One": 1},
  {"Status": "Completed","Manager One": 2},
  {"Status": "Draft One","Manager One": 1},
  {"Status": "Final Pass", "Manager One": 1},
  {"Status": "In Progress", "Manager One": 11},
  {"Status": "New", "Manager One": 18},
  {"Status": "On Hold", "Manager One": 2},
  {"Status": "Rejected", "Manager One": 2},
  {"Status": "", "Manager Two": 1},
  {"Status": "Draft One", "Manager Two": 1},
  {"Status": "Final Pass", "Manager Two": 1},
  {"Status": "In Progress", "Manager Two": 8},
  {"Status": "New", "Manager Two": 2},
  {"Status": "In Progress", "Manager Three": 9},
  {"Status": "New", "Manager Three": 2},
  {"Status": "On Hold", "Manager Three": 3}
]
[   {"Status": "", "Manager One": 1},   {"Status": "Completed","Manager One": 2},   {"Status": "Draft One","Manager One": 1},   {"Status": "Final Pass", "Manager One": 1},   {"Status": "In Progress", "Manager One": 11},   {"Status": "New", "Manager One": 18},   {"Status": "On Hold", "Manager One": 2},   {"Status": "Rejected", "Manager One": 2},   {"Status": "", "Manager Two": 1},   {"Status": "Draft One", "Manager Two": 1},   {"Status": "Final Pass", "Manager Two": 1},   {"Status": "In Progress", "Manager Two": 8},   {"Status": "New", "Manager Two": 2},   {"Status": "In Progress", "Manager Three": 9},   {"Status": "New", "Manager Three": 2},   {"Status": "On Hold", "Manager Three": 3} ]
I then run the select action and have modifed the the expression to reflect the way it's been set up for my data:
But it always returns the same error:
I'm not sure what I am doing differently.

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

Like (1)

Report
Copy link

Link copied!
That's because of the spaces in your property names (Manager One, Manager Two, Manager Three).

Since the data is converted to XML, and spaces are not allowed in XML's node names, these spaces are replaced with _x0020_ there.

The correct xPath expression is

first( xpath( xml(json(concat('{"root":{"item":', outputs('Data'),'}}'))), concat('//item[Status="',item(),'"]/Manager_x0020_One/text()') ) )

Since there are many other problems with spaces in property names, I always try to avoid them (as I did in my example). 😉

Was this reply helpful? Yes No
Trent92 13 on at

Like (0)

Report
Copy link

Link copied!

Works beautifully now. Thank you for the help

Was this reply helpful? Yes No