cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonykiefer
Super User
Super User

Create collection while removing undesired content

I'm trying to create a collection that has multiple columns.  The collection should be filtered by one specific column - "Description".  I would like to build the collection based on the "Description" column while removing all records where the "Description" does not start with "F1", "F3", or "X1".  

 

So only records where the "Desription" starts with "F1", "F3", and "X1" populate the collection.

 

Here is my code...

 

ClearCollect(CopyFromBomModelCollection, AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
First(Filter(CopyFromBomModelCollection, Description<>"F1"),
First(Filter(CopyFromBomModelCollection, Description<>"F3"),
First(Filter(CopyFromBomModelCollection, Description<>"X3")))

 

This isn't working and I'm having trouble trying to figure this out...any help would be great!

1 ACCEPTED SOLUTION

Accepted Solutions

@Abhilash_Swain ...I tried the quotes and the result is the same.  As an alternative I am going to create a SQL view with the filtered data instead.  I do appreciate you help!

 

tonykiefer_0-1654274021026.png

 

 

 

View solution in original post

12 REPLIES 12
Abhilash_Swain
Super User
Super User

Hi @tonykiefer ,
Please try this.

 

 

ClearCollect(CopyFromBomModelCollection, AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId), Filter(CopyFromBomModelCollection, Description<>"F1" , Description<>"F3",Description<>"X3"))

 

 

OR 

 

ClearCollect(CopyFromBomModelCollection, AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId), Filter(Filter(Filter(CopyFromBomModelCollection, Description<>"F1"),Description<>"F3"),Description<>"X3")) 

 

Please check the syntax if I miss ")".

 

 

 

@Abhilash_Swain Thank you for the quick reply.  I made one mistake...the column I need to filter by is the ItemId...but that was easily corrected.  Unfortunately I am still getting the same result.  The undesired records are not filtering out.

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, ItemId<>"F1" , ItemId<>"F3",ItemId<>"X3"))

 

OR

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(Filter(Filter(CopyFromBomModelCollection, ItemId<>"F1"),ItemId<>"F3"),ItemId<>"X3"))

 

Neither solutions work.

 

Also...in my original description I need to filter out the records that do NOT start with "F1", "F3", or "X1"

 

tonykiefer_0-1654180459723.png

 

I also tried this with no success...

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, ItemId, StartsWith("F1","F1"), ItemId, StartsWith("F3","F3"), ItemId, StartsWith("X1","X1")))

 

The goal is to eliminate all records where the ItemId does not begin with "F1", "F3", or "X1"

 

tonykiefer
Super User
Super User

I also tried this with no success...

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1"), StartsWith(ItemId,"F3"), StartsWith(ItemId,"X3")))

 

tonykiefer_0-1654183264420.png

 

 

Abhilash_Swain
Super User
Super User

I am sorry, We are looking for a condition with OR and we are trying a condition which is for AND . 😄 

Try this,

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1") || StartsWith(ItemId,"F3") || StartsWith(ItemId,"X3")))

 

Use !StartsWith(ItemID,"F1") if you want not starts with. 

Hello @Abhilash_Swain,

We seem to be heading in the right direction but I still get the same result set.

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1") || StartsWith(ItemId,"F3") || StartsWith(ItemId,"X3")))

 

tonykiefer_0-1654200562958.png

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1") || StartsWith(ItemId,"F3") || StartsWith(ItemId,"X3")),FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId))

 

We were doing it wrong for AddColumns section.

tonykiefer
Super User
Super User

Hello @Abhilash_Swain ,

Using the latest suggestion only provides errors.

 

Do you have a test table you can use for testing?

 

tonykiefer_0-1654203628148.png

 

Seems the column names are not recognized. 
Please check the below screen shot.

Abhilash_Swain_0-1654266724066.png

 

Please give a like and accept as solution if I answered your query.

tonykiefer
Super User
Super User

@Abhilash_Swain I'm pretty certain the col name is recognized because it works when I add the new column.

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1") || StartsWith(ItemId,"F3") || StartsWith(ItemId,"X3")),FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId))

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,140)