Forum Discussion

stever78's avatar
stever78
Brass Contributor
Jul 11, 2022
Solved

APPEND query - can we still get it to work even if one of the queries in the append has not loaded ?

  Hi all, would really appreciate your help.   I Have an APPEND power query , that appends 16 other power queries.   On occasion one or two of the "other" queries (that the append uses) do not l...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 13, 2022

    stever78 

    As workaround we may add dummy table, easiest way from here

    and modify the script as

    let
    tablesToCombine = {dummy, Table1, Table2, Table3, Table4},
    toTable = Table.FromList(tablesToCombine, Splitter.SplitByNothing() ),
    noErrors = Table.RemoveRowsWithErrors(toTable, {"Column1"}),
    #"Removed Top Rows" = Table.Skip(noErrors,1),
    Source = Table.Combine(#"Removed Top Rows"[Column1])
in
    Source

    Perhaps there is more elegant solution, will try to play with is some later. Test file is attached.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 13, 2022

stever78 

As workaround we may add dummy table, easiest way from here

and modify the script as

let
    tablesToCombine = {dummy, Table1, Table2, Table3, Table4},
    toTable = Table.FromList(tablesToCombine, Splitter.SplitByNothing() ),
    noErrors = Table.RemoveRowsWithErrors(toTable, {"Column1"}),
    #"Removed Top Rows" = Table.Skip(noErrors,1),
    Source = Table.Combine(#"Removed Top Rows"[Column1])
in
    Source

Perhaps there is more elegant solution, will try to play with is some later. Test file is attached.

stever78's avatar
stever78
Brass Contributor
Jul 13, 2022
OK That makes sense., ill give that a try. Strange isnt it why that happense if the error is on the first row only
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 13, 2022

    stever78 

    One more way is to handle error at very beginning when you load the table, like

    let
    Source = try Excel.CurrentWorkbook(){[Name="Table1"]}[Content] otherwise null
in
    Source

    I get null on error, it depends on what else you are doing. Could be dummy table which you may filter by name, whatever.

    With that to combine tables

    let
    tablesToCombine = List.RemoveNulls( {Table1, Table2, Table3, Table4} ),
    Source = Table.Combine(tablesToCombine)
in
    Source

Resources