Pivot column in sharepoint List with Power automate

[iglesias6]

Hey Guys!

I have the following sharepoint list:

I would like to create a new sharepoint list that gives me the following:

Cust_HighLevelVertical    Customer Name

Education                         New York City... ; Palm Beach Country... ; University of IOWA ; john hopkins university

Finance                             Banco Santander Rio S.A

Is there a way to create a flow with power automate to do this? How?

If not possible, how is the best way to do this?

Thanks a lot!

[Chriddle · Accepted solution]

Compose

[
	{
		"Name": "name 1",
		"HighLevel": "Education"
	},
	{
		"Name": "name 2",
		"HighLevel": "Finance"
	},
	{
		"Name": "name 3",
		"HighLevel": "Education"
	},
	{
		"Name": "name 4",
		"HighLevel": "Education"
	},
	{
		"Name": "name 5",
		"HighLevel": "Finance"
	}
]

Select

From

xpath(
	xml(json(concat('{"Root":{"Item":', outputs('Compose'),'}}'))),
	'//HighLevel[not(preceding::HighLevel=.)]/text()'
)

Map Education

item()

Map Name

join(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Compose'),'}}'))),
		concat('//Item[HighLevel="', item(), '"]/Name/text()')
	),
	' ; '
)

[iglesias6]

Can you give me some explanations of what you are doing? Also, I have like one hundred different names. Is there a way to do this dynamic?

Also, would you share with me that flow??

[Chriddle]

Can you give me some explanations of what you are doing?

The Select's From creates a list of distinct HighLevels

In Map Name the xpath() creates an array of Names with that HighLevel which is then joined into a string.

Also, I have like one hundred different names. Is there a way to do this dynamic?

This is dynamic and can handle hundreds of different names

Also, would you share with me that flow??

I already deleted it, but these are only 2 actions. Just create them 😉 

[iglesias6]

All right!

But I am not getting how I get a new sharepoint list out of this. I created one, and I want to put this outcome in it:

So, when I test it, I get this:

And I would like to get this:

Cust_HighLevelVertical	Customer Name
Education	New York City... ; Palm Beach Country... ; University of IOWA ; john hopkins university
Finance	Banco Santander Rio S.A

I should be getting only once the "Education" row, only once the "Finance" row and so on. Also, I don´t want to get the "Name:name1" and that stuff. I believe I am not getting something from your response from before, sorry for that.

Can you explain me again how I can get this outcome?

[Chriddle]

The Apply to each has to loop over body('Select')

The create Item's Title has to be items('Apply_to_each')['Education']

Customer value has to be items('Apply_to_each')['Name']

[iglesias6]

Following this steps, I am getting this:

Just in case, I am sharing my flow:

Why it is 

"The create Item's Title has to be items('Apply_to_each')['Education']" 

Shouldn´t it be something that includes all of my Cust_HighLevelVertical column?

[Chriddle]

My flow uses my data (in the Compose). And I use other property names.

Therefore you have to integrate your data into my flow.

One way (not the most elegant, but probably the easiest) is to include an additional Select right after your Get items:

From: the values of your Get items

Map Name: Customer Name (from the Dynamic Content)

Map Education:  Cust_HighLevelVertical (from the Dynamic Content)

Then replace my data in the Compose' with the body of this new Select.

[iglesias6]

Okey, I didnt get that you wanted to use another names and stuff, my bad.

Now I am following better but I dont understand how my compose would be. Can you enter into detail there?

I know this is wrong, but how should it be?

[Chriddle]

Clear the Compose's Input.

Then add the expression body('Select_2')

[iglesias6]

Alright! This worked almost perfectly.

I got this error only:

The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: The API operation 'PostItem' requires the property 'item/Customer' to be a string of maximum length '255' but is of length '332'.

Do you know how this can be fixed?

[Chriddle]

That's the maximum length of a "Single line of text" in Sharepoint.

Change the column to "Multi-line Text" or shorten your input string.

[iglesias6]

Thanks a lot for your help!