I am looking best way to query Azure table storage for non index column within a partition.

For example we have a table “Employee” where Partition Key is assigned with department and Row Key is assigned with EmployeeId.
Table has below schema.
PartitionKey = Department
RowKey = EmployeedId
Name,
Email,
Location

So if I need to get the data for all employee in given location and given partition knowing that there is not other index column , how more efficient query can be written.

Got to know about “Intra-partition secondary index pattern” which may give the desired performance but at the same time it will require more storage as same row supposed to copied in same partition with different value in RowKey column.

I came across an idea about combining location and EmployeedId values to assign to RowKey column
like us-1001, in-2001 which may work well for querying the data when we know which location and which employee , data needs to be fetched
$filter=(PartitionKey eq ‘Engineering’) and (RowKey eq ‘in-2001’)

But what if I need only records for a given location and partition
$filter=(PartitionKey eq ‘Engineering’) and (RowKey like ‘in’)

So, in this case Would the above query will scan all records in the given partition (as a kind of like operation to be performed to search location wise data) ?