Expression.Error: The key didn't match any rows in the table - Exchange Data Import

[Anonymous]

Hi fellow PBIers,

First thing: I know there are several solutions to the Error in the Subject but none of them worked for me.

My problem is as follows:

• Built a report & query which igets its data from Exchange Email-Attachments (xlsx-files)
• Report and query work fine for one day
• When the new data on the next morning arrives the query "crashes" with the mentioned "key didnt match any rows in the table"
• It seems to crash because the step in which it opens the attachment of the mail is based on id-key which seems to change for every new mail:
  
  the code from the advanced editor looks as follows:
  

  #"AAMkADYzMmZiMmU5LTAzYzMtNGMzYS05MWIwLTg2ZGI2OTE3NGE5ZABGAAAAAACM8/gWvep/QZRIO1D6QBW8BwAjFkhrLDLaT66NTvauBkrRAADloYILAAAjFkhrLDLaT66NTvauBkrRAAG+e/lUAAA=" = #"Filtered Rows1"{[Id="AAMkADYzMmZiMmU5LTAzYzMtNGMzYS05MWIwLTg2ZGI2OTE3NGE5ZABGAAAAAACM8/gWvep/QZRIO1D6QBW8BwAjFkhrLDLaT66NTvauBkrRAADloYILAAAjFkhrLDLaT66NTvauBkrRAAG+e/lUAAA="]}[Attachments],
      AttachmentContent = #"AAMkADYzMmZiMmU5LTAzYzMtNGMzYS05MWIwLTg2ZGI2OTE3NGE5ZABGAAAAAACM8/gWvep/QZRIO1D6QBW8BwAjFkhrLDLaT66NTvauBkrRAADloYILAAAjFkhrLDLaT66NTvauBkrRAAG+e/lUAAA="{0}[AttachmentContent],
      #"Imported Excel" = Excel.Workbook(AttachmentContent),

  From my understanding the query looks to import from the mail with that exact id.
  
  If it would be possible to do this more "dynamic" so that the query really only applies the filter to get the newest mail, extract data from that and import it, then i guess the problem would be solved? Unfortunately i dont know yet how to do this. Has anyone an idea how to solve this?

Thanks a lot in advance and kind regards,

Max

[v-chuncz-msft]

@Anonymous,

You may select Sort Descending and Keep Top Rows under Home tab.

[Anonymous]

@v-chuncz-msft thanks for the reply.

i currently perform the following actions before selecting the attachment:

1. sort descending by datetimereceived
2. keep top rows: 2
3. filter by name identifier
4. open attachment

i tried ur solution by only keeping one top row and then opening the attachment, but it still takes the attachment based on the email-id. not quite there yet.

[Anonymous · Accepted solution]

Okay, so I actually found a solution that works. Gonna leave this here too, in case anyone runs into the same problem:

To choose the attachment not based on the Email-ID (like it automatically does) you have to open the "Advanced Editor" in your Power Query and then replace the following code part:

 #"AAMkADYzMmZiMmU5LTAzYzMtNGMzYS05MWIwLTg2ZGI2OTE3NGE5ZABGAAAAAACM8/gWvep/QZRIO1D6QBW8BwAjFkhrLDLaT66NTvauBkrRAADloYILAAAjFkhrLDLaT66NTvauBkrRAAG+e/lTAAA=" = #"Filtered Rows1"{[Id="AAMkADYzMmZiMmU5LTAzYzMtNGMzYS05MWIwLTg2ZGI2OTE3NGE5ZABGAAAAAACM8/gWvep/QZRIO1D6QBW8BwAjFkhrLDLaT66NTvauBkrRAADloYILAAAjFkhrLDLaT66NTvauBkrRAAG+e/lTAAA="]}[Attachments],
    AttachmentContent = #"AAMkADYzMmZiMmU5LTAzYzMtNGMzYS05MWIwLTg2ZGI2OTE3NGE5ZABGAAAAAACM8/gWvep/QZRIO1D6QBW8BwAjFkhrLDLaT66NTvauBkrRAADloYILAAAjFkhrLDLaT66NTvauBkrRAAG+e/lTAAA="{0}[AttachmentContent],

with:

#"SelectAttachment" = #"Filtered Rows1"{0}[Attachments],
   AttachmentContent = SelectAttachment{0}[AttachmentContent],

then your query will just open the attachment of the Email you filtered for (and ignore the ID)

kind regards, max