How to efficiently filter Azure Table Storage data in Power Query

Question

I want to pull data from azure table storage into Excel 2016 / Power Query. It's hard to find good documentation on exactly what's going on, but whatever it is it's slow. I have a large table so I want to ensure a filter is applied at the Azure end rather than pulling all the data to me and filtering client-side.

Here's my query so far (built using the designer but this is the Advanced Editor version):

let
    Source = AzureStorage.Tables("mystorageaccount"),
    ElmahLogs1 = Source{[Name="ElmahLogs"]}[Data],
    #"Filtered Rows" = Table.SelectRows(ElmahLogs1, each [Timestamp] > #datetime(2016, 5, 12, 12, 0, 0)),
    #"Expanded Content" = Table.ExpandRecordColumn(#"Filtered Rows", "Content", {"HostName", "Type", "Source", "Message", "User", "StatusCode", "AllXml", "PublicId"}, {"Content.HostName", "Content.Type", "Content.Source", "Content.Message", "Content.User", "Content.StatusCode", "Content.AllXml", "Content.PublicId"})
in
    #"Expanded Content"

As you can see I'm trying to filter by Timestamp first, assuming that'll be able to be processed pretty swiftly on Azure. But it seems to take minutes rather than seconds.

Is there a way to see if the filtering is being done client-side or server side?

Is there a better way to pass a filter expression to Azure, e.g. put an OData filter somewhere in the AzureStorage.Tables() function or Source() function?

Zhaoxing Lu Zhaoxing Lu · Accepted Answer · 2016-05-17T23:29:57

Filtering only on Timestamp will cause a whole table scan which is very inefficient. The best practice for Azure Table querying is to at least filter PartitionKey. For your scenario, please consider redesigning your table per Log Tail Pattern here (I strongly recommend you to read the whole article since it can help you understand Azure Table well).

How to efficiently filter Azure Table Storage data in Power Query

2 Answers