Business Intelligence - BI, Excel, Power BI, Power BI Desktop, Power Query, Self-Service BI

Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

Microsoft Excel is one of the most common data sources for Power BI. We can store Excel files in various storage types. The way we get data from Excel varies depending on the storage type. In this post, I quickly show two methods to connect to an Excel file stored in SharePoint Online.

Method 1: Getting the Excel File Path from the Excel Desktop App

This method requires you to have the Excel application installed on your machine. In this method, we open the Excel files stored in SharePoint Online in the Excel Desktop App in our machine and get the file path from there.

In SharePoint Online go to the desired document library then follow these steps to make it work:

  1. Select the Excel file
  2. Click the Open button
  3. Click Open in app
Opening Excel file from SharePoint Online in Excel Desktop App
Opening Excel file from SharePoint Online in Excel Desktop App

This opens the Excel file in the Excel Desktop application. In the Excel follow these steps:

  1. Click the File menu
  2. Click Info
  3. Click the Copy path button
Copying Excel Path from Excel Desktop App
Copying Excel Path from Excel Desktop App

So far we got the Excel file path. The step is to get data from the copied path in Power BI Desktop.

Open Power BI Desktop and follow these steps:

  1. Click Get data
  2. Click Web
Getting data from Excel stored in SharePoint Online in Power BI Desktop
Getting data from Excel stored in SharePoint Online in Power BI Desktop
  1. Paste the path we copied from Excel in the URL text box
  2. Delete the ?web=1 from the end of the copied path
  3. Click OK
Modifying the path copied from Excel to get the data in Power BI Desktop
Modifying the path copied from Excel to get the data in Power BI Desktop
Continue reading “Quick Tips: Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop” →
Business Intelligence - BI, Power BI, Power BI Desktop, Power BI Exporter

Exporting Power BI Desktop Data Using Power BI Exporter

Exporting Power BI Desktop Data Using Power BI Exporter
Exporting Power BI Desktop Data Using Power BI Exporter

I am excited to announce that our amazing team at Data Vizioner has developed a lightweight and easy to use external tool for Power BI Desktop named Power BI Exporter. The Power BI Exporter is a free community tool available for download here. With External Tools General Availability, you can expect to see more and more External Tools built by our amazing community members. I personally stunned to see many amazing ideas turn into useful tools. Learn more about Power BI announcements about External Tools GA here. I am humbled to be named in the announcement though. So thank you to Microsoft for that.

Let me start with a little background. In the past few years, I wrote a series of blog posts about exporting data from Power BI Desktop and Power BI Service. Those posts are quite popular. They are in the top 10 most visited posts on my website, contributing to about 15% of my website visitors. Here are the previous posts I wrote on this specific topic:

Apart from my website statistics showing many people want to export data from Power BI Desktop, some of my customers asked the same question. They wanted to export the curated data from their data model within Power BI Desktop to CSV format and make the curated data available for their other platforms. While all the methods mentioned in my previous blog posts are working, some users still find them complex. So I thought, we can make it better. We can make a straightforward tool that exports the data with only two clicks. So we started building the Power BI Exporter as a micro-project. We added some more ideas to the original idea of only exporting the data. We thought it is good to export the data along with the table names, column names and relationships. Having that information handy, we can quickly build the same data model as the one we exported its data but using the CSV files as the data sources. The other idea was to pack everything in a ZIP file on the fly, so we have a single ZIP file, including the tables, columns, and relationships. As a result, the first version of the Power BI Exporter is born. In this post I explain how it works.

Downloading and Installing Power BI Exported

You can download Power BI Exporter from its official webpage from Data Vizioner website. You require to enter your email address then click the Download button as shown in the following image:

Downloading Power BI Exporter from Data Vizioner Website
Downloading Power BI Exporter from Data Vizioner Website
Continue reading “Exporting Power BI Desktop Data Using Power BI Exporter” →
Business Intelligence - BI, Dataflows, Excel, Power BI, Power BI Desktop, Power BI Desktop RS, Power BI Service, Power Query, Self-Service BI

Quick Tips: Renaming All Tables’ Columns in One Go in Power Query

Renaming All Tables' Columns in One Go in Power Query

I previously wrote a blog post explaining how to rename all columns in a table in one go with Power Query. One of my visitors raised a question in the comments about the possibility to rename all columns from all tables in one go. Interestingly enough, one of my customers had a similar requirement. So I thought it is good to write a Quick Tip explaining how to meet the requirement.

The Problem

You are connecting to the data sources from Power BI Desktop (or Excel or Data Flows). The columns of the source tables are not user friendly, so you require to rename all columns. You already know how to rename all columns of a table in one go but you’d like to apply the renaming columns patterns to all tables.

The Solution

The solution is quite simple. We require to connect to the source, but we do not navigate to any tables straight away. In my case, my source table is an on-premises SQL Server. So I connect to the SQL Server instance using the Sql.Database(Server, DB) function in Power Query where the Server and the DB are query parameters. Read more about query parameters here. The results would like the following image:

The Results of Sql.Database() Function in Power Query
The results of running the Sql.Database(Server, DB) function

As you see in the above image, the results include Tables, Views and Functions. We are not interested in Functions therefore we just filter them out. The following image shows the results after applying the filter:

Filtering out SQL Server Functions After Connecting from Power Query
Filtering out SQL Server Functions

If we look closer to the Data column, we see that the column is indeed a Structured Column. The structured values of the Data column are Table values. If we click on a cell (not on the Table value of the cell), we can see the actual underlying data, as shown in the following image:

Continue reading “Quick Tips: Renaming All Tables’ Columns in One Go in Power Query” →
Business Intelligence - BI, Power BI, Power BI Desktop, Quick Tips, Self-Service BI, SQL Server Management Studio - SSMS

Quick Tips: Registering SQL Server Profiler as an External Tool in Power BI Desktop

Registering SQL Server Profiler as an External Tool

It has been a long time that I use SQL Server Profiler to diagnose my data models in the Power BI Desktop. I wrote a blog post in June 2016 about connecting to the underlying Power BI Desktop model from different tools, including SQL Server Management Studio (SSMS), Excel and SQL Server Profiler. In this quick post, I share a pbitool.json file that you can use to register the SQL Server Profiler as an external tool. Read more about how to register an external tool here. This is quite handy as this way to use SQL Server Profiler to diagnose Power BI Desktop without needing to find the diagnostic port. As an external tool, the SQL Server Profiler automatically connects to the data model via the diagnostic port. You can download the sqlserverprofiler.pbitool.json file from here. After you download the file you can open it in a text editor to see or modify the JSON code. If you are using SSMS 18, then you do not even need to modify the file. If you use a different version, the only thing you have to change is the “path”.

The contents of the sqlserverprofiler.pbitool.json file
The contents of the sqlserverprofiler.pbitool.json file
Continue reading “Quick Tips: Registering SQL Server Profiler as an External Tool in Power BI Desktop” →