Mitchellsql

Skip to content
  • Home
  • About
Search

startswith function

Azure Data Factory–Filter Activity

August 6, 2018 / Mitchell Pearson / 9 Comments

image

The series continues! This is the sixth blog post in this series on Azure Data Factory, if you have missed any or all of the previous blog posts you can catch up using the provided links here:

  • Check out part one here: Azure Data Factory – Get Metadata Activity
  • Check out part two here: Azure Data Factory – Stored Procedure Activity
  • Check out part three here: Azure Data Factory – Lookup Activity
  • Check out part four here: Azure Data Factory – If Condition Activity
  • Check out part five here: Azure Data Factory – Copy Data Activity

What is the Filter activity in Azure Data Factory?

The Filter activity applies a filter expression to an input array. Understanding that definition will help simplify how and where to use this activity.

Let me set up the scenario for you. In this example, I want to use Azure Data Factory to loop over a list of files that are stored in Azure Blob Storage. I am going to use the Metadata activity to return a list of all the files from my Azure Blob Storage container. Unfortunately, I don’t want to process all the files in the directory location. Below I have posted the list of files currently in my storage account, notice the file name “inputEmp_tq.txt”. I want to remove this file from the list of files returned.

image

Get Metadata Activity – Get List of Files to process

I reviewed the metadata activity in the very first blog post in this Azure Data Factory series, therefore, I won’t bore you with those details again.

  1. The dataset is pointing to a folder location, not a specific file. This is important.
  2. I selected “Child Items” from the field list properties, this will return the name of all files in that directory location.

image

Filter Activity – Remove unwanted files from an input array

The first step is to add the filter activity to the pipeline and connect the activity to the successful output of the metadata activity:

image

Now it’s time to set up the Filter activity. The filter activity requires two items during configuration.

  1. Items – Input array on which filter should be applied.
  2. Condition – Condition to be used for filtering the input array.

The items will be the output of our metadata activity and the Condition I will build using the built in expression language. Only items that evaluate to true will be returned in the final array!

Filter Activity Configuration

First, I will configure the Items property. This is simply the output parameter from the metadata activity and so I will use the following code:

@activity(‘meta_GetListOfFiles’).output.childItems

image

Next, I will set up a condition that will remove any files that don’t match the naming pattern I want. In this scenario I am simply looking for file names that start with FactInternetSales_ and any files that don’t match this criteria will be removed from the final array. You can see the actual formula in the previous screenshot but I want to quickly show you how I found the function used for this example.

First, I am going to click my mouse cursor in the Condition box and then I will click on “Add dynamic content”.

image

If you have followed any of the blogs in this series then you are familiar with this new window that opens up. The function that I use in this example is a String Function called startswith. See the following screenshot on where I found this function:

image

Here is the final expression:

image

As always, thanks for reading my blog!

Advertisement

Recent Posts

  • Working with Averages in DAX using AVERAGEX, SUMMARIZE and DATESINPERIOD
  • Managed Virtual Networks and Private Endpoints in Azure Synapse and Azure Data Factory
  • Dynamically changing title names in Power BI
  • Working with Parameters and Expressions in Azure Data Factory
  • Introduction to Wrangling Data Flows in Azure Data Factory

Recent Comments

VINICIUS AUGUSTUS PA… on Azure Data Factory–Filte…
Mitchell Pearson on Advanced TSQL Takeover
lordi on Advanced TSQL Takeover
Mitchell Pearson on The transaction log for databa…
You cant see me on The transaction log for databa…

Archives

  • January 2021
  • December 2020
  • September 2020
  • May 2020
  • April 2020
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • August 2017
  • July 2017
  • June 2017
  • April 2017
  • August 2016
  • May 2016
  • April 2016
  • February 2016
  • July 2015
  • June 2015
  • April 2015
  • February 2015
  • January 2015
  • November 2014
  • September 2014
  • August 2014
  • June 2014
  • May 2014
  • February 2014
  • January 2014

Categories

  • Azure Data Factory
  • Azure Logic Apps
  • BIxPress
  • DAX
  • DBA
  • MDX
  • Performance Tuning
  • Power BI
  • PowerApps
  • Problem, Design, Solution
  • Professional / Organization
  • Scripting
  • Speaking
  • SQL Tips
  • SSAS – Analysis Services
  • SSIS
  • T-SQL
  • Task Factory
  • Tips and Tricks
  • Working with R

Meta

  • Register
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.com

Top Posts & Pages

  • Dynamically changing title names in Power BI
  • ALL vs ALLSELECTED in DAX and Power BI Desktop
  • How to add Data Labels to maps in Power BI
  • SQL Tips! How to edit the query for Edit TOP 200 Rows in Management Studio (SSMS)
  • Managed Virtual Networks and Private Endpoints in Azure Synapse and Azure Data Factory
Create a free website or blog at WordPress.com.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • Mitchellsql
    • Join 92 other followers
    • Already have a WordPress.com account? Log in now.
    • Mitchellsql
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar