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


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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors