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

0 votes
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.
Apr 7, 2023 in Others by narikkadan
• 63,720 points
349 views

1 answer to this question.

0 votes

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"
answered Apr 7, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,720 points
732 views
0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,720 points
1,066 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,720 points
2,141 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
4,003 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
8,468 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,521 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
5,829 views
0 votes
1 answer

Power Query - Find matching contents from multiple other tables

Try entering this code in Powerquery's input ...READ MORE

answered Feb 16, 2023 in Others by Kithuzzz
• 38,010 points
343 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP