cancel
Showing results for 
Search instead for 
Did you mean: 

Repeating Section. How to Extract and Split to Excel.

Stonehage
Nintex Newbie
7 26 7,403

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.

26 Comments
bimi82
Nintex Newbie

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].

Stonehage
Nintex Newbie

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?

bimi82
Nintex Newbie

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.

Stonehage
Nintex Newbie

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

bimi82
Nintex Newbie

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.

Stonehage
Nintex Newbie

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..

bimi82
Nintex Newbie

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 .. :/

Stonehage
Nintex Newbie

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.

bimi82
Nintex Newbie

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

Stonehage
Nintex Newbie

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.

Stonehage
Nintex Newbie

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.

Nintex Employee
Nintex Employee

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

clrowe1124
Nintex Newbie

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? 

Stonehage
Nintex Newbie

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.

clrowe1124
Nintex Newbie

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.

clrowe1124
Nintex Newbie

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.

Stonehage
Nintex Newbie

Brilliant!

I'll add this option to the article.

Thanks again!

bimi82
Nintex Newbie

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.

Stonehage
Nintex Newbie

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.

bimi82
Nintex Newbie

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??

Stonehage
Nintex Newbie

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.

marven
Nintex Newbie

This is brilliant, avoiding complicated workflows or even understanding XML is what I am all about. Thank you. But......

I had  to take it a step further, and failed. I have an embedded repeated section in my first repeated section. I try to follow the instructions again for that section but it keeps throwing up errors. Am I just pushing Excel too hard? I even tried copying values olnly from the first extraction onto a new sheet and tried it that way to no avail. Any ideas?

Stonehage
Nintex Newbie

What are your errors? Have you compared your output to my example workbook above?

Stonehage
Nintex Newbie

Couple of things I've noticed just doing this again. If you have a null in your repeating section data, either remove it or replace null with an empty xml section. I was getting errors with nulls in my data.

When expanding the Custom column, you may have to expand and then expand again before the columns appear.

yvette
Nintex Newbie

Thank you for this solution! I've used this often.

 

However, I am now facing a challenge: can you tell me how to get this to work when the repeating section is not connected to a multiple line text list column?

 

I would think I should be able to access the FormData and thus the RepeaterData XML in Power Query / Power BI? But have not been able to make it work. I even tried downloading the XML seperately and uploading this XML file into a seperate column - but how do I get this XML? I tried REST, ListData.svc etc. but have not managed it.

 

I can of course configure a workflow with a Query XML action and use that to write the RepeaterData XML back to a list column - but am looking for an easier solution as I have a couple of lists to report on (all without the repeating section connected to the list!). Any thoughts would be appreciated, thanks. Smiley Happy

Stonehage
Nintex Newbie

Smiley Happy

 

Hi, no I'm unaware of a simple way. As you mentioned, using a workflow to get the data copied to another filed would be my solution as well.

 

T.