Designing PartitionKey and RowKey in my Azure Table storage

Question

I am designing my data model which is going to use Azure table Storage as database. I am in process of defining my Partition key and RowKey and as per the good practice, my Partition key and Row key should be well designed in ordered to query my database and also query in visualization tool like Power Bi.

I have come into conclusion that my PartitionKey and Rowkey can be combination of few different columns in my data. Partition Key to (testclass|testid)and RowKey to (testname|testEndtime|testVoltage|index). Here testclass,testid, testname,testEndtime,testVoltage are columns of my existing table and index is going to be row number(in order to make it unique)

My question:

how can I query if I have such Rowkey as defined above. for example if I would like to query my data between two testEndtime values how can I do it using Rowkey? Remember in visualization tool like Power bi, we only have 3 columns(PartitionKey ,RowKey and timestamp) to query and filter our data on server side.(one would know if they have worked with Azure table storage)

I checked one example and they did query like this in Power Bi

#"Filtered Rows" = Table.SelectRows(tablename, each ([PartitionKey] >= "XXXX" and [PartitionKey] <= "YYYYY" )),

Anybody??

Answer 1

PowerBI does not support DirectQuery/Live connection to Azure Table Storage.

What this means is, data from Table Storage is cached within PowerBI and queried from the cache instead of the Table directly when you filter data on the report. So, how you design the PartitionKey and RowKey is not particularly important if PowerBI is the only client consuming data from your Table.

To access other columns in the Table and use them in visuals and filters, expand the Content group.