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 · Accepted Answer · 2016-05-17 23:29:57Z

4

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).

answered May 17, 2016 at 23:29

Zhaoxing Lu

6,44222 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rory Over a year ago

Thanks, I'd assumed that Timestamp was effectively indexed. I now see I always need to use PartitionKey and then ideally use RowKey next for optimal filtering server-side.

Carl Walsh · Accepted Answer · 2016-05-20 02:39:54Z

1

There's no general way yet to see whether filtering are happening client-side or server-side, but we recognize that it would be helpful!

Since Azure Tables uses HTTP internally, if you are comfortable with Fiddler you can inspect the web requests that Power Query makes and see if the filter is being sent.

answered May 20, 2016 at 2:39

Carl Walsh

7,2273 gold badges53 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to efficiently filter Azure Table Storage data in Power Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related