附加到现有表的 Power Query [英] Power Query to Append to Existing Table

问题描述

我最近切换到 PowerQuery 以从各种来源获取数据.我已将现有数据加载到名为masterEntries"的表中.

I recently switched to PowerQuery to fetch data from various sources. I have loaded my existing data to a table called "masterEntries".

我的查询调用了一个函数来检查masterEntries"中每个源的最后一条记录，并只获取较新的记录.

The query I have calls a function to check the last record for each source in "masterEntries" and fetches only newer records.

let
    Source = Excel.CurrentWorkbook(){[Name="formsMaster"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FormName", type text}, {"Form", type text}, {"LastEntry", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each formEntries([FormName],[LastEntry])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"EntryId", "Field1", "Field2", "Field3", "Field5", "DateCreated"}, {"EntryId", "Field1", "Field2", "Field3", "Field5", "DateCreated"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Form", "LastEntry"}),
in
    #"Removed Columns"

此查询将数据加载到新表中.相反，我想将数据附加到masterEntries".

This query loads the data to a new table. Instead I want to append the data to "masterEntries".

我正在尝试使用 PowerQuery 而不是 VBA 来做到这一点.PowerQuery 具有追加查询功能，可以将两个或多个查询/结果组合到一个新表中.

I am trying to do this with PowerQuery and not VBA. PowerQuery has Append Query feature where two or more queries/results can be combined to a new table.

即使是将上述查询的结果表(latestEntries")附加到现有表(masterEntries")的新查询也可以.

Even a new query to append the resulting table from above query ("latestEntries") to existing table ("masterEntries") will do.

关于如何使用 PowerQuery 完成的任何想法?

Any ideas on how it can be done with PowerQuery?

编辑

1. 我的原始数据(masterEntries")是手动加载的.这是一张有 400K+ 条记录的大表.如果有帮助，我可以使用查询加载它.
2. 每次运行latestEntries"都会检查masterEntries"中已有哪些记录，并仅从不同来源获取较新的条目.
3. Power Query 中的 Append Query 方法只是一个连接.它不会永久附加记录.也就是说，当latestEntries"带来一组新记录时，masterEntries"会丢失较早运行latestEntries"中的记录.

推荐答案

这听起来有点像增量加载"的请求.Excel 中的 Power Query 目前不支持此功能.解决方法是通过linkback"表，如下所述:http://ms-olap.blogspot.de/2015/05/incremental-data-loads-in-microsoft.html

This sounds a bit like a request for "incremental load". This is currently not supported by Power Query in Excel. The workaround is to go via a "linkback"-table like described here: http://ms-olap.blogspot.de/2015/05/incremental-data-loads-in-microsoft.html

如果您的链接表超过 1,1 Mio 行，您可以使用 JSON 压缩，如下所述:http://www.thebiccountant.com/2016/12/06/how-to-store-tables-长于 11-mio-rows-in-excel/但请注意，这会降低性能.

If your linkback-table exceeds 1,1 Mio rows, you can use JSON-compression like described here: http://www.thebiccountant.com/2016/12/06/how-to-store-tables-longer-than-11-mio-rows-in-excel/ But be aware, that this costs performance.

这两种方法都会消耗"性能，因此这种技术只有在您节省"重复执行非常繁重的转换(或来自网络的长时间加载)时才有意义.

Both methods "cost" performance, so this technique only makes sense, if you "save" repetitive execution of really heavy transformations (or long loads from the web).

这篇关于附加到现有表的 Power Query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！