Transpose Columns To Rows

[musicman101]

Hello,

I want to create a flow that can transpose an existing list so that the column headers appear as separate rows with the data appearing in columns. Below is a test list I created. The number of data will vary so there won't be just one column's worth of data and it will vary. 

The goal would be to display the data like this.

I've seen a similar approach on this forum but it was just regarding one line of data at a time, rather than varying lengths. 

Is this possible? Thanks for your help in advance!

[edgonzales]

@musicman101 ,

Hi there.  Just checking to see if a Dataflow is an option here?  Not certain of the rest of the requirements, but without context, it feels like that might be the most viable and efficient approach.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[grantjenkins · Accepted solution]

Hopefully this is what you're looking for. It will transpose the columns you specify and create a CSV file saved on SharePoint.

For this example, I'm using the following SharePoint List. You can have whatever columns you want and any number of rows.

See full flow below. I'll go into each of the actions.

Get items retrieves my list items.

XML is a Compose action that converts the data to XML so we can use XPath expressions.

xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))

A sample of the XML output is below showing the actual field names. XML can sometimes alter the names depending on spaces, special characters, etc.

Headers is a Compose action where you specify what fields you want to use in your final output. It has an object for each field. Name will be the internal name of the field as per the XML (as shown in XML output above). Title is the actual display name you want to display in your CSV output.

[
    {
        "Name": "Artist",
        "Title": "Artist"
    },
    {
        "Name": "Title",
        "Title": "Title"
    },
    {
        "Name": "CatNumber",
        "Title": "Cat Number"
    }
]

Select uses the output from Headers and the following expression that performs the transposing of the data including prepending the Header Title.

concat(item()?['Title'], ',', join(xpath(outputs('XML'), concat('//root/value/', item()?['Name'], '/text()')), ','))

Join uses the output from Select and joins each row with a new line character which is required for a valid CSV file.

//New line character

decodeUriComponent('%0A')

Create file uses the output from Join and uses the following expression for the File Name. You can use whatever name you like for your scenario. Note that it needs to have .csv as the file extension.

@{formatDateTime(utcNow(), 'yyyyMMdd-hhmmss')}.csv

After the flow runs, we will have a new CSV file containing the transposed data.

The only actions you need to modify are the Get items, Headers, and Create file, to cater for your specific data and output. XML, Select, and Join don't need to be modified.

[musicman101]

Thanks a lot @grantjenkins that's perfect!

One last question, I'd like to send the export via email as a CSV.

I've added the 'Create CSV Table' function and then 'Send an Email Notification' to send the file to the end of the flow. However, I receive an error due to the data being in a string format rather than an array. Is there an easy way to get around this issue?

Thanks again for your help, really appreciate it.

[grantjenkins]

Can you try wrapping the output from the Join in a json expression.

[grantjenkins]

Actually, you can probably just use the output from the Select to create your CSV table.

[musicman101]

Thanks. I tried that but sadly get the error message below.

"Unable to process template language expressions in action 'Create_CSV_table' inputs at line '0' and column '0': 'The template language function 'json' parameter is not valid. The provided value 'Artist,The Rolling Stones,The Rolling Stones,The Beatles,The Beatles,The Beatles,The Beatles,The Beatles
Title,Exile On Main Street,Exile On Main Street,Abbey Road,Abbey Road,Abbey Road,Abbey Road,Abbey Road
Cat Number,TEST002,TEST001,Apple001,Apple002,Apple003,Apple004,Apple005' cannot be parsed: 'Unexpected character encountered while parsing value: A. Path '', line 0, position 0.'. Please see https://aka.ms/logicexpressions#json for usage details.'."

[musicman101]

I just gave that a go but I get an error at the 'Create CSV Table' stage of "The property 'columns' must be specified unless the 'from' property value is an array of objects."

[grantjenkins]

The Join is already effectively a CSV table, so you just need to include that for the Attachments Content within your Send an email.

[musicman101]

Brilliant, thanks for your help @grantjenkins !

[musicman101]

Thanks Ed, I don't have a lot of experience in either I have to admit but will do some digging. Appreciate your help 🙂