Forum Discussion

navafolk's avatar
navafolk
Helper IV
3 years ago
Solved

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

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 IDGroup name
11AA
12AB
22BB

 

-- 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.

  • 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.

5 Replies