Unanswered

Copy data between Sharepoint lists

(0) Share

Report

Posted on by mabl4367

I want to do a simple thing: I want to copy all records from one Sharepoint list to another.

The destination list was created using the "New", "List", "From existing list" option so the lists should have identical schemas.

I then started to create a Power Automate flow that would copy the data from the source list to the destination list using Get Items, iterate through each item and creating it in the destination list using For each and Create Item.

For simple lists with text columns this works fine but once the list contains columns of datatype "Choice" it no longer works.

The problem appears in the Create Item action:

For some reason static values from the first record in the source list is suggested as values for the Advanced parameters. I'm unable to select dynamic data from the source list columns. The source columns appears when selecting dynamic data but when I select any of them, nothing is entered into the Advanced parameter field.

After doing a test run of obviously incorrectly configured flow I was able to inspect the data I got back from Get Items. It appears that the filed called "Function" was previuosly called "Integration team" and that this renaming of the column has resulted in that the columns DisplayName has changed to "Function" but that its name is still "Integration_x0020_team".

So in order to get the correct value to the ne sharepoint list I need to create an expression like this: item()?['Integration_x0020_team']?['Value'].

This means that I cant simply rely on the GUI. For each column I need to dive into the output of Get Items to find out the correct name of the column. Also I can forget about making the flow general in the sense that I would like to define the source and destination site and lists as a parameters so that the flow could be used to copy data between any lists. This will not be possible since the column names of the lists may be different for each list.

Why is it so darn difficult to do something that should be so simple!?

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!

HI,

So a few things

1. It's not simple because you are looking at single "string" names and saying the UI says this but the back end says that. I agree to a point.

Back-end Schema names are URL compatible, so they have to encode the spaces in the Label (aka name) that is typed the first time the list is created.

But the schema end in the back is intentionally static, regardless of whether you change the "Label" in the UI. So no you should never assume that string means anything more, than someone's fancy at the time they created it, or renamed it.

2. Choices are a pain period, since (and I dont know your situation) but Single versus Multi-choice have to follow the format structure defined for those objects, so passing over (primarily multi-select) can be a bother, but its up to the person to create the JSON necessary in the request

3. Create a singular tool in Power Automate, is technically possible, just more.... dutiful needs have to happen. Use the HTTP requests to SharePoint to get column schema names, and data type etc so you can do whatever it is you want. By looping through them, grabbing the values, putting them in an Array, then use the array to dynamically assign to the target.

But of course its near impossible to do this anyway without having a configuration setup that will populate what dynamic Target is being used, same with the source.

All possible, but ETL itself isn't "simple", unfortunately neither is this.

NOTE: the same exact thing has been around 30 years with Dynamics and Power Platform. Once you Name them the logical and schema names (in the back end) do not change, only the String up front can be changed.

I will say this, you were absolutely doing the right thing by using Create from Existing List.

Importing from Excel just names everything Field1/2/3/4 bleh and not all important templates you create will do it correctly either.

Was this reply helpful? Yes No
BabyBots 243 on at

Like (0)

Report
Copy link

Link copied!
This can be extremely frustrating, I completely get it.
The issue stems from the fact that SharePoint maintains an internal name for each column (which doesn't change even if the display name does). Power Automate’s GUI often struggles with these internal names, forcing you to inspect the raw JSON output from Get Items.

A few things that might help:

Use the Peek Code option – When selecting dynamic content, try using "Peek Code" (right-click on the field) to see the actual JSON path it expects. This can save you from manually checking the Get Items output each time.

Manually referencing internal names – As you discovered, instead of relying on the UI, manually referencing fields like item()?['Integration_x0020_team']?['Value'] ensures the correct data is pulled. Sadly, this extra step is unavoidable due to how SharePoint handles renaming.

Standardize column names – If possible, avoid renaming columns after they’ve been created. Since SharePoint locks in the internal name at creation, changing it later only affects the display name, leading to these mismatches.

Using a configuration list – If you need flexibility across multiple lists, consider storing a mapping table in a SharePoint list or an Excel file, where you define the source-to-destination column mappings. Then, use that in your flow to dynamically resolve column names.

Expression-based solutions – Since you can’t rely on the GUI, a general approach would be to use json() or coalesce() expressions to handle variations dynamically, but that still requires custom logic for different lists.

It’s definitely frustrating, but once you get familiar with SharePoint’s quirks in Power Automate, it becomes easier to work around these limitations.

🚀 Automate. Optimize. Innovate. 🚀

Helping businesses streamline workflows and maximize efficiency with Microsoft Power Platform. Let’s transform your processes and unlock new possibilities!
Explore BabyBots.ai

Was this reply helpful? Yes No