Create collection while removing undesired content

[tonykiefer]

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!

[Abhilash_Swain]

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 ")".

[tonykiefer]

@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"

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]

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")))

[Abhilash_Swain]

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. 

[tonykiefer]

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")))

[Abhilash_Swain]

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]

Hello @Abhilash_Swain ,

Using the latest suggestion only provides errors.

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

[Abhilash_Swain]

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

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

[tonykiefer]

@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))