Azure, how to process only the first row of Excel file?

Chuck Roberts 130

I use MS Azure Data Factory online in the portal. I have some type of premium license.
All blob storage is the newer Gen2.
One of the fields in row 1 is named "TrackNum" via a Derived Column step, and in row 1 has the contents "Shipment Tracking Num". But I can't depend on that value being the same case for each spreadsheet we get so I need to select just row number 1 (the first row).

I have an Excel spreadsheet that I would like to check the headers in but only process row 1. So I added a Filter action and in the box "Filter on" I entered rowNumber() == 1 and get an error. The error is "Unknown function name".

So how do I use only row 1?

My big picture is my program will have many data flows, this data flow only checks the column header names in row 1. If any data flow gets an error the whole thing stops. All data flows will be controlled by a pipeline.

p.s. So glad these posts use Markdown!

EDIT.

rowNumber() is not available in a FILTER transformation or DERIVED COLUMN transformation.
rowNumber() is only available in a WINDOW transformation.

phemanth 11,470 Reputation points Microsoft Vendor

2024-11-06T18:43:11.4766667+00:00
@Chuck Roberts

Thanks for reaching out to Microsoft Q&A.

Steps to Process Only the First Row of an Excel File in Azure Data Factory

Create a Data Flow:

In your Azure Data Factory, create a new Data Flow.

Add a Source:

Add a source transformation and connect it to your Excel file in Azure Blob Storage.

Ensure that the "First row as header" option is enabled in the source settings so that the column names are read correctly.

Add a Derived Column:

After the source, add a Derived Column transformation.

In this transformation, you can add a new column that assigns a constant value (e.g., 1) to every row. For example, create a column called RowIndex and set its value to 1.

plaintext 1RowIndex = 1

Add a Filter Transformation:

Add a Filter transformation after the Derived Column.

In the filter condition, check for the constant value you assigned in the previous step. Since all rows will have RowIndex set to 1, you can filter for it.

plaintext 1RowIndex == 1

Select Transformation (Optional):

If you only want to select specific columns from the first row, you can add a Select transformation after the Filter. Here, you can choose which columns you want to keep.

Sink Transformation:

Finally, add a Sink transformation to specify where you want to output the processed data (e.g., another Excel file, a database, etc.).

This approach effectively allows you to isolate and process only the first row of your Excel file by leveraging a derived column to create a constant value for filtering. Since the rowNumber() function is not available in the context you were trying to use it, this method provides a straightforward workaround.

Hope this helps. Do let us know if you any further queries.
Chuck Roberts 130 Reputation points

2024-11-07T10:39:54.1533333+00:00

Since all rows will have RowIndex set to 1, you can filter for it.

But I don't want all rows to have a RowIndex of 1. I just want the first row to have a RowIndex of one. I only need to look at the first row which has headers. And if I check "Enable first row as header" Azure Data Factory cannot process that row. I actually have to turn that off.
phemanth 11,470 Reputation points Microsoft Vendor

2024-11-13T01:57:07.93+00:00
@Chuck Roberts

To process only the first row of your Excel file in Azure Data Factory while keeping the “Enable first row as header” option turned off, you can follow these steps:

slight changes to the previous steps

Source Transformation: Add your Excel file as a source and ensure that the “First row as header” option is disabled. This allows all rows to be treated as data.

Add a Derived Column: Right after the source, add a Derived Column transformation. Here, you can create a new column (e.g., RowIndex) and set it to 1 only for the first row. Use the following expression:

RowIndex = if(rowNumber() == 1, 1, null)

Filter Transformation: Add a Filter transformation next. In the filter condition, use:

RowIndex == 1

This will ensure that only the first row is processed.

Select Transformation (Optional): If you want to keep only specific columns from the first row, you can add a Select transformation after the Filter to choose which columns to retain.

Sink Transformation: Finally, add a Sink transformation to specify where you want to output the processed data.
phemanth 11,470 Reputation points Microsoft Vendor

2024-11-13T18:54:59.6566667+00:00

@Chuck Roberts We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Chuck Roberts 130 Reputation points

2024-11-15T10:17:28.57+00:00

Thank you. I have time to check this today. Please be patient, in some cases it may take me 5 business days to get back to this project and try your answers.
Chuck Roberts 130 Reputation points

2024-11-15T10:31:08.6633333+00:00

In my Derived Column transformation, when I use the formula iif(rowNumber() == 1, 1, 0) I get a "Type mismatch" error. If I use if(rowNumber() == 1, 1, 0) I also get a type mismatch error. When I use RowIndex = iif(rowNumber() == 1, 1, 0) or RowIndex = iif(rowNumber() == 1, 1, null) I also get a "Type mismatch" error.

When I go into the expression builder the function rowNumber() is grayed out for my data flow.

Is rowNumber() simply not available for a data flow?
phemanth 11,470 Reputation points Microsoft Vendor

2024-11-20T05:45:09.3533333+00:00
@Chuck Roberts

It looks like you’re encountering some challenges with the rowNumber() function in your Derived Column transformation in Azure Data Factory. Here’s what you need to know:

Understanding rowNumber()

The rowNumber() function is indeed not available in the context of a Derived Column transformation. It is only accessible within a Window transformation. This is why it appears grayed out in the expression builder.

Alternative Approach

Since you want to process only the first row, you can use a different method:

Use a Window Transformation:

Add a Window transformation after your source.

In the Window transformation, you can use rowNumber() to create a new column that assigns a row number to each row.

Add a Derived Column:

After the Window transformation, add a Derived Column transformation.

Here, you can use the iif() function to create a new column (e.g., RowIndex) based on the row number:
RowIndex = iif(rowNumber() == 1, 1, null)

Filter Transformation:

After the Derived Column, add a Filter transformation to only keep rows where RowIndex equals 1.

This approach allows you to effectively isolate and process only the first row of your Excel file without running into type mismatch errors.
Chuck Roberts 130 Reputation points

2024-11-20T16:16:18.73+00:00
This gives me an error, please see below.

In my data flow I have a source transformation. My dataset name ds_01 I do not check "First row as header".

Then I have a SELECT to grab only needed columns and name them. One of the columns I name "Tracknum".

Next I have a WINDOW transformation. In Tab "4. Window columns" I make a column called "rownum" which contains the expression rowNumber(). On the "2. Sort" tab I sort by the Tracknum column. When I do data preview in this WINDOW transformation the first row is not the header row. When I do a Data Preview, if I don't choose a field in "2. Sort tab" I get an error that "Window functions require order by clause".
phemanth 11,470 Reputation points Microsoft Vendor

2024-11-25T12:36:26.62+00:00
@Chuck Roberts

Let’s break down the steps and address the errors you’re facing.

Source Transformation:

Ensure your dataset (ds_01) is set up correctly, and do not check “First row as header.” This allows all rows to be treated as data.

Select Transformation:

Use the Select transformation to grab only the needed columns and rename them. For example, you can rename one of the columns to “Tracknum.”

Window Transformation:

In the Window transformation:

4. Window Columns: Create a new column called rownum with the expression rowNumber().

2. Sort Tab: You must sort by a column to use window functions. Since you want to sort by “Tracknum,” ensure that this column is included in your sort criteria.

Addressing the Errors

First Row Not Being Header: Since you have not checked “First row as header,” the first row will be treated as data. This is expected behavior. If you want to process the first row as headers, you will need to handle that separately after filtering.

Error: “Window functions require order by clause”: This error occurs because window functions need a defined order. Make sure you select a column (like “Tracknum”) in the Sort Tab of the Window transformation. If you don’t select any column, you’ll get this error.

Share via

Azure, how to process only the first row of Excel file?

Your answer