Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
navafolk
Helper III
Helper III

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:

navafolk_0-1684221544925.png

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:

navafolk_1-1684222078560.png

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

2 ACCEPTED SOLUTIONS

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!

 

View solution in original post

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.

View solution in original post

5 REPLIES 5
m_dekorte
Super User
Super User

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!

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})

 

navafolk_1-1684235417553.png

 

 

 

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!

 

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. 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors