Jul 11 2022 02:22 AM - edited Jul 11 2022 04:14 AM
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
Jul 11 2022 05:23 AM
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
Jul 11 2022 05:56 AM
Jul 12 2022 03:09 PM - edited Jul 12 2022 03:51 PM
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.
Jul 13 2022 08:11 AM
SolutionAs 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.
Jul 13 2022 09:40 AM
Jul 13 2022 01:14 PM
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
Jul 13 2022 08:11 AM
SolutionAs 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.