Get all column headings for all sheets

[Leebecker]

Hello,

I am trying to find a way to get all the column headings (or all the values in a particular row) for all the sheets in a spreadsheet.  It seems like a fairly simple thing to do, but I can't seem to think of how to do it...

The number of sheets on the spreadsheet can vary, and the reason I want to do this is that I want to check that the headings on all the sheets match up to a list of headings that I have got, so any of the headings could be wrong or not exist.

If I could add an index to all the sheets, then I could filter on a particular row for all the sheets.

Could someone help with this please?

Thanks!!

Lee

[mahoneypat · Accepted solution]

Here is a function that gets the values in Row 1 for all sheets in a workbook.  You can adapt it as needed.  Create a blank query, open the advanced editor and replace the code there with this.  That will create the function you can use in another query that shows 1+ excel file.  On the Add Column tab, click on Invoke Custom Function and point this function at the Content column.

// let
//     Source = File.Contents("C:\Test\GetHeaders.xlsx"),

(filecontent) => let Source = filecontent,
    OpenExcel = Excel.Workbook(Source, null, true),
    #"Filtered Rows" = Table.SelectRows(OpenExcel, each ([Kind] = "Sheet")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Headers", each Record.ToList([Data]{0})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Headers"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Headers", type text}})
in
    #"Changed Type"

The top lines with // are commented out.  You can comment out the //(filecontent) line and uncomment those to adapt it.

Pat

[Leebecker]

Brilliant this works perfectly, thank you!!

In the mean time, I also came across this video that works for the adding index part (just in case anyone else is interested):

https://www.youtube.com/watch?v=7CqXdSEN2k4

Your solution is much slicker though 🙂