Ignore table query with Expression.Error The key didn't match any rows in the table

[navafolk]

Hi pros,

I have two queries need to append.

-- 1st query (Query1): It is  picked from folder (C:\group_amount) with many spreadsheets (group_amount_yyyymm.xlsx). Each file contains data of a month, and each sheet (yyyymmdd) contains a day data. I usually take data based on date I am requested by Folder query.  My current data is up to 15 May 2023:

   Query1:

let
    Source = Folder.Files("C:\group_amount"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "group_amount_202305")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Source.Name"})
in
    #"Removed Columns"

   Transform Sample File:

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"Sheet" = Source{[Item="20230515",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Sheet",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
in
    #"Promoted Headers"

-- 2nd query (Query2): It just takes a table from my current worksheet to use as a back up table with 2 columns (1 column less than Query1):

Group ID	Group name
11	AA
12	AB
22	BB

-- 3rd query (Append): this query is to append my both queries above (Query1 and Query2)

let
    Source = Table.Combine({Query1, Query2})
in
    Source

in a nice day, append's expectation will be:

Now, the problem occurs, for some days, if the data in Query1 is not available, it will show Expression.Error The key didn't match any row in the table and it will stop my Append query too.

Please help me to ignore Expression.Error of Query1 if data is not available for that day. i.e. Append query will keep running with Query2 only:

*I tried "try Query1 otherwise", but it did not work.

[m_dekorte · Accepted solution]

Hi @navafolk,

Adapt the 'Transform file' query to Method 1 including the HandleMissing condition and set up an EmpyTable query as well. Here's an illustration

Setting up an if-then-else structure in pre- append queries 

Ps. If this helps solve your query please mark this post as Solution, thanks!

[m_dekorte]

Hi @navafolk 

Here you'll find some methods to help you deal with that

Exploring ways to handle missing table(s) in an Append 

Ps. If this helps solve your query please mark this post as Solution, thanks!

[navafolk]

Hi @m_dekorte ,

Thank you for your reply but the error still occurs. Please help me with this.

= Table.Combine(Table.ToColumns(Table.RemoveRowsWithErrors(Table.FromColumns({{Query1, Query2}}))){0})

[m_dekorte · Accepted solution]

Hi @navafolk,

Adapt the 'Transform file' query to Method 1 including the HandleMissing condition and set up an EmpyTable query as well. Here's an illustration

Setting up an if-then-else structure in pre- append queries 

Ps. If this helps solve your query please mark this post as Solution, thanks!

[navafolk]

Hi @m_dekorte,

It showed error at EmptyTable. 

Expression.Error: The name 'EmptyTable' wasn't recognized.  Make sure it's spelled correctly.

Should it be pre-defined somewhere? Thank you. 

[navafolk · Accepted solution]

Thank you @m_dekorte,

Your way works great now. I just try other way round for the EmptyTable

#table(0,{{}})

(Create an empty table with M (microsoft.com))

Transform File function in Power Query looks like:

let
    Source = (Parameter1 as binary) => let
        Source = Excel.Workbook(Parameter1, null, true),
        #"Sheet" = try Source{[Item="20230515",Kind="Sheet"]}[Data] otherwise 1,
        IsMissing = if Sheet=1
        then #table(0,{{}})
        else let
        #"Removed Top Rows" = Table.Skip(#"Sheet",1),
        #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
        in
        #"Promoted Headers"
        in IsMissing
in
    Source

 then remove null row in Query1 and they are all beautiful now.