鉴于电源查询中的工作表数量总是不同的范围/工作表名称，如何将不同的工作表合并到一个文件中 [英] How to combine different sheets into one file given the numbers of sheets always different range/sheet name in power query

问题描述

使用 Power Query，我将不同的工作表合并为 1 个合并工作表(即 AllPermit).

Using Power Query, i combine different sheets into 1 consolidate sheet(i.e. AllPermit).

当每个月的张数因许可证交易而异时，我遇到了问题.例如，APR - 我总共有 4 张.

I facing problem when the numbers of sheets each month is different depending on Permit transactions. Eg, APR - I have total of 4 sheets.

• Permit_1_1000

• Permit_1_1000

Permit_1001_2000

Permit_1001_2000

Permit_2001_3000

Permit_2001_3000

Permit_3001_4000

Permit_3001_4000

5 月，销量下降，最终只有 3 张.

On May, sales drop and end up will only have 3 sheets.

有没有办法让我不要每个月都重新进行电源查询，这样它就不会出现寻找 Sheet#4 的错误?还有，当有新床单时，可以智能检测到有5张床单?

Is there any solution for me not to redo the power query again every month, so that it wont have errors that looking for Sheet#4? Also, when there is new sheets, it can be intelligently detected there are 5 sheets?

非常感谢任何建议.

推荐答案

这样的事情对我有用，将目录中工作簿上的所有选项卡、所有列、所有行组合起来

Something like this works for me, combining all tabs, all columns, all rows, on workbooks in directory

let Source = Folder.Files("C:\directory\subdirectory"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"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", "Name2"}),
#"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"

感谢 Imke 提供列表步骤

Thanks to Imke for List step

这篇关于鉴于电源查询中的工作表数量总是不同的范围/工作表名称，如何将不同的工作表合并到一个文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！