Comparing data between Excel and Outlook

[CVianello]

Currently we have and email that is sent to a dedicated inbox with a user request. These are also sent to a third party vendor for processing (via a different method). At 15:20 (cut off for requests are 15:00), an Excel file is sent by the third party vendor via FTP with the client requests to another third party. On occasion, the number of email confirmations we receive do not match up with the number of requests in the Excel file sent via FTP. This process is currently done manually.

Problem 1: I am tying to count the number of emails where subject =X, but only from 15:00 the previous day up until that point (so basically the counter resets to 0 at 15:00 every day). NB, from Friday 15:00 to Monday 14:59:59s is considered 1 cycle as requests can be received over the weekends, but are not sent until Monday.

*Also, is it possible to save this data to a table once 15:00 hits?

Problem 2: The FTP receives the file at 15:20, I would like PBI to be able to read the file, count the number of rows (-1 for the title row) and compare this with the number generated in problem 1? Im sure I could do this through a PS script, but I want to try and keep this within PBI.

Thanks!

CV

[v-frfei-msft]

Hi @CVianello ,

To create a custom column as below.

#datetime(Date.Year([date]), Date.Month([date]), Date.Day([date]),Time.Hour([date]), 0, 0)

M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ3MAQiBUNTKyMDKwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(Date.Year([date]), Date.Month([date]), Date.Day([date]),Time.Hour([date]), 0, 0))
in
    #"Added Custom"

[CVianello]

Hi @v-frfei-msft ,

Thanks for that, but that doesnt help with my issue unfortunatly. I can see how the solution is good for grouping by hour, but thats not what I am wantiong to do.

Thanks

CV