cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leebecker
New Member

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

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

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.

 

mahoneypat_1-1634816529857.png

 

mahoneypat_0-1634816511542.png

 

// 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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User
Super User

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.

 

mahoneypat_1-1634816529857.png

 

mahoneypat_0-1634816511542.png

 

// 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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors