Forum Discussion
Get all column headings for all sheets
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
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
2 Replies
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
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=7CqXdSEN2k4Your solution is much slicker though 🙂
