Forum Discussion
Filter not working properly
I am very new to Azure Data Factory, and to be honest I have no clue what I am doing.
Owh well here we go, I got a pipeline with extracting a XML to my database and it works fine.
But the thing is I need to filter on multiple CustomerID (Sap_ID). These customers don't use our apps so it has no use for us to use the pipeline. What I am working with:
I am creating a pipeline variable with: @split(pipeline().parameters.Folderpath,'/')[2]
this works fine but when I want to use it for the filter is gives so errors.
I use the pipeline variable of the CustomerID and use not(equals(item().Customer_SapID, 123456)) to filter it.
but it returns an error saying it expects an array.
How can I solve this? And how can I use multiple Customer_SapID in my filter?
Hi AlexTheGreat ,
Thank you for reaching out to Microsoft Fabric Community Forum.
You're using Azure Data Factory (ADF) to pull XML data into your database. The problem is: you want to exclude certain customers based on their CustomerID (Sap_ID) — because they don’t use your apps, so they aren’t needed in the report.
You tried filtering one CustomerID using:
@not(equals(item().Customer_SapID, 123456))
This works for just one ID, but you need to filter multiple IDs, like excluding 123456, 789012, and 345678. Also, ADF gave you an error saying “expects an array” because the filtering logic wasn’t using a proper list.
You can do a few workarounds:
- Create a list of IDs you want to filter
For example:
["123456", "789012", "345678"] - Pass it as a parameter or set it in a variable
This is your exclusion list. - In your Filter activity, use this logic:
@not(contains(variables('BlockedCustomers'), item().Customer_SapID))
If you're using a pipeline parameter instead of a variable, just change it to:
@not(contains(pipeline().parameters.BlockedCustomers, item().Customer_SapID))
This will filter out all items in your data that match the CustomerIDs in the list.
If you want to keep only certain customers, use:
@contains(pipeline().parameters.AllowedCustomers, item().Customer_SapID)
If you're passing your list as a text string like "123456,789012", just convert it to a list using:
@split(pipeline().parameters.CustomerIDs, ',')
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
- Create a list of IDs you want to filter
1 Reply
Hi AlexTheGreat ,
Thank you for reaching out to Microsoft Fabric Community Forum.
You're using Azure Data Factory (ADF) to pull XML data into your database. The problem is: you want to exclude certain customers based on their CustomerID (Sap_ID) — because they don’t use your apps, so they aren’t needed in the report.
You tried filtering one CustomerID using:
@not(equals(item().Customer_SapID, 123456))
This works for just one ID, but you need to filter multiple IDs, like excluding 123456, 789012, and 345678. Also, ADF gave you an error saying “expects an array” because the filtering logic wasn’t using a proper list.
You can do a few workarounds:
- Create a list of IDs you want to filter
For example:
["123456", "789012", "345678"] - Pass it as a parameter or set it in a variable
This is your exclusion list. - In your Filter activity, use this logic:
@not(contains(variables('BlockedCustomers'), item().Customer_SapID))
If you're using a pipeline parameter instead of a variable, just change it to:
@not(contains(pipeline().parameters.BlockedCustomers, item().Customer_SapID))
This will filter out all items in your data that match the CustomerIDs in the list.
If you want to keep only certain customers, use:
@contains(pipeline().parameters.AllowedCustomers, item().Customer_SapID)
If you're passing your list as a text string like "123456,789012", just convert it to a list using:
@split(pipeline().parameters.CustomerIDs, ',')
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
- Create a list of IDs you want to filter