Dynamically select columns from array

(0) Share

Report

Posted on by DJ_Jamba

2,837 Moderator

I have two arrays:
Array 1: "Columns Required"
["Name","Email","Model"]

Array 2:

[

{

"Name": "Nick",

"Email": "nick@nick.com",

"Manufacturer": "Audi",
"Model": "S3",

"Plate": "XYZ 1234",
"Colour": "Silver",

"Region": "South East",

"Country": "United Kingdom"
},

{

"Name": "Bob",

"Email": "bob@bob.com",

"Manufacturer": "Fiat",
"Model": "Punto",

"Plate": "ABC 987",
"Colour": "Black",

"Region": "North",

"Country": "France"
}

]

How do I SELECT the columns from Array 2 based on the desired columns in Array 1, when Array 1 can be any combination of column names?

Array 1 could be:
["Name","Email","Model"]

then on another occasion, it could be:

["Name","Manufacturer","Model","Country"]

Array 2 columns are static (always the same)

Categories:

General topics

All responses (5)

Answers (1)

Sort by

cchannon 4,702 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
First, Create an Array variable with your Array 1 values in it. You need to have a property name though, so it would look something like this:

[ {column: "Name"}, {column: "Email"}, {column: "Model"}, ]

Then, add an Apply to Each loop to loop over that Array 1 variable.

Then, it's just a matter of taking whatever actions you want with:

Array2?[thisLoopItem.column]
DJ_Jamba 2,837 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @cchannon

Thanks for your reply.
At first glance (in my head), I think you can only select from an array using index e.g.
Array2?[1]

But I'll give it a go by just outputting into a compose 🤞🏼
DJ_Jamba 2,837 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Unfortunately it didn't work 😞

The template language expression 'body('Array2')?[item()?['column']]' cannot be evaluated because property 'Model' cannot be selected. Array elements can only be selected using an integer index.
DJ_Jamba 2,837 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I'm looking at the removeProperty function and even though I can build the nested code successfully, it's a string and so cannot evaluate e.g.

removeProperty(removeProperty(removeProperty(item(), 'col1'), 'col2'), 'col3') ...

If i paste the string into a formula it works a charm!
Verified answer

DJ_Jamba 2,837 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Well I solved it by creating an array of unwanted columns, looped through that and used the removeProperty function to remove the columns that weren't required and it has indeed left me with the required columns.
So even though I can dynamically arrive at a set of data without knowing which columns are required, I got there using an incredibly ugly technique 🤣

Hopefully MS will introduce an Eval() function before I die...