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.

stever78's avatar
stever78
Brass Contributor
Jul 12, 2022

UPDATE - Question not completely solved.
Something very strange. IF one of the ERROR appended queries is the FIRST table in the appended list  then this code above still throws a KEY error.
However if one of the ERROR queries is ANYWHERE else in the list of queries then it works fine.
Do you have any ideas Sergei ??
my code is
tablesToCombine = {Weighted_1_1, Weighted_2_1, Weighted_3_1, Weighted_4_1, Weighted_5_1},
toTable = Table.FromList(tablesToCombine, Splitter.SplitByNothing() ),
noErrors = Table.RemoveRowsWithErrors(toTable, {"Column1"})[Column1],
Source = Table.Combine(noErrors),

rest of code
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "Weighted"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Weighted] <> null and [Weighted] <> "")
in
#"Filtered Rows"

so i have attached two pics of the line in the power query.
1 IF the error is in the first row, then i get a further error below and the whole append doesnt load

Or

2 IF the error is not in the first line but in ANY other row, then it works and the NO ERRORS next step loads and all is fine.

 

How strange is this, is there something obvious in the coding that is causing this fualt.

 

many thanks for your help, i hope this can be solved, as it was going well initially, thanks steve

 

So in pic one, error at top and the rest of the query including No errors step doesnt load and i get the KEY faults

 

But in pic2, error at the bottom, then everything other step loads ok and my data loads fine.

 

SergeiBaklan 

  • 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