cancel
Showing results for 
Search instead for 
Did you mean: 

Repeating Section. How to Extract and Split to Excel.

Workflow Hero
6 21 1,401

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.

21 Comments
Workflow Hero

that is an excellent workaround
Shanks for sharing this with us ...

Do you happen to know if it's possible to update the column with the data instead of creating new ones?
For example, I have a column in the list called PartNumber and would be intrested to know if I can say "update the column PartNumber with the [PartNumber] from the repeating section field called [Inventory].

Workflow Hero

Hi!

Not sure exactly what you are trying to do but I've done a few uploads into Sharepoint lists with a repeating section and auto generated the XML. Usually via a SQL script or via workflow from another list. This works well until I have to update that repeating section. I think perhaps this is your problem?

For example, I have built a Staff Movement process. It has a parent form and corresponding child tasks listed in a repeating section. These are generated via workflow from a lookup list of required tasks. The problem occurs when I have a new requirement to add. It's great for new forms but for those in flight, rebuilding the repeating section nukes what is there already.

The only way I can imagine doing it is to split the data back out via XML which would include a row number, update that rows xml and then rebuild it back. Not something I've fancied tackling yet .

Or am I way off your issue?

Workflow Hero

You went over the topic lol 

I was able to follow the above instruction using the PowerQuery, which creates new columns for each <item> within the XML format. This all good and well BUT my question was: if is possible to use same approach via PowerQuery but instead to asking to create new columns for each <item> rather use existing columns within the list?

of course those columns are blank by default.

Workflow Hero

Simply add another custom column at the end of the power query process?

Workflow Hero

Hi,

Well, your process involves creating a new column.
again, I am saying is it possible to ask PowerQuery, to update existing column with the data from the XML ?

Like this:

Whereas your instructions (which work by the way) created a new column referencing to the FreightInventory column and then it creates separate column for each item within the XML inventory.

Workflow Hero

No I'm not getting it. I think I'm being thick  Are you saying the when splitting your XML column, you're getting no data in the split columns?  You need to scroll to the right and expand your split out columns by clicking on the icon shown below. They should look like this..

Workflow Hero

I am sure you are not thick  but I am not able to put my question across to you.
Right, let's try from a different angle...

When following your instructions:

1) I create a new column to fetch the data =Xml.Tables([the Name of the column where the XML file is located])
in my case it is [FreightInventory]
2) then you go to expand the data as you highlighted per the screenshot.

All this works fine, and the data is displayed.
However, my question is ... is it not possible to use already predefined columns such as PartNumber (in my scenario) and have the PartNumber from the XML populated in that column ... instead of having this automated Cosutm.Items.Item.... bla bla bla.

I am assuming this is not possible but yeh "silly question" I know .. :/

Workflow Hero

I'm really sorry but I'm not getting it . Perhaps it's time for a for a form screenshot an excel screenshot and then what you are hoping for screenshot in excel.

Workflow Hero

this will never end lol

The screenshot below is when I load the list from the URL.
(the step: Choose your list and click on edit)

Now from this point and onwards >
how do I get the XML data into each specified column (see the red arrows).

Remember: your steps involve to create a new column and reference it to the XML column, which then by expanding the splits it generates the <items> into columns and this approach works well and have no complaints about it.

but but but ... my questions is populating the <items> into the pre-existing columns like: PartNumber | PartDescription

Workflow Hero

Well I think if you've done it correctly with adding the custom column, after that step it gives you the Custom Colum to expand. Expand that and then far to the right more columns appear with the custom column name appended to the front. Expand those show the data from the XML.

If you want, attach your xml data in an excel spreadsheet and I'll have a look.

Workflow Hero

Ok, so please find attached to my original post an excel sheet that I've put together. In this example I export from the sharepoint list into Excel, and used that as the source. Interestingly a couple of observations. I did have to expand the custom column twice, to see my XML fields and all came through expect the comments.

Workflow Hero

This is brilliant Anthony!! What an awesome way to do this.

Workflow Hero

Love the example.  Any idea how to deal with look-up columns using Power Query?  This is not necessarily within the repeating section (although someone might have look-ups in their repeating section). The Excel data query is showing the ID of the item in the look-up column, not the value.  Same problem with all the "people" fields; SharePoint ID instead of the name. I know SharePoint fixes those if you download to Excel from SharePoint, but anyway to fix them via a Power Query into the list? 

Workflow Hero

Hi. Not that I've found. From searching, the full Power BI app seems to allow you to expand the columns. For power query I think you'd need to bring in your lookup lists as well into the workbook and then do some lookups. Maybe even a simple vlookup after loading.

T.

Workflow Hero

Yep, I was afraid of that.  I'm not the best at Excel functions.  But I've passed the info to the analytics teams that setup reporting attached to the lists and maybe they can figure it out.  Thanks for replying.

Workflow Hero

Actually, I was able to add the Power Query you described above to the Excel download that is created using out-of-box SharePoint functionality.  Then you don't have to build the Power Query from scratch, but can start with an existing view and that takes care of mapping the look-ups and people fields in the SharePoint list. 

To do this, click the "Export to Excel" button in the List menu while in the view you want to download.  Be sure that view includes the list column that contains your repeating section data. Save the file and open it.  Then on the Data tab in Excel, find the Get & Transform Data group and select "From Table/Range" instead of getting new data.

From Table/Range button

The Power Query window will open with the all the columns you have just downloaded and you can use the steps above to create the custom column and expand out the XML in your repeating section to create multiple rows.  Then you can hide the XML column itself and you'll have a nicely formatted table:

Excel Example Thanks for a great article, my data analysts will love this functionality.

Workflow Hero

Brilliant!

I'll add this option to the article.

Thanks again!

Workflow Hero

Hmm I thought I would see if I can do the same but ... is grayed out?

I am using Office 2013 by the way.
Indeed, downloaded the exported list to desktop and opened it in Excel.

Though, I do have PowerQuery installed already.

Workflow Hero

You need to select a cell on your table which should have data from sharepoint and then under the powerquery section choose from table \ range.

Workflow Hero

Ah, I see!
This is just a shortcut to get to the PowerQuery function.

am I right to believe that in such way, the user does not need to install PowerQuery application??

Workflow Hero

If you're just after exported data from a SharePoint list, then yes you don't need power query. You just export to excel. You need power query to split out the repeating section.