cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cfoenki
Frequent Visitor

Compiling a collection from a Sharepoint list

Hi all,

 

I have an issue with one of my collection from Sharepoint.

 

Here is the architecture of my sharepoint List:

 

Document IDRole 1Role 2Role 3Role 4Role 5
ID001YesYesNo

No

Yes
ID002NoNoYesYesYes

 

I would like to make a collection from this with this architecture (List document IDs with Roles where Yes is selected):

 

Document IDRole
ID001Role 1
ID001Role 2
ID001Role 5
ID002Role 3
ID002Role 4
ID002Role 5

 

Do you think it is possible? How could I achieve this?

 

Thank you in advance for all the help you could give!

1 ACCEPTED SOLUTION

Accepted Solutions
v-yangar-msft
Microsoft
Microsoft

Hi @cfoenki 

 

Yes, its possible to achieve this based on your description.

You can refer to the following steps as below:

1.Here is the SharePoint list in my side:

v-yangar-msft_0-1612422571112.png

 

2.Add a button and set the OnSelect property of button to:

ClearCollect(SampleCollect,SampleList)

 // SampleList is my sharepoint list, this formula aims to add a collection which use the sharepoint list value.

3.Set the OnSelect property of button to:

ClearCollect(SampleGroup,GroupBy(SampleCollect,"Title","Role1","Role2","Role3","Role4","Role5","Others"))

 //this formula aims to coalesce column which is hidden column in sharepoint list:

v-yangar-msft_1-1612422571115.jpeg

4.Set the OnSelect property of button to:

ClearCollect(SampleCollection,DropColumns(SampleGroup,"Others"))

// this formula aims to drop the other column

5.Set the OnSelect property of button to:

ClearCollect(SampleCollections,RenameColumns(SampleCollection,"Title","Document ID"))

// this formula aims to Rename the column name

v-yangar-msft_2-1612422571117.png

6.Set the OnSelect property of button to:

ClearCollect(SampleCollectionA,AddColumns(SampleCollections,"Role",""))

 // this formula aims to add a Role column

7.Set the OnSelect property of button to:

ForAll(SampleCollections,

If(Role1=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role1",'Document ID':ThisRecord.'Document ID'}));

If(Role2=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role2",'Document ID':ThisRecord.'Document ID'}));

If(Role3=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role3",'Document ID':ThisRecord.'Document ID'}));

If(Role4=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role4",'Document ID':ThisRecord.'Document ID'}));

If(Role5=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role5",'Document ID':ThisRecord.'Document ID'}));)

//this formula aims to add multi records which meet the condition Role=true and patch the right value.

8.Set the OnSelect property of button to:

ClearCollect(SampleCollectionB,DropColumns(SampleCollectionA,"Role1","Role2","Role3","Role4","Role5"))

//this formula aims to drop the column role1~role5

9.Set the OnSelect property of button to:

ClearCollect(SampleCollectionC,RemoveIf(SampleCollectionB,Role=""))

// this formula aims to remove two records which the value is blank.

v-yangar-msft_3-1612422571119.png

 

 

Hope it helps!

 

Thanks,

Arrow

View solution in original post

1 REPLY 1
v-yangar-msft
Microsoft
Microsoft

Hi @cfoenki 

 

Yes, its possible to achieve this based on your description.

You can refer to the following steps as below:

1.Here is the SharePoint list in my side:

v-yangar-msft_0-1612422571112.png

 

2.Add a button and set the OnSelect property of button to:

ClearCollect(SampleCollect,SampleList)

 // SampleList is my sharepoint list, this formula aims to add a collection which use the sharepoint list value.

3.Set the OnSelect property of button to:

ClearCollect(SampleGroup,GroupBy(SampleCollect,"Title","Role1","Role2","Role3","Role4","Role5","Others"))

 //this formula aims to coalesce column which is hidden column in sharepoint list:

v-yangar-msft_1-1612422571115.jpeg

4.Set the OnSelect property of button to:

ClearCollect(SampleCollection,DropColumns(SampleGroup,"Others"))

// this formula aims to drop the other column

5.Set the OnSelect property of button to:

ClearCollect(SampleCollections,RenameColumns(SampleCollection,"Title","Document ID"))

// this formula aims to Rename the column name

v-yangar-msft_2-1612422571117.png

6.Set the OnSelect property of button to:

ClearCollect(SampleCollectionA,AddColumns(SampleCollections,"Role",""))

 // this formula aims to add a Role column

7.Set the OnSelect property of button to:

ForAll(SampleCollections,

If(Role1=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role1",'Document ID':ThisRecord.'Document ID'}));

If(Role2=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role2",'Document ID':ThisRecord.'Document ID'}));

If(Role3=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role3",'Document ID':ThisRecord.'Document ID'}));

If(Role4=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role4",'Document ID':ThisRecord.'Document ID'}));

If(Role5=true,Patch(SampleCollectionA,Defaults(SampleCollectionA),{Role:"Role5",'Document ID':ThisRecord.'Document ID'}));)

//this formula aims to add multi records which meet the condition Role=true and patch the right value.

8.Set the OnSelect property of button to:

ClearCollect(SampleCollectionB,DropColumns(SampleCollectionA,"Role1","Role2","Role3","Role4","Role5"))

//this formula aims to drop the column role1~role5

9.Set the OnSelect property of button to:

ClearCollect(SampleCollectionC,RemoveIf(SampleCollectionB,Role=""))

// this formula aims to remove two records which the value is blank.

v-yangar-msft_3-1612422571119.png

 

 

Hope it helps!

 

Thanks,

Arrow

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,633)