How to promote the first line of each table before combining files?

[KUERY]

Hello! I am new to power query and apologize if this is a basic question. 

I have multiple files in a folder, with multiple sheets in each file. These files do not have consistent naming conventions or headers. 

For my purposes, I think that the information found in How to use Power Query to Combine Multiple Files that have different headings - YouTube should be fine to combine these files and capture the different header names. But I have hit kind of a road block in that after extracting each table from each sheet I am not sure how to promote the headers of all the tables simulaneously.

Each of these tables has Columns 1, 2, 3, and so on. I'd like to promote each table's first row to the header for that table and then combine them. Is there a simpler way of reaching the same goal?

Thank you!

[Ni_BI_Żak]

Hi.

I know this topic is old but i can't see proper answer.

Best solution to have it, is to transform not expanded tables directly without creating new column

Table.TransformColumns(#"Previous Step", {"Data", each Table.PromoteHeaders(_, [PromoteAllScalars = true])})

where "Data" is the name of column where in rows are nested tables.

[dufoq3]

Why do you consider this as best solution? If you load data from excel, better solution is to change 2nd parameter of Excel.Workbook function from null to true.

[Ni_BI_Żak]

Hi. Thx for reply.
1st - Its universal not only for Excel nested data (all nested tables, f.ex csv's from sharepont folder)
2nd - approach with transform column allows You to change without creating another column (less time calc space - better data control)

[KT_Bsmart2gethe · Accepted solution]

@Hi @KUERY ,

If you want to promote first row of each table as a header, there are various way to do it.

You can add a custom column after the #"Removed Columns" with the formula below:

Name the custom column "Promote Headers" or anything you like or leave it as Custom.

Formula: Table.PromoteHeaders([File Data.Data])

Then, you can expand the columns.

Regards

KT

[KUERY]

This worked. Now I just need to combine them with their different headers. Thanks!

[Vijay_A_Verma]

I think that video is a good way to achieve the goal. What is the exact issue which you are facing?

[KUERY]

Well, I guess my problem is that I don't have headers in the tables. I am using the folder as the source with the following code to extract each of the tables:

let
Source = Folder.Files("X:\XXX\XXXX\XXXX\Folder Name"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"Content", "Source.Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "File Data"}}),
#"Expanded File Data" = Table.ExpandTableColumn(#"Renamed Columns", "File Data", {"Name", "Data", "Item", "Kind", "Hidden"}, {"File Data.Name", "File Data.Data", "File Data.Item", "File Data.Kind", "File Data.Hidden"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded File Data",{"Content", "Source.Name", "File Data.Name", "File Data.Item", "File Data.Kind", "File Data.Hidden"})

in

#"Removed Columns"

Now I have each of the data sets extracted, the headers for each table are just "col 1,2,3 etc." I'd like to promote the first row in each table to the header and then combine them. The video talk about how to combine data with different headers, but I need to also combine different sheets from different workbooks in a folder as well. 

[BA_Pete]

Hi @KUERY ,

Open Advanced Editor in Power Query for any query that references an Excel file as a source.

Change this 'null' argument to 'true':

Pete

[KUERY]

I have used a folder as a source and then extracted the sheets from each of the workbooks. This is the code that I have right now:

let
Source = Folder.Files("X:\XXX\XXXX\XXXX\Folder Name"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"Content", "Source.Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "File Data"}}),
#"Expanded File Data" = Table.ExpandTableColumn(#"Renamed Columns", "File Data", {"Name", "Data", "Item", "Kind", "Hidden"}, {"File Data.Name", "File Data.Data", "File Data.Item", "File Data.Kind", "File Data.Hidden"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded File Data",{"Content", "Source.Name", "File Data.Name", "File Data.Item", "File Data.Kind", "File Data.Hidden"})
in
#"Removed Columns"

this is where I end up with the Screen Shot. I'd Like to promote each table's first row to the headers and then combine them. The headers are not all consistent. Otherwise I would just expand, promote the first row, and then filter out all the other headers.