Summary: How to setup a PowerBI Report and Dashboard (Power Automate).
Date: Around 2020
Refactor: 1 March 2025: Checked links and formatting.

Goal: Make a dashboard of “What's going on at Infra”. Sources are a rolling planning sheet and TOPdesk.

As any product, PowerBI has limitations that influence the way you work with it. Note the following remarks and make your choices:

• Reports can be embedded or published to other applications or the web. Dashboards can not.
• If you deploy a report to the web, it is viewable by the world. Viewers do not need a license.
• Reports and dashboards created in your personal workspace cannot be published to a different workspace.
• Reports created in PowerBI Desktop can be published to a shared workspace.
• Reports created in PowerBI Desktop and published to a workspace can be downloaded as a pbix file (preview function - requirements in place)
• Reports and dashboards can be shared with other people, but they need a license and can't edit it.

Based on these limitations you should make decisions on:

• Make a report or a dashboard
• Make it in PowerBI Desktop or directly in the PowerBI Pro service
• Publish or create in a a personal workspace or a shared workspace

• Name: RollingPlanning.xlsx
• Location: SharePoint Online Teamsite

Note that because the file is on SharePoint Online we do not need a connector for this source and the data is automatically updated.

Columns:

• Name (Required)
  • Keep the name short as it might not display entirely in the PowerBI dashboard.
• Description
  • This is a short description in case you used a non-understandable name. It is only used here in the sheet.
• TimeEstimation (Required)
  • Estimated time for the work to be done in hours. Don't change the TimeEstimation when the work is in progress.
• TimeRealization
  • Time spent on the work.
• ActivityType (Required)
  • Project: Official projects that require PSA, resources from multiple teams, etc. Project Initiation can be Infra or any other team.
  • Improvements: Non-official projects that are inititiated and fully managed by the Infra team. These improvements do not reoccur on a yearly basis.
  • Maintenance: Yearly recurring updates and upgrades of products that are managed and maintained by the Infra team and are already in the product catalog.
  • Run: Tasks that are never ending and occur on a frequent basis or unplanned throughout the entire year.
• Startyear (Required for projects and improvements)
  • The year the work is planned for, and eventually started in. Don't change the startyear when the work is in progress.
• UnforSeenIn
  • The year the work is taking place but is unforseen from a yearplan perspective (this assumes you have a clear work intake process so new work can be forseen)
• Status (required)
  • Run: For activitytype run
  • 01-Request: For work that is being considered for the backlog. Characteristic is the unclarity. Is it inline with regulation and guidelines. Is the (business)owner and the objective (inclusing value and priority) clear?
  • 02-Backlog: The accepted work that needs to be done. Time is roughly estimated. Only the overall objectives are clear.
  • 03-Ready: The refined work. Can be sliced into multiple workitems (multiple changes and or multiple change activities). Time is estimated through team effort and the entire team understands what needs to be done. It is ready to start working on.
  • 04-Progress. The work that is being worked on.
  • 05-Done. The work that has been done. The delivered output is accepted by the requester and the Infra team. Acceptance criteria are met, documentation and other administrative tasks are done.

Columns:

• Name (Required)
  • Distinctive name of employee
• External
  • Is the employee external
• Startyear
  • The year the external hours are planned for. If the external employee is (again) available in a new year a new line should be made.
• Hours (Required)
  • The number of hours the employee is available. An FTE should be considered as 1768 hours in 2019 (365 days, 104 weekend days, 10 public holiday days, 30 holiday days)

Add a column (and PowerBI visuals) for RealizedHours if you want to know about the effect of overtime, trainig and sickness

For PowerBI, the data needs to be formatted:

• Select a cell, and go to Insert → Table. A table is automatically created depending on your data range.

Go to the workspace where you want to create the report and follow these steps:

• Click on Get Data → Add File → SharePoint Online Teamsite
• Provide the root url of the teamsite, for example: https://getshifting.sharepoint.com/teams/ICT/Infrastructure
• You can now browse to the excel file you want to add

You do not need to set a data refresh schedule as the data is automatically refreshed from SharePoint Online.

Some testing showed that the data is indeed updated, but the “Refreshed” timestamp in the dataset overview of the workspace is not.

TOPdesk holds all it's information in a database. The datadict function (Help > DataDict (must be enabled per user)) grants insight into the relationship between what you see on the screen and the underlying tables.

I also found through testing the following useful information:
Currentphase:

• 2: Waiting for approval & On Hold
• 3: Rejected
• 5: In progress
• 7: Closed

Status:

• 1 : “Real work”
• 2: Approvals

You need to follow these steps to get your TOPdesk data visualized in PowerBI:

• Add SQL data in PowerBI Desktop
• Create and publish a report to PowerBI Pro workspace
• Install PowerBI Data Gateway on a server
• Add SQL Dataset in PowerBi Pro Data Gateway
• Setup a data refresh schedule
• Change the PowerBI report to use the dataset from the Data Gateway

• In Power BI Desktop, on the Home tab, select Get Data → SQL Server.
• Under Server enter your sql server name, and under Database enter the database name
• Verify your credentials, then select Connect.

Creating all the visuals are explained below, but after you've created the report click Publish and select the workspace you want to publish the report to.

Note that this will also work without a PowerBI Data Gateway. You'll need the data gateway to schedule an automatic data refresh

Install On Premises Data Gateway on a server:

• Installation directory: D:\Program Files\On-premises data gateway
• Email address to use with this gateway: an email address that is checked on regular bases. Don't know yet what is is used for, but is does not need to be the name of the installation account
• Installation/Login account: Service account with a Office 365 license. This account becomes automatically an administrator of the gateway.
• Name: ICT Infrastructure Gateway
• Recovery Key: Save it somewhere safe.
• Region: North Europe

When the installation is finished you need to login to the PowerBI pro service using the account you installed it with. Go to Settings → Manage Gateway → Add administrators so you can add the SQL dataset using your own account.

Settings can be changed after the installation by starting the “On-Premises data gateway” application. Here you'll find options for logging, restarting, test connectivity, etc

See here for more information on installing the PowerBI Data Gateway.

Login to the PowerBI Pro service using an account that has administrative access to the PowerBI Data Gateway and go to the workspace where you'll use the data source. Follow these steps to add a SQL data source:

• Go to Gateway → Add data source
• Data Source Type: SQL Server
• Fill in the SQL Server and database information
• Authentication method: Basic for SQL Authentication
• Fill ib the credentials
• Click Add

After the datasource is successfully connected go to the Users tab of the data source to add users that can use the data source by filling in their email address.

See here for more information on adding a SQL data source.

Go to the workspace where you've added the datasource and go to datasets and click the schedule refresh icon of the dataset you want to configure for automatic refresh. You can add multiple times for a schedule.

Still in the workspace where you've added the datasource, and still in the settings of that datasource, go to Gateway connection and enable the “Use a data gateway” slider. Apply your changes.

Note that when defining the titles in the visuals of a report, they can be changes in the pinned visuals in the dashboard, and after pinning, these titles are not synchronized.
Note that you can change the display name of a field. When I did that I try to show the original name in the fields section.
Note the Page level filter on the excel visuals is set to 2019 or blank. This filter will include all reoccuring maintenance and run tasks and the improvements and projects of 2019. If you would duplicate the report page you can change this filter to 2020 and you have all the visuals for the new year (just modify the titles of the visuals).

Note that this looks at the secondline operator instead of the operator because after the incident is solved it gets deescalated for verification by the servicedesk.

Also enable the Data Labels toggle to show the number of tasks per phase.

For every visual you want to include click the visual on the Pin, and select the dashboard you want to show the visual on. After adding all the visuals you need to:

• Give appropriate titles per visual
• Rearrange them the way you want them

Provided you did everything as I wrote down, this could be your result: