How to add a SharePoint file as a data source to a Power BI Dataset using Tabular Editor
A guide to adding non-SQL based data sources to a Power BI Dataset using Tabular Editor
Tabular Editor is a widely recognized tool within the Power BI community, renowned for its impressive data modeling capabilities, DAX editor, and advanced functionalities. When it comes to adding a new table from traditional SQL database-based data sources in Tabular Editor, the process is relatively straightforward. However, incorporating data from non-SQL sources like SharePoint requires additional effort and considerations.
This article aims to demonstrate the process of importing an Excel file hosted on SharePoint as a data source in a Power BI dataset using Tabular Editor.
Please read our terms of use before proceeding with this article: https://dhyanintech.medium.com/disclaimer-disclosure-terms-of-use-fb3bfbd1e0e5
Before we get started, it’s important to point out that if you can use Power BI Desktop to add new data sources to your dataset, then do so. Why? Because it is much more straightforward and user-friendly, and you can apply Power Query actions with immediate results. Tabular Editor should only be used if the Power BI Desktop can’t work for you. Let’s look at a few scenarios where you can’t use Power BI Desktop.
- You don’t have a PBIX file of your data model available
- You can’t download the dataset from the Power BI Service because the download is not allowed due to previous XMLA write operations on the dataset
Prerequisites
Tabular Editor uses the XMLA endpoint to connect to the dataset in the Power BI service. XMLA endpoint is a premium-only feature in Power BI. Therefore, the dataset should be hosted in a Premium workspace with XMLA write operations enabled. In addition, we need
- XMLA endpoint for the workspace
- Build permission for the target dataset
- SharePoint URL and access to the file
- Power BI Desktop
- Tabular Editor (both v2 and v3 should work)
To demonstrate the steps and the process, we will add an Excel stored in a SharePoint folder containing the following country information to a Power BI dataset hosted in a Premium P capacity.
Start in Power BI Desktop
Open Power BI Desktop and create a new PBIX file. We will use this PBIX to connect to the SharePoint folder, add the Excel, and clean and transform using Power Query. Proceed to load the file and perform the required cleaning and transformation steps in the Power Query. Refer to the following link if you need guidance on connecting to SharePoint and loading the Excel file.
Our goal is to obtain the M code from the Power Query Editor. The M Code contains the connection details and the steps to clean and transform the Excel data. You don’t have to save the PBIX file but keep it open until we finish all the steps.
Continue in Tabular Editor
Open Tabular Editor and connect to the dataset in the Power BI Service. Follow the following steps if you’re new to Tabular Editor:
- Select File > Open > Model from DB
- In the Load Model from Database prompt, enter the XMLA endpoint of the Power BI workspace as Server address, select Integrated as Authentication, select Read/Write as the Mode and click OK.
- Follow the prompts to authenticate yourself.
- In the Choose database prompt, select the target dataset to which you want to add the new table.
We recommend backing up your dataset or performing the steps in a replicated copy of your dataset. So you have an intact copy in case things go wrong.
Wait for the Tabular Editor to connect to your dataset and load the TOM Explorer. Next, we will add a new table to the model. Follow these steps:
- Select Tables or Model > Add Table. A “New Table” will be added to your model and visible in the Tom Explorer under Model > Tables.
- Give a proper name to the “New Table” and expand the arrows until you reach the lowest level, i.e., table name > Partitions > partition name. Double-click on the partition name to open the Expression Editor or select the partition name, then select View > Expression Editor.
Now is a good time to copy the M code we created from the Power BI Desktop. Follow these steps:
- Paste the M code in the Expression Editor.
- Save your changes: saving the changes writes the changes back to the model in the Power BI workspace.
M code only contains the data source connection details, not the authentication credentials.
Configure in Power BI Service
Next, we must supply our credentials for the SharePoint source we just added. Head over to the workspace hosting your dataset in the Power BI Service. Go to the dataset settings and ensure that Data source credentials and Privacy level are set correctly for SharePoint.
Follow the instructions and prompts after selecting Sign in to set your credentials. When done, switch back to Tabular Editor.
Finish in Tabular Editor
Next, we need to add the columns from the new table and verify that the data can refresh as expected. If you encounter any errors during these steps, then scroll over to the troubleshooting section at the end of the article.
- Right-click the table or the partition name and select Update table schema…
- In the Apply Schema Changes prompt, click OK. Notice the summary of the table and columns that will be added to the model. Wait for the operation to complete, and then we can see the columns under our new table. This is an important step when the Tabular Editor adds the metadata of the columns to the dataset.
We must refresh the table before using it to define relationships with other tables or write DAX measures based on it. We don’t have to refresh the entire model. Refreshing the new table is sufficient. Save the model and right-click on the table name > Refresh > Full refresh (table) to refresh the table.
Here’s another method to refresh selected tables without using Tabular Editor
Wait for the refresh to finish, and let’s look at the data loaded to our model. Right-click on the table name and select Preview data.
Looks good. Proceed to build relationships and DAX measures as required, and we’re done.
Troubleshooting
- Did you run into a
DatasourceHasNoCredentialErrorwhile trying to update the table schema or preview the data?
It’s one of the most common errors while adding new tables in Tabular Editor. This error is caused by missing data source credentials in the dataset hosted in the Power BI workspace. Without the credentials, the dataset can’t connect to the data source to show the table structure and the data. To resolve this error, ensure the credentials are set correctly under the dataset settings in the Power BI Service.
- Are you trying to merge or combine data from multiple data sources in Power Query (M code) and getting errors while trying to update the table schema?
They are common errors while combining data from multiple sources in Power Query and are caused by Power Query’s Firewall. Resolving Firewall errors in Power BI Dataset is relatively more straightforward than Tabular Editor and requires additional steps. Refer to the following article for detailed steps and instructions on how to get around this error.
Coming Soon…
Conclusion
In this article, we demonstrated how to add SharePoint and other non-SQL-based data sources in Power BI models using Tabular Editor. We also presented insights into typical errors and effective solutions to address them.
Further Reading
While setting up the credentials in Power BI Service, we also set a privacy level for the data source. Data privacy levels are crucial security measures, preventing data from being exposed beyond trusted boundaries. Privacy levels also affect the data mashup operations in Power Query and the Firewall. We have an amazing article on privacy levels and their interaction with the Firewall.
👉 Don’t forget to subscribe to our Power BI publication and Weekly Newsletter…
