英文:
Excel Power Query for Merging multiple sheets into one
问题
如何在清洗数据之前自动合并Excel中的多个工作表到一个工作表？
场景：我想设置一个查询，可以自动清洗传入的Excel文件的数据。我在这里遇到的问题是，传入的Excel文件有三个工作表，我需要在处理之前将所有这些工作表合并成一个工作表。
我尝试在线查找解决方案，但我只能找到如何在数据源是单个工作簿时将多个工作表合并为一个的方法。但在我的情况下，数据源是一个包含所有传入Excel文件的文件夹。
英文:
How to automate merging of multiple sheets into one sheet in excel before cleaning the data?
Scenerio: I want to setup a query which will clean the data automatically of incoming excel files. The problem I am facing here is that the incoming excel files have three worksheets and I need to combine all those worksheets into one sheet before processing it.
I tried to find the solution online but all I could find was how to combine multiple sheets into one WHEN THE DATA SOURCE IS A SINGLE WORKBOOK. BUT IN MY CASE, THE DATA SOURCE IS A FOLDER WHICH CONTAINS ALL THE INCOMING EXCEL FILES.
答案1
得分: 2
你可以在PowerQuery中尝试以下代码来合并一个目录中的文件：
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"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded GetFileData",{"Content", "Hidden", "Item", "Kind"}),
List = List.Union(List.Transform(#"Removed Columns"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", List,List)
in
#"Expanded Data"
英文:
you can TRY THIS IN POWERQUERY TO COMBINE THE FILES OF A DIRECTORY
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"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded GetFileData",{"Content", "Hidden", "Item", "Kind"}),
List = List.Union(List.Transform(#"Removed Columns"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", List,List)
in #"Expanded Data"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论