stonehage

Repeating Section. How to Extract and Split to Excel.

Blog Post created by stonehage on Oct 10, 2018

Hi!

 

One of the nice features of Nintex forms is an ability to create a repeating section within a single form. This allows you to add multiple lines of something, IE sales and then tally the rows up.

  

One of the problems is easily reporting on this row data. If you export to Excel you just get the ‘Rows’ within the form in a single field as XML.

 

I wanted to create an excel report that split out these xml fields into individual rows and be refreshable. Power Query to the rescue with a bit of help from my power query guru.

 

The following is a guide on splitting that data into individual rows using excel and power query and connecting directly to the Sharepoint list to create a refreshable report.

 

WHAT YOU NEED

First off you need your sharepoint list and make sure your repeating section is connected to a multiple line field to capture the XML. All of the fields you’ve added in the repeating section should be named.

 

You will then need Excel 2013 onwards with the latest version of PowerQuery.

 

REPORT

Within Excel, open up power query and connect to the sharepoint site where your list resides.

 

 

The url should be to the root of the site. Not the list.

 

Choose your list and click on edit

  

Add a custom column. This will point to the name of your repeating section field. In this example, Responses.

 

=Xml.Tables([Your Field Name])

 

Expand your custom column.

 

This will create a column to the right. Custom Items. Expand That. You should see all your fields from your repeating section appear.

 

 

Now you should have more rows and columns. The last step is to expand your extracted columns to see the data. These should all match your field names in the repeating section.

 

Here is my Assigned Column for example…

 

Expand all of your repeating section fields.

Once you’ve done that you can remove the columns you don’t want and click Close and Load.

 

As this is connected to the sharepoint list it can be refreshed as required.

Outcomes