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.
DATA SOURCE
You have a couple of options here...
Option1. If your list has lookups they might come through as ID's. To get around this use option 2 below.
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 to load the list.
Option 2. ( Thanks Cam )
Export your SharePoint list ( including your repeating section column of course ) into excel using the export to excel option from the SharePoint list ribbon control.
Save the file and open it. Then on the Data tab in Excel, click anywhere within the table and under PowerQuery find the Get & Transform Data group and select "From Table/Range" instead of getting new data.
The Power Query window will open with the all the columns you have just downloaded and you can continue on.
EXTRACTING THE REPEATING SECTION
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.
I've attached an example that's using SharePoint data in an excel sheet as the source.
