Condense a Single Array Across Multiple Fields with a Key Value

[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

[efialttes]

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!

[Chriddle · Accepted solution]

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:

[Trent92]

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

[ 

  {"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. 

[Chriddle]

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). 😉

[Trent92]

Works beautifully now. Thank you for the help