Forum Discussion

Deepak's avatar
Deepak
New Member
9 years ago

how to limit email data source to specific folder

Hi Guys, 

 

Hope you can help me with this one.

 

I have a report that is currently using an Exchange Online data source to collect emails, however as the mail box is fairly large, i would like to apply a filter so that it only pulls data from a nominated mail folder. In my case the Inbox.

 

 

I have tried applying a filter to do this, but it still refreshes the entire mailbox, which does take time.

 

Any ideas? my data source settings below....

 

Source = Exchange.Contents("[email protected]"),
Mail1 = Source{[Name="Mail"]}[Data],
#"Filtered Rows" = Table.SelectRows(Mail1, each true),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attachments", "Preview", "Body", "Attributes"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Categories", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Expanded Sender" = Table.ExpandRecordColumn(#"Extracted Values", "Sender", {"Name"}, {"Sender.Name"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Sender", each ([Folder Path] = "\Inbox\"))
in
#"Filtered Rows1"

Need Help

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi Deepak,

     

    According to my test, the step "#"Filtered Rows"" in your codes seems to be failed. It looks like this in my test:

     

    #"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Drafts\"))

    There are only one row in my test, so the refreshing is very fast.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • NEJO's avatar
      NEJO
      Advocate I
      8 years ago

      I also get this exact same issue.

       

      Its not that the filter within the steps don't work, but when you apply the steps - and it starts the download, I can see it running through all of the folders initially rather than just going to the selected set of folder.

       

      My guess is that its step 1 and 2 which still require the entire dataset to be pulled into the query for it to then be filtered.

       

      Is there a way around this, so we can select at source a specific folder, as without I seem to be struggling and have to wait an hour for the full dataset to download to enable me to apply any new steps.

       

      Thanks

      Neil

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hello Everybody,

         

        As Nejo said, filters have no effect and when you apply them, all mails are load first and then it is filter. 

         

        So during refresh or loading you can see PBI desktop running through all of the mailbox folders initially rather than just going to the selected set of folder. 

         

        Did anyone find a work around to avoid this loss of time?

         

        Kinds Regards, 

         

        Marius

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Very late reply here, but I believe I've been able to find a solution for this. When connecting to the MS Exchange account, if you right-click on the Mail item in the Navigator window -> transform data, then filter out the subfolders you require, it seems to only refresh the subfolder(s) (rather than the entire mailbox). I'm not sure how this is different to filtering out the subfolder after you load in the entire mailbox, but it seems to have sped up my refreshes massively!