使用 Power Query 将多个工作表与不同的列组合在一起 [英] Combining multiple sheets with different columns using Power Query

问题描述

我有一个包含多页的工作簿，需要将其合并(即堆叠)到一张桌子中.虽然它们有许多相似的列名，但它们并不都具有相同的列，列顺序也不同.因此，我无法使用固有的合并功能，因为它使用列顺序.Table.Combine 将解决这个问题，但我无法想出创建一个将使用每个"的语句.机械师来做到这一点.

I have a workbook with multiple pages that need to get combined, i.e. stacked, into one table. While they have many similar column names, they do not all have the same columns and the column order differs. Because of this I cannot use the inherent merge functionality because it uses column order. Table.Combine will solve the problem, but I cannot figure out to create a statement that will use the "each" mechanic to do that.

对于 x 工作簿中的每个工作表Table.Combine(上一张，下一张)退回所有堆叠的纸张.

For each worksheet in x workbook Table.Combine(prior sheet, next sheet) return all sheets stacked.

有人可以帮忙吗?

推荐答案

如果您使用 Excel.Workbook 加载工作簿，您可以选择 Sheet 类型(而不是 Table 或 DefinedName 种类)并忽略工作表名称.

If you load your workbook with Excel.Workbook you can choose the Sheet Kind (instead of Table or DefinedName kinds) and ignore the sheet names.

let
    Source = Excel.Workbook(File.Contents("C:\Path\To\File\FileName.xlsx"), null, true),
    #"Filter Sheets" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Promote Headers" = Table.TransformColumns(#"Filter Sheets", {{"Data", each Table.PromoteHeaders(_, [PromoteAllScalars=true])}}),
    #"Combine Sheets" = Table.Combine(#"Promote Headers"[Data])
in
    #"Combine Sheets"

这篇关于使用 Power Query 将多个工作表与不同的列组合在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！