There was a similar scenario yesterday in the forums where I used XML and XPath to get what they wanted. This sort of thing is perfect for XPath as it can avoid using loops making it much more efficient.
Below is the List I'm using for this example. Note that it has multi-select Choice (Countries) and Person (Members) columns.
See full flow below. I'll go into each of the actions.
Get items retrieves all the items from our list.
XML is a Compose that converts our JSON output from Get items to XML. Note that the expression also adds a root element to ensure it's valid XML. The expression used is below. Getting our data into XML means we can use XPath in the next step to collate values across rows.
xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))
Below is a sample of what the list data would look like in XML.
Create CSV table uses XPath expressions to build up our table.
The expression we use as the input (From) data is below. Effectively, it's getting the value property from our XML which is our array of items.
xpath(outputs('XML'), '//root/value')
The Title and Description values are Single line of text columns so we can use the following expressions to get these.
xpath(item(), 'string(//Title/text())')
xpath(item(), 'string(//Description/text())')
Status is a single-select Choice column, so we also need to add /Value to get the actual value.
xpath(item(), 'string(//Status/Value/text())')
Countries is a multi-select Choice column, so we first need to get the collection of Countries including the /Value, then join them with ', '.
join(xpath(item(), '//Countries/Value/text()'), ', ')
Members is a multi-select Person column, so we first need to get the collection of Members including /DisplayName to get the actual name of the person, then join them with ', '
join(xpath(item(), '//Members/DisplayName/text()'), ', ')
This gives us our CSV table with all our data.
Finally, we can add the CSV table to a Send an email action as an attachment.
And below, the CSV data in the attachment in the email.
