none
Power Query to Append to Existing Table RRS feed

  • Question

  • I recently switched to PowerQuery to fetch data from various sources.

    I have loaded my existing data to a table called "masterEntries". I loaded this data manually to a table in my data model. It has 400K records and grows fast (10-15K per week).

    I get fresh data using the following query. This query calls a function to check the last record for each source in "masterEntries" and fetches only newer records. This query is called "latestEntries".

    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"

    I want to append the data fetched by "latestEntries" to "masterEntries".  But it appears there is no way to do this. 

    When I try "Append Query" it does the append but loads the data to a new table. And the append operation is just a connection where two or more queries/results are combined. Since "latestEntries" always checks the last record_id in "masterEntries" and fetches newer records, the appended table also changes losing many records.

    Right now, I have made the "latestEntries" load to a table. Then I use a VBA script to add the records from this table and then empty this table.

    Any ideas on how I can avoid VBA and do the whole thing with PowerQuery?


    Thursday, December 1, 2016 9:26 AM

Answers