none
Promote the 4th or 5th row within a table to headers RRS feed

  • Question

  • I am using power query to pull tables from multiple(300+) exact format excel files within a folder. The script that I use works, but is clunky. I am having to use the command Table.PromoteHeaders 5 times to get the column headers that I need. My question is," Is there an easier command that can promote the 5th row of the table to the header?" Script is below.

    let
        Source = Folder.Files("I:\Transfer\SERVICE\Service Sheets\Hub Line 3"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
        #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
        #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
        #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each Text.Contains([Custom.Name], "shift") or Text.Contains([Custom.Name], "Shift")),
        #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.Item", "Custom.Kind", "Custom.Hidden"}),
        #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each Table.PromoteHeaders([Custom.Data])),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.PromoteHeaders([Custom])),
        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each Table.PromoteHeaders([Custom.1])),
        #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.3", each Table.PromoteHeaders([Custom.2])),
        #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Custom.4", each Table.PromoteHeaders([Custom.3])),
        #"Removed Columns2" = Table.RemoveColumns(#"Added Custom5",{"Custom.Data", "Custom", "Custom.1", "Custom.2", "Custom.3"}),
        #"Expanded Custom.4" = Table.ExpandTableColumn(#"Removed Columns2", "Custom.4", {"HiddenDate", "HiddenHours", "Hidden Line", "Equipment", "Problem Description ( Do Not Include Breaks or Lunches)", "Total DT"}, {"Custom.4.HiddenDate", "Custom.4.HiddenHours", "Custom.4.Hidden Line", "Custom.4.Equipment", "Custom.4.Problem Description ( Do Not Include Breaks or Lunches)", "Custom.4.Total DT"})
    in
        #"Expanded Custom.4"

    Tuesday, September 5, 2017 2:02 PM

Answers

  • when you're in a table, you get this little table icon on the top left of the table. That contextual menu is only available within a table, and from there you can choose the option to "Remove Top Rows". You can use that option and then the Promote headers. The name of that function is Table.Skip and you'd change your code to be something like

     #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each Table.PromoteHeaders(Table.Skip([Custom.Data],4)))

    • Proposed as answer by Jake Burns Tuesday, September 5, 2017 3:09 PM
    • Marked as answer by Davis615 Tuesday, September 5, 2017 3:57 PM
    Tuesday, September 5, 2017 2:42 PM

All replies

  • when you're in a table, you get this little table icon on the top left of the table. That contextual menu is only available within a table, and from there you can choose the option to "Remove Top Rows". You can use that option and then the Promote headers. The name of that function is Table.Skip and you'd change your code to be something like

     #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each Table.PromoteHeaders(Table.Skip([Custom.Data],4)))

    • Proposed as answer by Jake Burns Tuesday, September 5, 2017 3:09 PM
    • Marked as answer by Davis615 Tuesday, September 5, 2017 3:57 PM
    Tuesday, September 5, 2017 2:42 PM
  • Hey Davis,

    Not sure if this would fit the bill but can you use Table.Skip to effectively delete the first 4 rows of the table.  Then us a single Table.PromoteHeaders step to get your headers.  Code:

    #"Promoted Headers" = Table.PromoteHeaders(Table.Skip(#"Removed Columns1", 3), [PromoteAllScalars=true])

    Hope that helps.

    Tuesday, September 5, 2017 2:58 PM
  • Thank you very much. This worked great. 
    Tuesday, September 5, 2017 3:57 PM