Combing data from multiple workbooks into a single master workbook using power query

Question

I'm now drafting a plan of action for report governance. The objective is to consolidate all of the reports and fields that we now utilise into a single master workbook. To do this, it is planned to save a copy of each report in a folder, after which a master workbook will pull data from the workbooks in the folder, relying just on the headings, which will serve as an indicator of the fields used. I'm in the process of developing little example workbooks as proofs of concept. Nevertheless, I am having trouble getting the master workbook to display only the headers of each report as a row. Any assistance?

Right now, I'm totally lost. My ideal scenario is for each row to represent a workbook saved in the chosen folder, with the first cell representing the filename and the following cells representing the report's column heads.

Answer 1

PowerQuery would scan the contents of all the files in the directory listed below and only provide the values for the first row of data, which is likely to contain the names of your column headings. It contains the file and tab names.

let Source = Folder.Files("C:\subdirectory\directory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
#"Added Custom1" = Table.AddColumn(#"Expanded GetFileData", "Data2", each Table.FirstN(Table.DemoteHeaders([Data]),1)),
List = List.Union(List.Transform(#"Added Custom1"[Data2], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom1", "Data2", List,List),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Content", "Data", "Hidden", "Item", "Kind"})
in #"Removed Columns"