英文:
Combing data from multiple workbooks into a single master workbook using power query
问题
我目前正在提出一个关于报告管理的解决方案。目标是将我们目前使用的所有报告和字段的信息存储在一个主工作簿中。为此，计划是在一个文件夹中保存每个报告的副本，然后让主工作簿从此文件夹中的工作簿中提取数据，仅获取标题，因为它们将指示所使用的字段。目前，我正在为小范例工作簿的概念验证阶段。但我在尝试只显示每个报告的标题作为行时遇到了困难。需要帮忙吗？ 
目前完全迷失了。理想情况下，我希望每一行对应于保存在所选文件夹中的工作簿，第一个单元格是文件名，然后行中的后续单元格是报告的列标题。
英文:
I am currently proposing a solution regarding report governance. The aim is to store information of all the reports and fields we currently use in one master workbook. To do this the plan is to save a copy of each report in a folder and then have a master workbook pull the data from workbooks in this folder, taking just the headings as they will indicate the fields used . Currently I am at the proof of concept stage for small example workbooks. But I am struggling to get only the headers of each report to show on the master workbook as a row each. Any help ?
Currently completely lost. Ideally I want each row to correspond to a workbook saved in the selected folder with the first cell being the filename and then the subsequent cells in the row being the column headers of the reports
答案1
得分: 1
Here's the translated code section:
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"
英文:
Below in powerquery would read in the contents of all the files in a directory, and just return the values on the first row of data, which presumably has your column headers on them. It includes the name of the tabs and file
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"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论