For Each with a Filter into Excel

[Bahalzamon]

I am attempting create several tables of information into Excel through Power Automate.

I have a main export example below:

I am looking to create 2 additional tables from this type of data. I am wanting to create the 2 types of tables dynamically as the number of activities are not always 9, there can be any number and the same with status's.

What I originally attempted was an array of all the activities then a union on itself to get the unique values. Then a for each on that unique valued array applied to the arrayed list of records and a length on the filtered items to get the totals.

I am wondering 2 things.

1. Is there a better way? Like a way to have Excel do it or some action I don't know about.
   1. If so, some direction or link to a resource. 🙂
2. If I am going down the correct path how do I complete my task as I am unsure how to call these values as the OOB option doesn't show the options I need so I must type things in manually and I am obviously typing it in incorrectly.

[v-dezhili-msft]

Hi @Bahalzamon ,

Please can you tell me how many records are there in your excel table.
If you have more than 256 records, you can turn on the Pagination setting.
I have a simple test for your reference.

Best Regards,

Dezhi

[Chriddle · Accepted solution]

xpath has a count function that fits well here:

1. For simplicity, the data here is in a compose action
2. Convert data to XML
3. Get all activities
4. Make them unique
5. Let xpath do the count of each activity in a Select action

Data: Compose action

[
	{"Member": "John", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "Jim", "Activity": "Activity 2", "Date": "12/2/2022", "Status": "Started"},
	{"Member": "Bill", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "Smith", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "John", "Activity": "Activity 3", "Date": "12/2/2022", "Status": "On Hold"},
	{"Member": "Jim", "Activity": "Activity 4", "Date": "12/2/2022", "Status": "Started"},
	{"Member": "Bill", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "Smith", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "John", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "Jim", "Activity": "Activity 4", "Date": "12/2/2022", "Status": "On Hold"},
	{"Member": "Bill", "Activity": "Activity 2", "Date": "12/2/2022", "Status": "On Hold"},
	{"Member": "Smith", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "John", "Activity": "Activity 3", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "Jim", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "Bill", "Activity": "Activity 1", "Date": "12/2/2022", "Status": "Scheduled"},
	{"Member": "Smith", "Activity": "Activity 2", "Date": "12/2/2022", "Status": "Started"}
]

DataXML: Compose action with expression

xml(
	json(
		concat(
			'{"root":{"obj":',
			outputs('Data'),
			'}}')
	)
)

Activities: Select action

From:

 @{outputs('Data')}

Map (Switch Map to text mode):

 item()?['Activity']

UniqueActivities: Compose action with expression:

union(
	body('Activities'),
	skip(createArray(0), 1)
)

ActivityCount: Select action

From:

@{outputs('UniqueActivities')}

Map:

Key: Activity

Value: item() 

-

Key: Count

Value: 

xpath(
	outputs('DataXML'),
	concat(
		'count(//Activity[text()="',
		item(),
		'"])'
	)
)

Output:

[
  {
    "Activity": "Activity 1",
    "Count": 9
  },
  {
    "Activity": "Activity 2",
    "Count": 3
  },
  {
    "Activity": "Activity 3",
    "Count": 2
  },
  {
    "Activity": "Activity 4",
    "Count": 2
  }
]

[Chriddle · Accepted solution]

I've been playing Flow Golf with my flow above and here's my hole-in-one (action). 😉
Same idea but using xpath to create the Activity array and inserting it directly into the Select's From.

Also moving the conversion to XML into the expressions.

From: 

@{union(
	xpath(
		xml(
			json(
				concat(
					'{"root":{"obj":',
					outputs('Data'),
					'}}'
				)
			)
		),
		'//Activity/text()'
	),
	skip(createArray(0), 1)
)}

Map (text mode):

{
  "Activity": @{item()},
  "Count": @{xpath(
	xml(
		json(
			concat(
				'{"root":{"obj":',
				outputs('Data'),
				'}}')
		)
	),
	concat(
		'count(//Activity[text()="',
		item(),
		'"])'
	)
)}
}

[Bahalzamon]

That is incredibly impressive. I thought I understood Power Advanced pretty well, after seeing this that's a nope lol

Really easy to update based on the column name.

Just updating the tableActivity to the column I want to track and it spits out the totals.

union(xpath(xml(json(concat('{"root":{"obj":', variables('serviceArray'), '}}'))), '//tableActivity/text()'), skip(createArray(0), 1))

xpath(xml(json(concat('{"root":{"obj":', variables('serviceArray'), '}}'))), concat('count(//tableActivity[text()="', item(), '"])'))

[Chriddle]

Thx @Bahalzamon 

Since I was curious about how to create tables dynamically, I tried to complete your task.

You can extend this by adding more col names to the input array of "Apply to each CountCol".

This flow assumes that an empty "CountSheet" sheet exists in Excel.

Overall flow

 

Caluculate counts and create tables

By the way, I'm looking for a better way to calculate the table range.

Do you know how to avoid letters in these areas (or a more elegant way to calculate them?)

Counts

From:

union(
	xpath(
		xml(
			json(
				concat(
					'{"root":{"obj":',
					outputs('Data'),
					'}}'
				)
			)
		),
		concat(
			'//',
			items('Apply_to_each_CountCol'),
			'/text()'
		)
	),
	skip(createArray(0), 1)
)

Map:

{
  "@{items('Apply_to_each_CountCol')}": @{item()},
  "Count": @{xpath(
	xml(
		json(
			concat(
				'{"root":{"obj":',
				outputs('Data'),
				'}}')
		)
	),
	concat(
		'count(//',
		items('Apply_to_each_CountCol'),
		'[text()="',
		item(),
		'"])'
	)
)}
}

Add count rows

[Bahalzamon]

@Chriddle  Yeah I have a table in Excel that I clear out then repopulate with the data you helped be organize. I then run some scripts I made in the Excel file to create some graphs and then save them into a document for reporting.

I am off to look how to create a copy of the excel file to then do the work in it then delete it using the original as a template as the process of deleting and adding is slow which there is a possibility of multiple people doing it at the same time which will definitely throw the results off lol

Thank you again, it was a huge help. 🙂