Excel file with multiple row of column headers, want to make it one column header using azure data factory

Question

Excel file with multiple row of column headers, want to make it one column header using azure data factory

Tulsi K 20

Hi, I have an Excel file used as a data source in Data Factory, where I encountered a challenge with multiple rows for column headers (Nested headers). Specifically, I aim to consolidate two rows into a single row to streamline the header columns. To provide a clearer understanding, consider the following example: User's image

By merging these rows, I aim to make like this.

project1 test1

project1 test2

project1 test3

project2 test4

project2 test5

project2 test6

Guidance on how to efficiently accomplish this task within Data Factory would be helpful.

Jiajing Hua 18,175 Reputation points Moderator

2024-02-21T07:48:12.7866667+00:00
Hi @Khimlal, Tulsi

I suggest you use Power Query to transpose the columns.

Select the range of the cells, import the range into Power query. Please note, don't tick the box of "My table has headers".

Select all columns, click Transpose.

Right click on "Column1", click Fill > Down.

Select "Column1" and "Column2", click Merge Columns. Choose the separator that you need, and name the new merged column.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Tulsi K 20 Reputation points

2024-02-21T08:00:14.0433333+00:00

Hi @Jiajing Hua Thanks for your response. Can I achieve it using adf without making any changes in excel? I have around 200 columns
Jiajing Hua 18,175 Reputation points Moderator

2024-02-21T08:11:24.9833333+00:00

Hi @Khimlal, Tulsi

Thanks for your back.

But I mainly deal with posts with "Excel" tag, so I give suggestions from an Excel perspective. I suggest you wait for a professional reply from an ADF expert. Thank you for your understanding.
Smaran Thoomu 33,270 Reputation points Microsoft External Staff Moderator

2024-02-22T06:16:41.4966667+00:00
Hi @Khimlal, Tulsi

Thank you for posting query in Microsoft Q&A Platform.

To consolidate the two rows of column headers into a single row in your Excel file using Azure Data Factory, you can follow these steps:

Create an Excel file dataset in Azure Data Factory.

Use the "Excel transformation" activity to read the Excel file and extract the data.

Use the "Derived Column" activity to create a new column that concatenates the two rows of column headers.

Use the "Select" activity to remove the original two rows of column headers.

Use the "Copy" activity to write the transformed data to a new Excel file.

Here's an example of how you can use the "Derived Column" activity to concatenate the two rows of column headers:

concat(column1, ' ', column2)

Make sure to replace "column1" and "column2" with the actual column names in your Excel file. For more information on using Azure Data Factory to transform data, you can refer to the following Azure documentation:

Transform data in Azure Data Factory using mapping data flows

Derived column transformation in mapping data flow

I hope this helps! Let me know if you have any further questions.
Tulsi K 20 Reputation points

2024-02-22T07:00:25.55+00:00

Hi @Smaran Thoomu I have noticed that when I don't select the first row as the header, the data preview displays as follows:

However, when attempting to derive a new column, I am unable to select "project1+test1" as an option. The only available columns for selection are "column1" and "column2".

On the other hand, when I select the first row as the header, the data preview changes to something like this:

The issue persists as I still cannot find an option to select "test1" from row 2 for concatenation. Kindly provide your input.
peiye zhu 320 Reputation points

2025-08-04T07:46:33.52+00:00

Do you mean to

form:

p1 p2 p3

t1 t2 t1 t2 t1 t2

to

p1_t1 p1_t2 p2_t1 p2_t2 p3_t1 p3_t2

2 answers

Your answer

Jiajing Hua 18,175 Reputation points Moderator

2024-02-21T07:48:12.7866667+00:00

Hi @Khimlal, Tulsi

I suggest you use Power Query to transpose the columns.

Select the range of the cells, import the range into Power query. Please note, don't tick the box of "My table has headers".

Select all columns, click Transpose.

Right click on "Column1", click Fill > Down.

Select "Column1" and "Column2", click Merge Columns. Choose the separator that you need, and name the new merged column.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Tulsi K 20 Reputation points

2024-02-21T08:00:14.0433333+00:00

Hi @Jiajing Hua Thanks for your response. Can I achieve it using adf without making any changes in excel? I have around 200 columns
Jiajing Hua 18,175 Reputation points Moderator

2024-02-21T08:11:24.9833333+00:00

Hi @Khimlal, Tulsi

Thanks for your back.

But I mainly deal with posts with "Excel" tag, so I give suggestions from an Excel perspective. I suggest you wait for a professional reply from an ADF expert. Thank you for your understanding.
Smaran Thoomu 33,270 Reputation points Microsoft External Staff Moderator

2024-02-22T06:16:41.4966667+00:00

Hi @Khimlal, Tulsi

Thank you for posting query in Microsoft Q&A Platform.

To consolidate the two rows of column headers into a single row in your Excel file using Azure Data Factory, you can follow these steps:

Create an Excel file dataset in Azure Data Factory.

Use the "Excel transformation" activity to read the Excel file and extract the data.

Use the "Derived Column" activity to create a new column that concatenates the two rows of column headers.

Use the "Select" activity to remove the original two rows of column headers.

Use the "Copy" activity to write the transformed data to a new Excel file.

Here's an example of how you can use the "Derived Column" activity to concatenate the two rows of column headers:

concat(column1, ' ', column2)

Make sure to replace "column1" and "column2" with the actual column names in your Excel file. For more information on using Azure Data Factory to transform data, you can refer to the following Azure documentation:

Transform data in Azure Data Factory using mapping data flows

Derived column transformation in mapping data flow

I hope this helps! Let me know if you have any further questions.
Tulsi K 20 Reputation points

2024-02-22T07:00:25.55+00:00

Hi @Smaran Thoomu I have noticed that when I don't select the first row as the header, the data preview displays as follows:

However, when attempting to derive a new column, I am unable to select "project1+test1" as an option. The only available columns for selection are "column1" and "column2".

On the other hand, when I select the first row as the header, the data preview changes to something like this:

The issue persists as I still cannot find an option to select "test1" from row 2 for concatenation. Kindly provide your input.
peiye zhu 320 Reputation points

2025-08-04T07:46:33.52+00:00

Do you mean to

form:

p1 p2 p3

t1 t2 t1 t2 t1 t2

to

p1_t1 p1_t2 p2_t1 p2_t2 p3_t1 p3_t2

Answer 1

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitTable = Table.SplitAt(Source,2),
    Headings = List.Transform(Table.ToColumns(Table.FromRows(List.Transform(Table.ToRows(SplitTable{0}), each Table.FillDown(Table.FromColumns({_},{"Value"}),{"Value"})[Value]))), each Text.Combine(_,"/")),
    Custom1 = Table.FromColumns(Table.ToColumns(SplitTable{1}),Headings),
    #"Added Index" = Table.AddIndexColumn(Custom1, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Project", "Test"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Index"})
in
    #"Removed Columns"

Hope this helps.

User's image

Answer 2

AnnuKumari-MSFT 34,566 Microsoft Employee Moderator

Hi Khimlal, Tulsi ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to merge rows having header in excel into single header . However , it is not just one step process , we need to perform data transformation on the source dataset. Additionally, in your scenario , test1,test2, and test3 needs to be converted to project1 test1, project1 test2, project1 test3 and the next set of columns : test4,test5 and test6 to be converted to project2 test4, project2 test5 and project2test .

Add Source transformation and point the dataset to the source excel file. Remove 'First row as header'
Add Filter transformation and provide this expression: !startsWith({_col0_},'Project')
Add derived column transformation and add column pattern to replace test1 with project1 test1 and so on with other
Add sink transformation and in dataset select 'first row as header'

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Tulsi K 20 Reputation points

2024-03-14T07:56:33.5266667+00:00

Hi @AnnuKumari-MSFT Thanks for your inputs. But In my case in the second header there are duplicate columns for example I can say like test1 and test2 are repeated. If I try to preview by skipping firstrow It throws error saying that the columns are duplicates. Any thought on this?
AnnuKumari-MSFT 34,566 Reputation points Microsoft Employee Moderator

2024-04-02T09:20:26.0633333+00:00

Khimlal, Tulsi ,

Duplicates columns are not considered in dataflow. you probably need to handle it by writing custom code in python or c# and use az function activity or custom activity to execute the same using ADF.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Share via

Excel file with multiple row of column headers, want to make it one column header using azure data factory

2 answers

Your answer