How do I connect Power BI to timetoreply?

timetoreply has a read-only OData feed that Power BI reads with its built-in OData connector. You load the tables you need, build your report, and the Power BI Service refreshes it on a schedule. You don’t need a gateway, a custom connector or any manual exports.

This guide covers:

1. Creating a personal access token
2. Connecting Power BI Desktop
3. Choosing what the report tables calculate
4. Checking the relationships
5. Publishing and scheduling a refresh
6. Replacing a token

For the tables and columns, see Power BI tables and columns. For help with the figures, see Reading timetoreply numbers in Power BI.

Already using Power BI with timetoreply?

Until now, we told customers to connect Power BI to timetoreply’s API with the Web connector (“Web API” or “Web page” in Get data). The OData feed is a different data source, not a new address for the old one. Changing the URL in an existing Web query won’t work. Build new queries with the OData connector as described below. Your existing Web connections keep working.

Before you start

You need:

• Power BI Desktop for Windows, to build the report.
• A timetoreply plan that includes API and BI tool access.
• A timetoreply Admin, Company Manager or Restricted Manager login. Managers also need the Manage Access Tokens page permission.
• Access to the reports you want to load. A table only appears in Power BI if you can open the matching report in timetoreply. See who sees which tables.

The feed address is:

https://portal.timetoreply.com/odata/v1/

1. Create a personal access token

Power BI signs in to timetoreply with a personal access token, not your timetoreply password.

1. In timetoreply, go to Tools → Access Tokens.
2. Click Create new Personal Access Token.
3. Enter a Name you’ll recognise later, such as Power BI weekly report, and click Create Access Token.
4. Click Click to Copy, and paste the token somewhere safe until you’ve entered it in Power BI. It’s shown only once. If you lose it, create a new one.

Treat the token like a password

• The token acts as you. Power BI sees exactly the teams, mailboxes and reports you can see in timetoreply. A Restricted Manager’s token only reaches their own teams and mailboxes.
• The token is not limited to reporting. The OData feed is read-only, but the token carries your account’s full permissions on the timetoreply API, including making changes there. Anyone who has it can do anything your account can do through the API.
• The token expires one year after you create it. The expiry date is shown next to it on the Access Tokens page. When it expires, Power BI’s refresh fails until you replace it.
• Only enter the token in Power BI’s sign-in boxes. Never put it in a query, a parameter, a report or an email.

To revoke a token, click the × next to it on the Access Tokens page. Anything using it stops working straight away.

2. Connect Power BI Desktop

1. In Power BI Desktop, click Home → Get data → More….

2. Search for OData and choose OData feed. Then click Connect.

   Caution

   Don’t choose Web API or Web page. They accept the same address and token without complaining, but they return raw text instead of tables.

3. Paste the feed address, https://portal.timetoreply.com/odata/v1/, and click OK. Use this address exactly. Don’t add a table name to the end.

4. When Power BI asks how to sign in, choose Basic on the left.

   • User name: token
   • Password: your personal access token

   If there’s a choice of which address the settings apply to, choose https://portal.timetoreply.com/odata/v1/. Then click Connect.

5. If Power BI asks for a privacy level, choose Private. See Privacy levels.

6. In the Navigator, tick the tables you want. Only load what you need: the log tables (Conversations and Messages) are by far the largest.

7. Click Transform Data, not Load, if you’ve ticked any report tables. You’ll choose what they calculate in the next step.

3. Choose what the report tables calculate

The report tables (SlaReport, OverviewReport, AwaitingResponseReport and their …Mailboxes versions) aren’t stored lists. timetoreply calculates them when Power BI asks, the same way it calculates the reports on screen.

Without filter steps, a summary report table returns the last completed week, communicating with anybody. For Admins and Company Managers, that is one company-wide row. For Restricted Managers, it is one row per visible team. Per-address tables split those selections by address. Add filter steps to choose your breakdown explicitly.

To get the breakdown you want, add filter steps in Power Query. timetoreply reads them and calculates every combination you select:

To get	Filter the column	For example
A range of weeks	PeriodStart	is on or after a Monday
Every team you can see	ReportForType	equals Team
Particular teams	ReportForId	equals a TeamId from the Teams table
External and Internal separately	CommunicatingWithType	equals EverybodyElse or equals MyDomains
Awaiting response over 4, 6 and 8 hours	NoReplyForSeconds (awaiting tables only)	equals 14400, 21600 or 28800

EverybodyElse is External: addresses outside your company’s own domains. MyDomains is Internal. They match Everybody else and My domains on the report screens.

For example, this query loads every team, External and Internal, for the last five completed weeks. To use it, open Home → Advanced Editor on the SlaReport query and replace everything in it:

let
    Source = OData.Feed("https://portal.timetoreply.com/odata/v1/", null, [Implementation = "2.0"]),
    SlaReport = Source{[Name = "SlaReport", Signature = "table"]}[Data],
    Teams = Table.SelectRows(SlaReport, each [ReportForType] = "Team"),
    ExternalAndInternal = Table.SelectRows(Teams,
        each [CommunicatingWithType] = "EverybodyElse" or [CommunicatingWithType] = "MyDomains"),
    CurrentWeekMonday = Date.StartOfWeek(Date.From(DateTime.LocalNow()), Day.Monday),
    LastFiveWeeks = Table.SelectRows(ExternalAndInternal,
        each [PeriodStart] >= Date.AddDays(CurrentWeekMonday, -35) and [PeriodStart] < CurrentWeekMonday)
in
    LastFiveWeeks

The same three steps work on the other report tables. Change "SlaReport" to the table’s name. On the awaiting-response tables, add a comma after the LastFiveWeeks step, add the following step before in, and change the final line after in to Thresholds:

    Thresholds = Table.SelectRows(LastFiveWeeks,
        each [NoReplyForSeconds] = 14400 or [NoReplyForSeconds] = 21600 or [NoReplyForSeconds] = 28800)

Tips for filter steps:

• Put them first, straight after the table is opened, before you change types, remove columns or merge anything. Otherwise Power BI may apply them itself, to the one default row, instead of sending them to timetoreply.
• Type the values in. A column’s tick-list only offers values that are already in the preview, so Team isn’t there while the preview shows MyCompany. Use Text Filters → Equals and type the value.
• Use Or, not And, for two values in the same column. “Equals EverybodyElse and equals MyDomains” can never be true, so it returns nothing.
• Use “equals” and date comparisons. These are the filters we’ve seen Power BI send to timetoreply. To check the result, change the example from -35 to -28: the summary should change from five weeks to four. Then restore -35. If the requested weeks do not appear, the filter may be running locally. For a closer look, use Tools → Query Diagnostics in the Power Query editor; see Microsoft’s Query Diagnostics guide.
• Only completed weeks. Weeks run Monday to Sunday in the token owner’s timetoreply time zone. A range that includes the current week is refused.
• Stay within the limits. One table can calculate up to 500 combinations at a time (weeks × teams × communication types, and × thresholds on the awaiting tables) and cover up to 53 weeks. Five weeks × two communication types × three thresholds is 30 per team, so that’s up to 16 teams. See Limits.

A refused filter shows as an empty table

If timetoreply refuses a selection, for example because it includes the current week or is over the limit, Power BI shows an empty table with no error. If a report table is unexpectedly empty, check your filter steps against the tips above.

When the queries look right, click Close & Apply.

A rolling window can pick the wrong Monday

DateTime.LocalNow() uses your computer’s date in Desktop, and UTC in the Power BI Service. timetoreply counts weeks Monday to Sunday in the token owner’s time zone. When those dates fall on different sides of Monday, this query can select the wrong five weeks, or include the current week and come back empty. To follow the token owner’s time zone, replace Date.From(DateTime.LocalNow()) with a Date parameter set to today in that time zone. The parameter stays on the date you enter until you change it.

4. Check the relationships

Power BI guesses relationships from column names and the data it has just loaded, and some of its guesses will be wrong. Open Model view → Manage relationships and check them:

• Relate tables on their IDs: TeamId, MailboxId and GroupMailboxId. Use Many to one and a Single cross-filter direction, even if today’s data makes a column look unique.
• Add ReportForId → Teams[TeamId] yourself when your report tables are filtered to teams. Power BI can’t find it because the column names differ.
• Delete any relationship between two report tables, such as SlaReport to SlaReportMailboxes on PeriodType. Relate each report table to shared lookup tables, such as Teams or a weeks table, instead.
• Delete EmailAddress relationships to MailboxAddresses. Use MailboxId → Mailboxes[MailboxId] instead, and make sure it’s active.

Before you build visuals, read Reading timetoreply numbers in Power BI. Team totals and SLA percentages need care.

5. Publish and schedule a refresh

Your sign-in details stay on your computer. They don’t go with the report when you publish it, so you enter them again in the Power BI Service.

1. In Power BI Desktop, click Home → Publish and choose a workspace.

2. In the Power BI Service (app.powerbi.com), open the workspace. Publishing creates two items with the same name: a report and a semantic model. Find the semantic model, open its ⋯ menu and choose Settings.

3. Expand Data access and click Data source credentials, then Edit credentials. “Data access” is a collapsible heading and easy to miss.

4. In the Configure window:

   • Authentication method: Basic
   • User name: token
   • Password: your personal access token
   • Privacy level setting for this data source: Private, unless you combine sources
   • Leave Skip test connection unticked.

   Click Sign in.

5. Click Refresh in the list on the left of the same page. Check the Time zone, turn Configure a refresh schedule on, choose a Refresh frequency and a Time, and click Apply. Click Add another time to refresh more than once a day.

6. Under Send refresh failure and critical warning notifications to, add a shared mailbox under These contacts as well as the owner. See the note below.

7. To check it works, click Refresh now on the semantic model, then View refresh history.

You don’t need a gateway. Under Gateway and cloud connections, Power BI says so and shows the gateway switch off.

Good to know

• Enter the credentials before you refresh. If you press Refresh first, it fails at once with “Scheduled refresh has been disabled.” Enter the credentials, then check the schedule is still on.
• A scheduled refresh starts a few minutes after its time, and can take a quarter of an hour before Power BI contacts timetoreply. A quiet start isn’t a failure.
• The credential boxes are empty when you reopen Edit credentials. That’s normal: Power BI doesn’t show a saved password.
• Power BI only emails when a scheduled refresh fails, not a manual one. That email is often your only warning that a token has expired or been revoked, so send it to a mailbox someone watches. See My Power BI refresh failed.

Replace a token

Replace your token before it expires, or as soon as possible after it’s revoked.

1. Create a new token on the Access Tokens page.
2. In the Power BI Service: open the semantic model’s Settings → Data access → Data source credentials → Edit credentials, and sign in again with Basic, user name token and the new token. Then click Refresh now to check it. You don’t need to republish.
3. In Power BI Desktop: click File → Options and settings → Data source settings, choose https://portal.timetoreply.com/odata/v1/, then Edit Permissions → Edit. Choose Basic and enter token and the new token.
4. Revoke the old token on the Access Tokens page.

Each place that refreshes the report needs the new token, including every published semantic model that uses it.

Privacy levels and combining with other data

The privacy level has nothing to do with sign-in or who can see your report. It only controls whether Power Query may send data from one source to another when a single query combines them.

Level	Meaning
Private	Data from this source is never sent to another source.
Organizational	Data can be sent to Private and other Organizational sources, but not Public sources.
Public	Data can be sent to any source.

• We recommend Private. If your model only reads timetoreply, it makes no difference.
• If you merge timetoreply tables with another source in Power Query, such as your CRM or a spreadsheet, Private can cause a Formula.Firewall error or a slower refresh. If both sources belong to the same trusted environment and may exchange data, set both to Organizational. Otherwise, keep them separate.
• Relationships aren’t affected. Loading timetoreply and another source as separate tables and relating them in Model view doesn’t involve privacy levels.
• Set it in both places. Desktop (Data source settings → Edit Permissions → Privacy Level) and the Service (Edit credentials) each have their own setting.

We haven’t tested merging with other sources ourselves. See Microsoft’s privacy levels and data privacy firewall guides.

What we haven’t tested

We tested this setup with Power BI Desktop and the Power BI Service, on a free Power BI licence in My workspace. We haven’t tested the following, so follow Microsoft’s documentation for them:

• Shared workspaces and paid capacity (Pro, Premium Per User, Premium or Fabric capacity). Publishing and sharing depend on your licence and the workspace capacity. See workspaces and licence types.
• Who can see or replace a saved credential in a shared workspace, and what happens when a semantic model’s owner changes. See data refresh in Power BI.
• Other clients: Excel, dataflows and Microsoft Fabric. Each uses Power Query and the same OData feed connector, but we haven’t tried them. See dataflows and Dataflow Gen2.

Microsoft’s own guides: publishing from Power BI Desktop and configuring scheduled refresh.

Need help or have feedback?

Email us at [email protected]. We’re happy to help.