none
Power Query is not updating all data on refresh from Exchange RRS feed

  • Question

  • Hi 

    I have built a power query model within Excel 2013 to read the contents of one mail folder where i have catagorised the mails using the Category feature in Outlook 2013.

    I am seeing that for example Outlook shows 771 emails not catagorised, power query shows 2014, The folder contains 3278 emails Power Query shows 4188 records.

    Excel view

    Folder view of current catagories

    let
        Source = Exchange.Contents(),
        Mail1 = Source{[Name="Mail"]}[Data],
        #"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Inbox\BU\CSI\Desktop Issues\")),
        #"Expanded Categories" = Table.ExpandListColumn(#"Filtered Rows", "Categories"),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Categories",{"DisplayTo", "DisplayCc", "ToRecipients", "CcRecipients", "BccRecipients", "DateTimeReceived", "Importance", "IsRead", "HasAttachments", "Attachments", "Attributes", "Id"}),
        #"Expanded Sender" = Table.ExpandRecordColumn(#"Removed Columns", "Sender", {"Name"}, {"Sender.Name"}),
        #"Expanded Body" = Table.ExpandRecordColumn(#"Expanded Sender", "Body", {"TextBody"}, {"Body.TextBody"}),
        #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Expanded Body", {{"DateTimeSent", type text}}, "en-US"),"DateTimeSent",Splitter.SplitTextByPositions({0, 11}, true),{"DateTimeSent.1", "DateTimeSent.2"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"DateTimeSent.1", type date}, {"DateTimeSent.2", type time}})
    in
        #"Changed Type"

    Any suggestions on where to start would be very much appreciated.

    Thanks 

    Andy

    Thursday, March 24, 2016 7:44 AM

Answers

  • UPDATE:

    I spoke with my internal IT team and this is the latest status.

    I changed my outlook configuration settings removing the tick "use cached settings" and the mailbox then updated with the old emails that were not visible in my mailbox.

    This then left me with a gap of around 500 emails. which after further investigation turn out to be one of the following types of emails;

    • Meeting notifications e.g Accepted, Rejected, Tentative
    • Missed conversation alerts from Lync/Skype for Business
    • Encrypted emails 

    It would appear PQ is not able to read these items from Exchange.

    I will assume that this is the case and investigate no further.

    Hope the above helps others


    Andy Barr

    • Marked as answer by Zanz38 Thursday, April 7, 2016 1:20 PM
    Thursday, April 7, 2016 1:19 PM
  • UPDATE:

    I spoke with my internal IT team and this is the latest status.

    I changed my outlook configuration settings removing the tick "use cached settings" and the mailbox then updated with the old emails that were not visible in my mailbox.

    This then left me with a gap of around 500 emails. which after further investigation turn out to be one of the following types of emails;

    • Meeting notifications e.g Accepted, Rejected, Tentative
    • Missed conversation alerts from Lync/Skype for Business
    • Encrypted emails 

    It would appear PQ is not able to read these items from Exchange.

    I will assume that this is the case and investigate no further.

    Hope the above helps others


    Andy Barr

    • Marked as answer by Zanz38 Thursday, April 7, 2016 1:22 PM
    Thursday, April 7, 2016 1:22 PM

All replies

  • Check out what the query returns if you leave out the "Expanded-steps" (Expanded Sender and Expanded Body)

    Imke Feldmann TheBIccountant.com

    Thursday, March 24, 2016 7:52 AM
    Moderator
  • Hi Imke,

    Thank you for the prompt reply and apologies for the delayed response. I removed as suggested and the number of pulled records remains the same with emails no longer in the mailbox still showing. I have cleared the cache via Power Query Tab > Options > Global > Data Load and this made no difference. As a work around i filtered the Query to show mail after 31/12/2015 which returned less than it should have but nearer the truth.

    Wednesday, March 30, 2016 11:10 AM
  • Same issue here, querying against an Exchange 2010 server (on-prem) using both Excel 2013 with PowerQuery, or also with Excel 2016 with Query built-in, both return 67 results for a folder (no filters or query steps), but the folder actually contains just over 100 emails.

    Shawn Keene

    Wednesday, March 30, 2016 3:10 PM
  • Sorry, but no further idea from me here.

    Imke Feldmann TheBIccountant.com

    Wednesday, March 30, 2016 4:33 PM
    Moderator
  • Hi Shawn,

    I am thinking this may be an Exchange issue and maybe not a power query issue. I deleted the connection to Exchange and set it back up. pulled all records back in from all folders and to my surprise I have emails going back to 2012, i move all emails through out the year to a annual PST file, starting a new PST file on the 1st Jan each year. So i know that within Outlook at least these emails going back further than 1st Jan 2016, will be in a PST file.

    I will raise an internal support call with my exchange team to see if they can find some of these emails on the server. Will post back the results.




    Andy Barr

    Thursday, March 31, 2016 7:08 AM
  • Hi Imke,

    Ok thanks for trying


    Andy Barr

    Thursday, March 31, 2016 7:09 AM
  • UPDATE:

    I spoke with my internal IT team and this is the latest status.

    I changed my outlook configuration settings removing the tick "use cached settings" and the mailbox then updated with the old emails that were not visible in my mailbox.

    This then left me with a gap of around 500 emails. which after further investigation turn out to be one of the following types of emails;

    • Meeting notifications e.g Accepted, Rejected, Tentative
    • Missed conversation alerts from Lync/Skype for Business
    • Encrypted emails 

    It would appear PQ is not able to read these items from Exchange.

    I will assume that this is the case and investigate no further.

    Hope the above helps others


    Andy Barr

    • Marked as answer by Zanz38 Thursday, April 7, 2016 1:20 PM
    Thursday, April 7, 2016 1:19 PM
  • UPDATE:

    I spoke with my internal IT team and this is the latest status.

    I changed my outlook configuration settings removing the tick "use cached settings" and the mailbox then updated with the old emails that were not visible in my mailbox.

    This then left me with a gap of around 500 emails. which after further investigation turn out to be one of the following types of emails;

    • Meeting notifications e.g Accepted, Rejected, Tentative
    • Missed conversation alerts from Lync/Skype for Business
    • Encrypted emails 

    It would appear PQ is not able to read these items from Exchange.

    I will assume that this is the case and investigate no further.

    Hope the above helps others


    Andy Barr

    • Marked as answer by Zanz38 Thursday, April 7, 2016 1:22 PM
    Thursday, April 7, 2016 1:22 PM
  • I'm not sure about the encrypted emails, but about others, currently we only support only a part of item types. But in the future we're thinking about adding a feature that will help customers look for any item types, that way you'll be able to see other types like meeting notifications etc.
    Thursday, April 7, 2016 5:49 PM
    Moderator
  • In my case the emails are all the same. We sent a mail merge of thousands of messages. Many bounced back as undeliverable (for example, sent to .ner instead of a .net address).  We dumped all of the bounce backs to a single folder in Outlook. They all synced, we were sure because it's a shared mailbox and others saw the full folder.  But using PowerQuery to query the folder didn't return all results.

    Additionally it didn't help us much. We were trying to easily identify the actual incorrect email address that failed to send. But the bounce-back came "from" a daemon and was "to" us, so there was no exposed field in the columns for the original "to" address that failed. Maybe we could try to find it in the body (most of the "not delivered" messages also included the original to-address), but they were in different formats.


    Shawn Keene

    Thursday, April 7, 2016 11:12 PM
  • Great thanks, would be good to get all item types, ASAP ;)

    In regards to encrypted email even if we got the sent/received data and had the body data hashed that would be better than nothing.. 


    Andy Barr

    Monday, April 11, 2016 10:22 AM
  • I was asked to pull body data in to columns using Power Query from a mailbox, we gave up as the body structure of the form data was not harmonised so trying to extract key data was next to impossible (business didn't want to harmonise all the forms and put default values in for null data to force the field being included in the email), you may run into this issue with the bounce back bodies if it is unstructured and does not contain the same fields in the same location.

    Andy Barr

    Monday, April 11, 2016 10:27 AM