Skip to main content

Notifications

Power Automate - General Discussion
Suggested answer

Create Array from SharePoint Columns

(1) ShareShare
ReportReport
Posted on by 10
 
Hello team, I hope you can help me here, I have a SharePoint list, it has multiple items, but basically one item looks like the one below:
 
 
The question is if there is a way to create an array like the one below:
 
[
{
Column A: Item Z
Column B: cat 2
Column C: 5
},
{
Column A: Item B
Column B: cat 4
Column C: 8
},
{
Column A: Item A
Column B: cat 1
Column C: 9
}
]
Categories:
  • CU27091846-0 Profile Picture
    CU27091846-0 10 on at
    Create Array from SharePoint Columns
    Thank you for the response,
     
    The information comes from a SharePoint text column. (3 columns). I wish I could share screenshots, but I am not allowed due to confidentiality company rules. Then I am using a select splitting the text by the coma, that is why you see the information in an array form.
     
    [
      {
        "Column A": [
          "Item Z",
          "Item B",
          "Item A",
        ],
        "Column B": [
          "cat 2",
          "cat 4",
          "cat 1",
        ],
        "Column C": [
          "5",
          "8",
          "9",
        ]
      }
    ]
     
    From this step I am stuck to merge the arrays into one, the items and the order will always match, meaning the first value from Column A goes with the first value of Column B, and the first value of Column C.
     
    I understand if the lack of flow images makes my explanation complicated.
  • Expiscornovus Profile Picture
    Expiscornovus 31,139 on at
    Create Array from SharePoint Columns
    Based on your output I would say these columns are actually multi choice fields. Each column has returned an array, hence the [] around the values.
     
    Can you try the first approach I shared?
     
    And if it doesn't work please share your flow setup, like FLMike already suggested. This makes it easier for us to suggest an approach/troubleshoot.
  • Suggested answer
    FLMike Profile Picture
    FLMike 31,580 on at
    Create Array from SharePoint Columns
    HI
     
    Yes its critical that you share your flow, how you get the data, where it comes from etc etc.
     
    Or as you can see it makes it impossible for folks to help.
  • CU27091846-0 Profile Picture
    CU27091846-0 10 on at
    Create Array from SharePoint Columns
    I really apologize, I really thought that I was going to be able to fit your solution into my flow, but I am not able to, maybe I should've mention that the data is coming from a select function and the output is like this:
     
    [
      {
        "Column A": [
          "Item Z",
          "Item B",
          "Item A",
        ],
        "Column B": [
          "cat 2",
          "cat 4",
          "cat 1",
        ],
        "Column C": [
          "5",
          "8",
          "9",
        ]
      }
    ]
     
    Can you please help? I guess I should create a new post and mark this one as resolved.
     
    Thank you for any help,
  • Expiscornovus Profile Picture
    Expiscornovus 31,139 on at
    Create Array from SharePoint Columns
    Like mentioned in my previous reply. In that case you can use the split function as well. The approach is pretty similar.
     
    Below is an update example for the text type of column.
     
    The apply to each uses the expression below to determine the max number of loops
    range(0, max(createarray(length(split(triggerOutputs()?['body/ColumnA'],',')),length(split(triggerOutputs()?['body/ColumnB'], ',')),length(split(triggerOutputs()?['body/ColumnC'], ',')))))
    The Append to array variable action uses the below json. It uses the current item as an index number to retrieve the correct value. I have also added a trim function because some values are separated by ', ' instead of ','
     
    {
      "Column A": "@{trim(split(triggerOutputs()?['body/ColumnA'],',')?[item()])}",
      "Column B": "@{trim(split(triggerOutputs()?['body/ColumnB'],',')?[item()])}",
      "Column C": "@{trim(split(triggerOutputs()?['body/ColumnC'],',')?[item()])}"
    }
     
     
  • CU27091846-0 Profile Picture
    CU27091846-0 10 on at
    Create Array from SharePoint Columns
    Hello,
     
    Thanks for the reply, sadly all the columns are text. Can you please help?
     
     
  • Expiscornovus Profile Picture
    Expiscornovus 31,139 on at
    Create Array from SharePoint Columns
    Are you column A, B and C multi-choice fields? If that is the case you could use an array to create one big array. If these are text fields you might want to use a split function as well.
     
    Below is an example which assumes these are multi-choice fields.
     
    The apply to each uses the expression below to determine the max number of loops
    range(0, max(createarray(length(triggerOutputs()?['body/ColumnA']),length(triggerOutputs()?['body/ColumnB']),length(triggerOutputs()?['body/ColumnC']))))
    The Append to array variable action uses the below json. It uses the current item as an index number to retrieve the correct value. 
    {
      "Column A": "@{triggerOutputs()?['body/ColumnA']?[item()]?['Value']}",
      "Column B": "@{triggerOutputs()?['body/ColumnB']?[item()]?['Value']}",
      "Column C": "@{triggerOutputs()?['body/ColumnC']?[item()]['Value']}"
    }
     
    The test result
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,636

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,942

Leaderboard