Smartsheet is a cloud-based platform used to manage projects, tasks, workflows, schedules, and business information. When connected to Power BI, Smartsheet data can be transformed into interactive reports and dashboards that make it easier to monitor performance and identify trends.
Power BI can import data from Smartsheet and use it for analysis and visualization. This can be particularly useful for project tracking, task management, operational reporting, and performance monitoring.
Note: The current Smartsheet connector is provided by Smartsheet and is generally available for Power BI semantic models and Power BI dataflows. It supports the Smartsheet US and EU regions.
What You Need Before Connecting
Before starting, make sure you have:
- Power BI Desktop installed
- An active Smartsheet account
- Permission to access the Smartsheet sheets or workspaces you want to analyze
- Access to the required Smartsheet region
- The required Smartsheet data available for reporting
The current Smartsheet connector supports the US region and EU region.
Step 1: Open Power BI Desktop
Open Power BI Desktop.
From the Home tab, select:
Home → Get Data
The Get Data window displays the available connectors.
Step 2: Search for Smartsheet
In the Get Data window, search for:
Smartsheet
Select Smartsheet from the results and select:
Connect
Microsoft's current Power Query documentation lists Smartsheet as a supported connector for Power BI semantic models and Power BI dataflows.
Step 3: Select the Smartsheet Region
Power BI may ask you to select the Smartsheet region associated with your account.
Choose the appropriate region:
- US
- EU
The region should match the Smartsheet account you are connecting to.
Tip: If you are unsure which region your Smartsheet account uses, check the Smartsheet environment you normally use to sign in.
Step 4: Sign In to Smartsheet
After selecting the region, Power BI prompts you to authenticate.
Select Sign in and provide your Smartsheet account credentials.
The current Smartsheet Power Query connector uses Smartsheet account authentication.
After successfully signing in, select Connect.
Step 5: Select the Smartsheet Data
After authentication, Power BI displays the Smartsheet data that your account has permission to access.
Select the required sheet, folder, or workspace data.
Depending on your reporting requirement, you may work with information such as:
- Project names
- Task names
- Task owners
- Start dates
- Due dates
- Status
- Priority
- Completion percentage
- Project milestones
- Resource information
After selecting the required data, choose either:
Load
or
Transform Data
For most reporting scenarios, selecting Transform Data first is useful because it allows you to review and prepare the data before loading it into Power BI.
Step 6: Transform Smartsheet Data Using Power Query
Selecting Transform Data opens the Power Query Editor.
Power Query allows you to clean and prepare your Smartsheet data before creating visualizations.
Common transformation tasks include:
- Removing unnecessary columns
- Renaming columns
- Changing data types
- Filtering records
- Removing duplicate records
- Replacing blank values
- Splitting columns
- Combining data
- Creating custom columns
For example, if your Smartsheet contains project tracking information, you might keep fields such as:
- Project Name
- Task Name
- Assigned To
- Start Date
- Due Date
- Status
- Priority
- Completion %
- Department
Cleaning the data before loading it helps create a more consistent Power BI data model.
Related Offerings
Step 7: Apply the Data Transformations
After completing the required transformations, select:
Home → Close & Apply
Power BI applies the Power Query steps and loads the prepared Smartsheet data into the report.
Step 8: Create Relationships Between Data
If your Smartsheet reporting solution uses multiple data sources, create relationships between related tables or datasets.
For example:
Projects → Tasks → Employees
A well-designed data model helps Power BI correctly filter and analyze information across different datasets.
When creating relationships:
- Use compatible data types.
- Select the appropriate relationship type.
- Check for duplicate key values.
- Make sure the relationship reflects the actual business process.
- Avoid unnecessary relationships that may create ambiguous filtering.
Step 9: Create Your Power BI Report
Once the Smartsheet data has been loaded and modeled, you can create interactive Power BI visualizations.
For example, a project management report could include:
Project Overview
Display:
- Total Projects
- Active Projects
- Completed Projects
- Delayed Projects
Tasks by Status
Show how many tasks are:
- Not Started
- In Progress
- Completed
- On Hold
Tasks by Owner
Identify the number of tasks assigned to each team member.
Project Progress
Compare project completion percentages across projects.
Overdue Tasks
Identify tasks whose due dates have passed but remain incomplete.
Project Timeline
Analyze project activity across a selected date range.
Power BI allows you to combine cards, charts, tables, slicers, and other visualizations to create an interactive report.
Step 10: Publish the Report to Power BI Service
After completing the report in Power BI Desktop, select:
Home → Publish
Choose the appropriate Power BI workspace.
After publishing, the report and its semantic model can be accessed through Power BI Service according to the permissions configured for the workspace.
Step 11: Configure Data Refresh
Smartsheet data can change as users update projects, tasks, schedules, and other information.
To keep the Power BI report current, configure the appropriate refresh settings after publishing the report.
The Smartsheet connector supports Import, so the data is imported into the Power BI semantic model rather than being used as a live DirectQuery connection.
Tip: Choose a refresh schedule that matches how frequently the Smartsheet data changes and how current the report needs to be.
Common Issues When Connecting Power BI to Smartsheet
1. Smartsheet Is Not Available in Get Data
Make sure you are using a current version of Power BI Desktop and search for:
Smartsheet
in the Get Data window.
Microsoft currently lists the Smartsheet connector as generally available for Power BI semantic models.
2. Authentication Failed
If Power BI cannot connect to Smartsheet:
- Check your Smartsheet credentials.
- Make sure your Smartsheet account is active.
- Verify that you have permission to access the required data.
- Confirm that the correct Smartsheet region is selected.
The connector requires a Smartsheet account for authentication.
3. Required Data Is Not Available
If the required sheet or information does not appear:
- Confirm that the sheet exists.
- Verify your Smartsheet permissions.
- Make sure you are using the correct account.
- Check that the correct Smartsheet region has been selected.
4. Incorrect Region Selected
The current Smartsheet connector supports:
- Smartsheet US
- Smartsheet EU
If the wrong region is selected, the connection may not work as expected.
5. Data Does Not Look Correct in Power BI
If the imported data contains unexpected values:
- Review the Power Query transformations.
- Check the data types.
- Verify filters.
- Check for blank values.
- Review duplicate records.
- Confirm that the correct Smartsheet sheet was selected.
6. Refresh Is Not Working
If the report works in Power BI Desktop but does not refresh correctly after publishing:
- Review the data source credentials.
- Check the semantic model refresh settings.
- Verify that the Smartsheet account still has access to the required data.
- Check whether the source sheet has been modified or removed.
Best Practices for Power BI and Smartsheet
Start With the Reporting Requirement
Before importing Smartsheet data, identify what the report needs to answer.
For example:
- Which projects are currently active?
- Which tasks are overdue?
- Which team members have the highest workload?
- Which projects are approaching their deadlines?
- How much work has been completed?
This helps you select only the data required for the report.
Keep the Smartsheet Data Structured
Use clear column names and consistent values in Smartsheet.
For example, maintain consistent status values such as:
Not Started → In Progress → Completed
Avoid using multiple variations of the same status because inconsistent source data can make Power BI reporting more difficult.
Use Power Query for Data Preparation
Use Power Query to clean and transform the Smartsheet data before creating your visualizations.
This helps maintain a consistent dataset and makes the report easier to manage.
Load Only the Required Data
Avoid importing unnecessary columns and data.
A focused dataset can make the Power BI model easier to maintain and can reduce unnecessary processing.
Design Reports Around Business Questions
Instead of simply displaying every field available in Smartsheet, build visuals around questions users need to answer.
For example:
“Which projects are at risk?”
could be supported by:
- Project Status
- Completion %
- Due Date
- Overdue Days
This produces a more useful business report than simply displaying raw Smartsheet records.
Plan Your Refresh Strategy
Determine how frequently the Smartsheet data changes and how current your Power BI report needs to be.
Configure refresh settings accordingly so users can work with appropriately updated information.
Conclusion
Connecting Smartsheet to Power BI provides a practical way to turn project, task, and operational information into interactive business reports.
By using the Smartsheet connector, users can import the required data into Power BI, prepare it with Power Query, create a structured data model, and build visualizations that make project and operational information easier to understand.
The current Smartsheet connector is generally available for Power BI and supports Import using Smartsheet account authentication. It supports both the US and EU Smartsheet regions.
With a well-structured Smartsheet source and a focused Power BI data model, teams can turn day-to-day project and operational data into clear reports that support better planning, monitoring, and decision-making.
