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

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?

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.

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

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.

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

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(sthe Name of the column where the XML file is located])
in my case it is sFreightInventory]
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 .. :/

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.

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

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.

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.

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

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? 

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.

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.

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.

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:

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

Brilliant!

I'll add this option to the article.

Thanks again!

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.

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.

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

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.

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?

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

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.

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