I'd suggest to drop the partitioning and all the mess associated with it.
Create clustered index started with [BackupDate] on each of the tables.
When retrieving data for a specific...

There is a fact which is denied by absolute majority of IT community:

So called "full stack developers" cannot do T-SQL code.

Well, they know the syntax, and they always can ask...

You need to start with converting utf-8 to USC-2, which is readable for SQL Server.
Mr.Google can help you with several of such functions.
After that you may use XML...

Should the date-times be just displayed in different time zone, or filtering conditions should also change?

In each temp table a character column must have the same collation as columns it's matched to in JOIN or WHERE clauses.

To make the code independent from tempdb collation every...

When you as a sysadmin create / alter a procedure on a user database it's saved as created by user 'dbo' of that database.

When you execute that procedure the code...

One of the biggest issues here is the join by TimeId.

If you can change the table design, you better drop the column TimeId from dbo.Time, make Date ap...

Adding to what's said by Frederico - by clustering on StartTime and applying the filter against this column you'll most certainly make the query run much faster.

Well, I stand by my suggestion - the best long time solution to the performance problems you have is to move away from using PowerBI.
Because query tools which build...

Before starting on this queries I have to ask:

Where do they come from?

What/who wrote/generated these queries?

Is there a possibility to change the way they built?