SOLVED

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

Brass Contributor

 

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 load due to thier own source table not being available

(that is fine , its not ideal, but sometimes will happen, no problems)

 

so can i get the APPEND query to still work and show results for the 14 other power queries that DO HAVE data and have loaded.

 

At present the APPEND just says , "expression error the KEY doesnt match any rows in the table". (obviously becuase 2 of those queries in the APPEND hasn't loaded) 

 

is this possible to sort of tell the append - just load the tables that you DO have data for ???

A sort of SKIP queries that didnt load and continue to produce and refresh the ones that DID load ?

 

many , many, thanks for your help. Steve R

 

unfortunately i cannot attach my worksheet due to data protections

7 Replies

@stever78 

As variant

let
    tablesToCombine = {Table1, Table2, Table3, Table4},
    toTable = Table.FromList(tablesToCombine, Splitter.SplitByNothing() ),
    noErrors = Table.RemoveRowsWithErrors(toTable, {"Column1"})[Column1],
    Source = Table.Combine(noErrors)
in
    Source

adding first 3 rows in Advanced Editor

Sergie, i have said it before but ill say it again, you are a star!! very well done and many thanks this has worked perfectly

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.

 

@Sergei Baklan 

best response confirmed by stever78 (Brass Contributor)
Solution

@stever78 

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

image.png

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.

OK That makes sense., ill give that a try. Strange isnt it why that happense if the error is on the first row only

@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
1 best response

Accepted Solutions
best response confirmed by stever78 (Brass Contributor)
Solution

@stever78 

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

image.png

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.

View solution in original post