Create/Map Array from Array String

[Bogdan]

Hello Powerusers,

I sadly cannot get along with this. So I hope I can get some direction from here:
 I have:

[
"Device Number": ["12324","67890"],
"Device Type": ["abc","xyz"]
]

And I need:

[
	{
	"Device Number": "12324",
	"Device Type": "abc"
	},
	{
	"Device Number": "67890",
	"Device Type": "xyz"
	}

]

How on earth can I do this in MS Flow? I am getting nuts with it 😞
Thank you in advance ! Cheers !

[eliotcole]

Firstly, can we see what you have so far in your flow, please?

Make sure that you blank out any sensitive data, and edit it into the original post.

Secondly, and purely for your information, there's a chance that you might need to use a select action here (which is the equivalent of an array map) with some expressions containing xpath() with xml() trickery involved, however it should be possible.

Thirdly; One question ... this first code block that you posted:

[
  "Device Number": ["12324","67890"],
  "Device Type": ["abc","xyz"]
]

Can I ask where that's from? Only I'm just curious if it's a mistype (no offence!), or it's the output of a program that kinda does its own thing! 😅

If it's a mistype, should that have been set as an object, thusly?

{
  "Device Number": ["12324","67890"],
  "Device Type": ["abc","xyz"]
}

Sorry, just trying to understand how you have got the text that you have.

There's a reason, because if this is text that you've generated yourself, then we can help (after seeing the flow) by generating that properly. If it's what a program provides, then I would assume we'll be clipping the first and last lines, then working the previously mentioned select xpath stuff. 🙂

Let us see that flow and if you can lemme know about that first code block. 👍

[Bogdan]

Hy,
Thank you for the reply! 🙂
To answer your questions:

Firstly - I changed it already so much, that it is a mess.

Secondly - I might have thought that it can be done with "Select" and such.

Thirdly: Yes I wrote it by hand 🙂

But I wil try to explain the workflow.

Data comes from a Powerapp Collection. It looks like this:

{
  "Gerätetyp": "Notebook,Handy",
  "Bezeichnung": "200WS,200WS",
  "Gerätenummer": "WS_Test_Notebook,A_Test_Handy",
  "Hersteller": "Lenovo,Apple",
  "Datum": "10/28/2021"
}

The idea is to create this below, so that I can populate a Word Document Template Table with repeating rows:

{
  "Gerätetyp": "Notebook",
  "Bezeichnung": "200WS",
  "Gerätenummer": "WS_Test_Notebook",
  "Hersteller": "Lenovo"
 
},{
  "Gerätetyp": "Handy",
  "Bezeichnung": "200WS",
  "Gerätenummer": "A_Test_Handy",
  "Hersteller": "Apple",
}

The Word Document Table shows like this:

And in Word:

So I need repeating rows, which need (I guess) a valid JSON.
What I have so far:

I hope I managed to express myself good enough. 😕

Thank you !

[eliotcole]

This is great, @Bogdan ... I'll be right back. 🙂

One question, when there is multiple entries in each of these key values, do they always contain the same number of comma separations in each one?

{
  "Gerätetyp": "Notebook,Handy",
  "Bezeichnung": "200WS,200WS",
  "Gerätenummer": "WS_Test_Notebook,A_Test_Handy",
  "Hersteller": "Lenovo,Apple",
  "Datum": "10/28/2021"
}

So, if "Gerätetyp" has 3 comma separated values then "Bezeichnung", "Gerätenummer", and "Hersteller" will also have three?

Oh ... and your English is better than mine!!!

[Bogdan]

Yes, those 4 will always have the same comma separated key Values.

Thanks for the English :-))

[eliotcole · Accepted solution]

Nice, OK, here you go!

If some of the values don't have the same amount of comma separated items it will need a workaround. But this should be good for most eventualities.

Anyway...

Solution

Create a Select action which performs in its 'From' field a range() expression based on the number of comma separated values in that first "Gerätetyp" field.

Then since the Select action is essentially just looking at an array of integer numbers, you can use those numbers to refer to each comma separated value in the original data. 🙂

Example Flow

Expressions From

The 'From' field has the following expression to create the range of incrementing integers:

range(0, length(split(outputs('dataIn')?['Gerätetyp'], ',')))

What this is doing is:

1. split() - It splits the data in the "Gerätetyp" value by a comma (',') to create an array.
2. length() - It counts how many items are in the created array.
3. range() - It creates a "range" array that starts at the number 0 and finishes at whatever the length() function has found.

This represents simply in this example as:

[
  0,
  1
]

  Keys and Values

The values for each key that is defined are detailed on separate lines in this code block:

split(outputs('dataIn')?['Gerätetyp'], ',')[item()]

split(outputs('dataIn')?['Bezeichnung'], ',')[item()]

split(outputs('dataIn')?['Gerätenummer'], ',')[item()]

split(outputs('dataIn')?['Hersteller'], ',')[item()]

outputs('dataIn')?['Datum']

Here you are looking for the most part at very similar expression language as the 'From' field. Except here you are picking an item out of the split() array by using an integer that is defined by the range which you created.

This is because when you call item() in a Filter or Select action you are picking the data in that specific array item, which in this case is simply a number and that number indicates which comma separated item you need from each of the original fields.

PRESTO!

[Bogdan]

Wow,
This is the Output I am Looking for !
I will try it tomorrow, as I am not at office right now.

Thank you very much for the Info & Solution! This is by far the trickiest thing I have dealt with until now. 🙂

[eliotcole]

No problems, Bogdan. Do let me know how you get on, and if you have issues, feel free to private message me about them. 🙂

[Bogdan]

Good morning,
Thank you for the solution ! 
I accept your proposal as solution, because it meets my requirement.

BUT there ist a But :))

The template columns get populated only with the first column:

Any idea why ? 

SOLVED - I had an older control in the Template, which I deleted, and NOW:

🎉Thank you @eliotcole  !

BR

[eliotcole]

Amazing, mate! Happy you're all sorted!