Split data format in Powerautoamte

[Ashok2]

Hi All 

I am trying to build flow where i have to get item from Sharepoint using certain Filter and append it in array and set in string variable then update in one of the column of Sharepoint using update properties its working fine but now i have requirement to update in particular format 

ex below is my Sharepoint list all are single line text column

Column A	Column B	Column C	Column D
1001	TestABC	ABC	GroupA , GroupB,GroupC
1001	TestABCD	ABCD	GroupA , GroupB
1001	TestABCDE	ABCDE	GroupB,GroupC
1001	TestABCDEF	ABCDEF	GroupA,GroupC

using below flow i am able to get data in below format 
[1001,TestABC,ABC,GroupA , GroupB,GroupC,1001,  TestABCD,ABCD,GroupA , GroupB,1001,TestABCDE,ABCDEGroupB,GroupC,
1001,TestABCDEF,ABCDEF,GroupA,GroupC]

But now i have a requirement to show data in below format grouping data as per ColumnD so what ever belong to GroupA GroupB, GroupC

GroupA 1001 ,TestABC ,ABC,1001,  TestABCD,ABCD,1001,TestABCDEF,ABCDEF,
GroupB 1001 ,TestABC ,ABC,1001,  TestABCD,ABCD,1001,TestABCDE,ABCDE
GroupC   1001 ,TestABC ,ABC,1001,TestABCDE,ABCDE,1001,TestABCDEF,ABCDEF

Hope its clear 

[NiloferA · Accepted solution]

Hello @Ashok2 ,

I will have to say that this was not pretty straight forward, but I managed to accomplish your requirement.

1. I have used Select action to get the Column D as an array of arrays
2. Then I have used nested Apply to Each to collect the Values of Column D (GroupA, GroupB & GroupC ..)
   1. 
3. Then I have used Apply to Each where I am passing the expression union(variables('MyArray'),variables('MyArray')), to get distinct values of Column D
4. Within this Apply to Each, I have added Filter Array action and configured it as follows
   1. 
   2. join(item()?['ColumnD'], ',')
5. Then I have added a Select action to select the values I want from the Filtered Array, I have only used ID and Title
   1. 
6. Then I am appending the values to a String 
   1. 
   2. replace(replace(replace(replace(join(body('Select_2'),','),'{',''),'}',''), '"',''),':',',')
   3. 
7. This is what the final output looks like
   1. 

Hope you find this helpful enough, let me know if you struggle anywhere.

Please give a Kudo if you found this helpful or mark this Reply as a Solution if it solved your problem. 

[Ashok2]

@NiloferA 

Thanks you for your response could you please help me understand step no 2 

In Step no 2 i am unable to get "Groups" in Apply to each 4 and "value" in Append to Array variable 

[NiloferA]

Hello @Ashok2 ,

Sure, In step 1 I am using a Select action where I have passed the output from Get Items and I have mentioned Groups as the key and Column D (Dynamic Content from Get Items) as value.

Then in Step 2, I am passing the output of Select as input for Apply to Each, then I am nesting an Apply to Each in it and passing this expression - items('Apply_to_each_3')['Groups']  and for Value its items('Apply_to_each_4')['Value']

Hope this is clear enough.

[Ashok2]

Hello @NiloferA 

I am getting below error in apply to each _3 
The execution of template action 'Apply_to_each_3' failed: the result of the evaluation of 'foreach' expression '@items('Apply_to_each_3')?['Groups']' is of type 'String'. The result must be a valid array

My Groups column is single line text column with comma separated values 

[NiloferA]

Hello @Ashok2 ,

I assumed you were using Multi Select Choice Column, but anyways we can work with string values too.

1. In Step 2, In Apply_to_each_4 add this expression - split(items('Apply_to_each_3')['Column D'],',') and In Append to Array Variable add this expression - trim(items('Apply_to_each_4'))
2. In Step 4, In Filter Array action, instead of join expression just pass Column D

The rest of the Flow remains the same.

[Ashok2]

Thanks it worked awesome solution!

[Ashok2]

@NiloferA 

Can you help me to modify this solution as COLUMND will be now as Dropdown type (Single Select)

[NiloferA]

Hello @Ashok2 ,

Please follow below steps for this change

1. In the Select action, add "Column D Value", for Choice columns you get two options in Dynamic Content, select the one with "Value" suffixed to the Column Name, the other one will give you an object of the Choice which will contain the ID and the Value. 
   1. 
2. You will only need one Apply to Each now, and configure it as follows
   1. Pass the Output from Select here
   2. Add Filter Array action, pass value from Get Items and configure it as follows
   3. 

      items('Apply_to_each')['Groups']

   4. Add Select action, pass the output from Filter Array here and configure it as follows
   5. 

      item()['ID'], item()['Title']

   6. Add Append to String Variable action and configure it as follows
   7. 

      items('Apply_to_each')['Groups'] ,   keep the replace as it is from my previous solution

   8. 
3. Then I am using a Set Variable action to display the output
   1. 

Let me know if you get stuck somewhere.

[Ashok2]

@NiloferA 

I tried below steps but getting repeated results so suppose i have enteries like below 

ColumnA	ColumnD
TEST	GroupA
TEST1	GroupB
TEST2	GroupA

Now output what i got is again is below  again got GroupA at end 

GroupA TEST , TEST2

GroupB TEST1

GroupA TEST,TEST2 

[NiloferA]

Hello @Ashok2 ,

To resolve this problem, In Apply to Each instead of passing the Output of Select directly, pass union(body('Select'),body('Select')) , this will get distinct values from Select and loop on them.

[Ashok2]

Hello @NiloferA 

in Select 2 if ID or Title go empty it throw below errors 

cannot be evaluated because property 'Title' doesn't exist, available properties are '@odata.etag, ItemInternalId, ID,

[NiloferA]

Hello @Ashok2 ,

You can try to handle it in the expression itself. So currently, in Select it's item()['Title'], you can replace this with if(empty(item()['Title']),"null",item()['Title']).  I am not sure how come ID can be empty, but yeah whichever column you are using to get values you can use this expression and handle empty values. You can replace null with anything else if you like or simply "" if you just want to leave it blank

[Ashok2]

Hello @NiloferA 

i tried same but getting same error below is screenshot and below is one of my expression

 if(Empty(item()['IdentityCard']),'',item()['IdentityCard'])

[Ashok2]

HI @NiloferA 

Changing your code to the below worked!

if(equals(item()?['Column'],null), '-', item()?['Column'])

[NiloferA]

Great @Ashok2! Glad your issue is resolved.