Tag Archives: Power BI Desktop

Power BI Desktop Excel Connections

Using Excel Files with Power BI Desktop and SharePoint

As discussed in a previous post, Working with Excel Files in the Power BI Service, Excel and Power BI have a rich, complex relationship. Power BI Desktop is the primary design tool for Power BI, and it has many feature overlaps with Excel as an analytic tool. Excel can be used both as an analytic tool and a data source, and the structure of the Excel file will dictate the way that Power BI Desktop can be used with it. If Excel is being used as an analytic tool (i.e. connected to data), the appropriate items in the file can be imported into Power BI Desktop. If it is being used as a data source (data in worksheets), Power BI Desktop will connect to it, and use its data to build a model. This post attempts to articulate the nuances of both scenarios.

Importing from Excel

Power BI Desktop has an unfortunate name in my opinion. It is a design tool and is not meant to replicate the capabilities of the Power BI service on the desktop, as the name might suggest. A better name for the product would I believe be Power BI Designer. Its purpose is to connect to and transform data (Power Query), build data models for Analysis (Power Pivot) and build reports (report designer).

Used as an analysis tool, Excel has all these capabilities as well. In fact, the first two (Power Query and Power Pivot) are identical to what is already in Power BI Desktop. Excel also has Power View for analytic reporting. Power View is very similar to the type of reporting in Power BI Desktop, but uses a different technology and has been deprecated for some time. As a result, Excel charts and pivot tables are the primary means of visualizing data in Excel.

So why would you need to use Power BI Desktop if you are using Excel? As explained in my previous post, the Power BI service can fully interact with Excel as an analysis tool, and allows you to interact with Excel right from the Power BI Service. If Excel is meeting all your analytics needs, then there may be no need to introduce Power BI Desktop at all. However, if you wish to take advantage of Power BI’s analytic reporting capabilities, and you have existing Excel assets, you may wish to convert them to the native Power BI format.  Whatever the reason, moving from Excel to Power BI is relatively straightforward with Power BI Desktop.

From the File menu in Power BI Desktop, select Import, and then Excel Workbook Contents.

Importing Excel Files contents

You are then prompted to select an Excel file. Once selected, you are then presented with a warning dialog.

Excel files contents warning dialog

The dialog does a very good job of explaining what will happen, specifically the fact that data from workbooks will not be brought into this new file. Any Power Pivot data models or Power Queries will be brought in. If the workbook contains legacy Power View sheets, they will be converted to native Power BI visuals. In addition, any legacy (non-Power Query) data connections used by the source file’s Power Pivot data model will be converted to Power Query and imported.

Imported Power View sheet

Legacy Power View Sheet converted to Power BI visuals

A complete list of workbook content and what is/isn’t converted is below:

Excel Content Import to PBI Desktop Support
Data in sheets Not imported
Data model (Power Pivot) Imported
Data connections Converted to Power Query and imported
Power Queries Imported
Power View Sheets Converted to PBI visuals and imported
Pivot charts/tables Not imported
Excel charts Not imported
Macros Not imported

Once imported, the new Power BI file (PBIX) lives on its own and contains no connection or any other type of relationship to the original source Excel file. If the source Excel file is changed, there is no way to update the PBIX file. Any imported data connections are between the PBIX file and the original data source. The new PBIX file can be published to the Power BI service like any other.

Connecting to Excel

Connecting to Excel as a data source is a very different thing than importing from it. In this scenario, the data in the worksheets and only the data in the worksheets is brought into the data model. The is very different behaviour than that of connecting to Excel files to the Power BI Service, where both the model and the worksheet data is brought in.

Using the Excel Connector

The easiest, and most obvious way to connect to Excel worksheet data is by using the Excel connector. From the ribbon in Power BI Desktop, select Get Data. The Excel connector is right at the top of the list.

Connecting to Excel files on the file system

Selecting it allows you select your source file, and then the workbooks within it, and then build out the data model.

This approach works well but carries with it an important limitation. The new queries are  connected to the file using a local file system. This means that to be refreshed, an on-premises data gateway is required. In order to eliminate the gateway requirement, you can connect to the file in SharePoint using the SharePoint folder connector.

Using the SharePoint Folder Connector

The SharePoint Folder connects to all the files stored in libraries of a SharePoint site. It allows you to report on file metadata, but it also allows you to drill into file contents.

From Power BI Desktop select Get Data but instead of selecting Excel, Search for SharePoint and select SharePoint folder.

Using the SharePoint folder connector

Once selected, enter the URL of the SharePoint site (NOT the URL of a library or folder) in the dialog box.

Next, you will be presented with a preview of all the files in your site. Unless you are only interested in file metadata, click on the Edit button to bring up the Power Query editor.

The initial view will contain all the files in the site, but we are interested in the content of just one of those files. Every file in this view will contain the hyperlinked value “Binary” in the Content column. Clicking that link for the file that you want to connect to will drill down into the contents.

Site contents using the SharePoint folder connector

From this level, you can build your Power Query, data model, and report as needed just as if you had used the Excel connector. The difference is that now when you publish your report to Power BI, it will know the file is stored in SharePoint and will connect directly to it. It will not require a gateway for refresh purposes. Once credentials are registered, the report will refresh itself directly from the workbook stored in SharePoint.

XLS vs XLSX

A note of caution. The above SharePoint folder approach only works for XLSX files. The Power BI Desktop and the Power BI service both support both Excel file formats (XLS and XLSX). However, refresh does not. If the source file format is XLS, and a refresh is attempted, you will receive the classic “microsoft.ace.oledb.12.0 provider” error in the Power BI service.

Excel files refresh error with XLS file types

The older Excel file format (XLS) requires an Access driver to refresh, which is not a part of the Power BI service. The newer XLSX file does not require this driver. As a result, if the source file is XLS, refreshing it requires going through an On-Premises Data gateway, and that gateway machine must also have the ACE components installed.

To recap, you can bring Excel assets into Power BI Desktop by using the import function, and you can load data from Excel files through Power Query. The two operations have very different results, and the can be combined if a source workbook contains both analyses and data.

Using Power BI to Report on Person Fields in SharePoint

This post is the second in a series exploring Power BI and complex data types in SharePoint. The first post explores working with multi-value columns. In this one, we’ll explore some of the nuances of working with person fields

Person fields in SharePoint are just a special case of the lookup field, and the Power BI SharePoint list connector is aware of them. As such, it provides helpers to make it relatively easy to get the person’s name. However, more information is also available. We’ll examine three approaches to extracting this information. It is worth noting that all SharePoint lists contain person fields for “Created By” and “Modified By”, and they are always available.

The List

Consider the following list that contains a multi-value choice field named Amenities:

The view displays the person’s name, although the column is a complex data type. There is more information than just the person’s name available behind it but this is unavailable to the SharePoint view. Power BI can however access this information in reports. Report requirements will ultimately dictate the best approach to extracting this information, but the good news is that there are several to choose from. In all cases the data first needs to be brought into Power BI Desktop.

Loading the Data

We first launch Power BI Desktop, select “Get Data” and then choose SharePoint Online list (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). We are then prompted for the URL of the SharePoint Site. The dialog is titled SharePoint lists, but the value is actually the URL of the site, NOT the list itself. Once this is entered we are prompted for credentials if we haven’t connected to this site before. After entering credentials, we can select the list that we want to report on. In our case, it’s named “Listings”. We select it, and then click on the Edit button.

Once the data loads in, one of the first things that you’ll notice is that there are a lot of columns to choose from, and it’s a good idea to remove the data that you don’t need. In this case, we can remove the ContentTypeId column and everything to the right of it, with two important exceptions We want to keep the “FieldValuesAsText” and “Agent” columns (we’ll come back to that shortly). Remember, for our purposes here, we want to report on the person column, “Agent”. The simplest way to represent this data is with the person’s full name, as it is displayed in the SharePoint view. As noted above, it is also possible to use this data in a more sliceable, or structured way. Let’s start with the simplest.

Extracting the Full Name

One thing that you will notice right away is that he more simple column types like “Title” show their value directly in the Query editor. In our case, there are two fields related to Agent, the “AgentId” and “Agent” columns. The Agent ID column displays a number, and the “Agent” column displays a record data type. We will explore these columns, but if all we need is the user’s full name, we can use the highly useful “FieldValuesAsText” Column.

We scroll right and select the expander icon for the “FieldValuesAsText” column, then deselect all available fields except the Agent column.

We then select OK, and rename the column to “Agent Name”. The Full name of the Person is retrieved and used for the column. At this point, it’s ready to use in a report.

Linking to the User Information List

In many cases, the user name of the person may not be enough. As mentioned above, the Person Field is really just a lookup column that is automatically looking up data from a specific list. That list is the User Information list which is a hidden list that exists in the root site of every SharePoint site collection. This list gets populated automatically when the site collection is accessed. When Power BI loads a person column, it automatically creates a ColumnNameId column as well containing the ID value of the person field from this list. In our example, this is the “AgentId” column.

To leverage the data in this list it must first be loaded into the model. Following the same steps taken for loading the Listing data above, we select the “User Information List” which does get exposed to the Power BI Query editor. Once loaded, we remove all of the unnecessary columns from the query, being sure that we leave the ID column.

When ready, we select the “Close and Apply” button from the Query Editor Ribbon. At this point, we have two tables in the model, Listings and User Information List. We then select the relationship editor tab. The “AgentId” column in the Listings table is related to the “id” column un the User Information list table, and we establish this relationship by dragging one onto the other. Once established, we double click on the relationship line to set the value of “Cross filter direction” to “Both”.

We can now return to the design pane, add a table visual, and add columns from both tables. In such a way, we can show the agent’s name, email, phone, etc.

Expanding the Person Column

Although linking to the User Information List is powerful, and easier, and arguably better way to do the same thing is to use the automatically generated person column. This column is named the same as the original person column and contains a series of “Record” type values. The records in question are the corresponding records from the User Information List.

To access the data in this column, we click on the column expander and then select all of the columns that we will work with. Values from the related User Information List will be added to the table automatically.

This approach is clearly simpler than manually loading the entire User Information List, and only loads the records that are related. It will however likely result in a large amount of repeated data that the two table approach avoids. It is possible to achieve a two table solution with the person field using the technique outlined in my earlier article on working with multi-value fields, but the resultant table will still only contain related records. If it is necessary to show people regardless of whether or not there is a related record, then the manual approach is the only way.

Which approach is ultimately used will depend on the requirements of the report, but it is possible to reach deep into the person object in a SharePoint person field.

Using Power BI to Report on Multi-Value SharePoint Fields

Power BI has direct support for reporting on SharePoint lists and documents whether SharePoint is on-premises or in the cloud. Getting at your data is relatively straightforward if you know the URL, but as I’ve written about before, SharePoint data can be idiosyncratic. Text and number fields work right away, but more complex data types require a little more thought, and this is certainly true of multi value fields.

Multi value fields are really SharePoint’s way of approximating the behavior of a one-to-many relationship of a relational database. SharePoint can store list-based data, and even though it has the concept of a lookup field, it is not a relational database. Chances are however that if we’re reporting on this type of field, we want it to behave as though it were. Fortunately, Power BI gives us several options for doing this.

The List

Consider the following list that contains a multi-value choice field named Amenities:

In the view, the different values are flattened, and displayed as a single value using a comma to separate them. How do we go about reporting on this data in Power BI? There’s no right answer, as it depends on the report requirements, but there are several options. In all cases the data first needs to be brought into Power BI Desktop.

Loading the Data

We first launch Power BI Desktop, select “Get Data” and then choose SharePoint Online list (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). We are then prompted for the URL of the SharePoint Site. The dialog is titled SharePoint lists, but the value is actually the URL of the site, NOT the list itself. Once this is entered we are prompted for credentials if we haven’t connected to this site before. After entering credentials, we can select the list that we want to report on. In our case, it’s named “Listings”. We select it, and then click on the Edit button.

Once the data loads in, one of the first things that you’ll notice is that there are a lot of columns to choose from, and it’s a good idea to remove the data that you don’t need. In this case, we can remove the ContentTypeId column and everything to the right of it, with one important exception We want to keep the FieldValuesAsText column (we’ll come back to that shortly). Remember, for our purposes here, we want to report on the multi-value column, Amenities. The simplest way to represent this data is as a string of comma separated values, which is the same way it is shown in a SharePoint view. It is also possible to use this data in a more sliceable, or structured way. Let’s start with the simplest.

Extracting a Simple Text Field

If our report is to show all the amenities associated with a listing, then a simple text string will do the job. Examining the data in the Query Editor, you will notice that there are no values displayed for Amenity, the way that there is for simpler field types like “Sold” or “Asking Price”. Instead, each cell contains a “List Value”. Each of these Lists contains the Amenities values for each record and clicking on one will extract those values for that one item. However, of we want to extract the values for all items, so we need to expand the column by clicking on the expand icon in the column header.

We are then given two options, “Expand to New Rows” or “Extract Value”. To extract the values into a single value, we select “Extract Values”

We are then given the opportunity to choose a delimiter, and after that the values in the column are replace with simple text values.

Another way to accomplish this same thing is to use the FieldValuesAsText column (referenced above). The fields in this column contain a single record containing the textual values for all the complex data types in a SharePoint list. If you are doing reporting with SharePoint data in Power BI, FieldValuesAsText is your friend. To use it, we just click the expand icon in its column header, deselect all of the columns that we don’t need (which in our case is everything except Amenities) and click OK. We are returned the contents of the list in a comma separated string for each item.

At this point, we can report on the data.

We click on Close and Apply in the ribbon to return to the report canvas. We add a table visual, and add some of the fields like address, street number, and Amenities to it. You’ll see that the table looks much like it would in a SharePoint list. Next, we add a slicer to the report page, and use one of the other columns, like City. Clicking on a city will filter the table by that city as expected. Now, if we want to filter by Amenity, we can add the Amenity column to the slicer, but it won’t behave the way we want it to. Each combination of amenity values will be represented as a single value. We are unable to slice on a single value like “Garage”.

Flattened multi value field as slicer

To use discrete values for our multi-value field, we need to do a little more work in the Get Data (Power Query) interface.

Extracting Discrete Values from a Multi-Value Field

We follow the initial steps from the “Simple Text Value” section above, but when the expand icon is selected, we select “Expand to new rows” and NOT “Extract values”. When this is done, each row will be copied to accommodate all of the individual Amenity values. If there were 3 Amenity values, we wind up with three rows of identical values, but with each of the values for Amenity.

Expanding to new rows

We can now go to the design surface and add a table, and a slicer that uses the multi value column, and now we can filter on discrete values. This may be sufficient in many cases. However, if we create a measure that based on an aggregate value for the table (like COUNTROWS or SUM), the value will not be correct if there are no slicer values. It works with all of the duplicated rows.

Incorrect number of listings

Again, this approach may work in many situations, but for ultimate flexibility, we need a true one-to-many relationship. Luckily, Power Query gives us a few simple tools to do this.

Creating a One-to-Many Relationship

Again, we follow the initial steps from the “Simple Text Value” section above, but do not click on the expand icon in the Amenities column. A One-to-many relationship requires at least two tables, so now is the point where we need to add a second one. We do so by copying the first. We right click on the query in the Queries pane and select “Duplicate”.

This creates a second query identical to our first, which will load into another table. We can then give the new query a better name, like “Amenities” in our case. Next, we select the Id, and the Amenities columns, right click and select “Remove Other Columns”, and we are left with only the ID and Amenities columns. Now we select the expand icon and select “Expand to New Rows”.

We now go back to the original query and remove the Amenities column. The result is two tables, Listings and Amenities, with Listings containing the bulk of the information. We then click on Close and Apply in the ribbon to load the data into the model, and the relationships icon to take is to the relationship builder. We need to establish a relationship between the two tables, and we do so by clicking on the Id field in one table and dragging it to the Id field of the other. Finally, double click on the relationship line between the tables, and be sure to select “Both” for Cross Filter Direction – that way Amenity values can filter List values. If “Both” is selected, the directional arrow will point two ways.

Now, the same visuals from above can be added using the Amenity column from the second table as a slicer, and everything should work as expected. Listings will be filtered by the value, if no amenities are selected, the correct number of listings will be shown In the data card, and no duplication will appear in the table.

SharePoint multi-value columns are an attempt to replicate the capability of a relational database. With a few simple steps, Power BI can take SharePoint data, normalize it, and analyze it as if it were a true relational database.

Get Power BI Desktop from the Microsoft Store

If you’re a Power BI Desktop user, you are likely familiar with the monthly update cadence. Once per month, a new Power BI Desktop is released, and normally it contains at least one new feature that you WILL want to use. In case you miss it, a little indicator will light up in the bottom right hand corner that will prompt you to update. Of course, you normally don’t notice this until you’re in the middle of designing something, and it’s never a good time.

Then you do click on the prompt, you’re take to a web page from which to download PBI Desktop. Doing so can take a few minutes (it’s relatively large), and when it starts, more often than not, you’ve forgotten to exit the current PBI Desktop. Once you do, you can run the installer and then you’re up to date. For 30 days. While I think that most of us welcome the new features, this update process itself is cumbersome. Thankfully, the availability of Power BI Desktop in the Microsoft store removes this pain.

Power BI Desktop – Microsoft Store Edition

Getting Power BI Desktop from the Windows store allows you to have it automatically updated in the background whenever new versions are available, just like any other app in the store. To be sure, it is not a separate “Microsoft Store Edition” of the product, but precisely the same product in a Windows Centennial (presumably) wrapper.

Installing Power BI Desktop from the Store

Before installing the store version of Power BI Desktop, it’s a good idea to uninstall the older version. This is not required, and the two can operate side by side, but this will most likely be confusing. The non-store version is uninstalled by opening “Programs and Features” in Control Panel and uninstalling from there. As an aside, the store version will not appear in this list – like other apps, it will only appear in the start menu, and if necessary can be uninstalled by right-clicking on the tile.

To install PBI Desktop, open the store and search for Power BI. At the moment, PBI Desktop does not appear in the search dropdown, but the Power BI app does. The app is not what you’re looking for here. Execute the search and you should see two results, Microsoft Power BI Desktop, and Microsoft Power BI. The icons for the two are very similar (but not identical!). Be sure that you select he one with “Desktop”.

Installing Power BI Desktop from the Microsoft Store

The power BI app is the Windows 10 app for Power BI and is designed for a mobile experience. It is like the versions found on iOS and Android. Selecting the Desktop tile should download and install Power BI Desktop.

Copy Your Saved Settings

One of the downsides of this new isolated model is that all the stored credentials, cached data and saved settings from your prior versions of Power BI desktop do not come forward into the store version. However, the good news is that you can bring them forward manually. To do so, navigate to your AppData folder for Power BI, likely at:

C:\Users\[YourUserName]\AppData\Local\Microsoft\Power BI Desktop

Copy everything in this folder (including subfolders) and copy it into:

C:\Users\[YourUserName]\AppData\Local\Packages\Microsoft.MicrosoftPowerBIDesktop_8wekyb3d8bbwe\LocalCache\Local\Microsoft\Power BI Desktop Store App\

The latter is simply the isolated store app version of the former. This content can be relatively large, so it’s a good idea to delete it from the source once finished. Uninstalling the original application does not clean up this content – in fact in my case, as advised above, I uninstalled it prior to the installation of the store app itself.

Once you’re up and running with the store version, you should never need to worry about manually updating Power BI Desktop again.

A Simplified Method of Working with SharePoint Data in Power BI

Although I typically advise against it, there are valid reasons to report on SharePoint list data directly. Power BI Desktop makes this data quite easy to access – you can use the built in connectors for SharePoint or SharePoint Online, or, due to the fact that any SharePoint list is available via OData, you can also use the OData data connector. Microsoft has recently made improvements to both methods, but the SharePoint connectors bring some significant usability advantages. One of these advantages is what I’m calling “summary columns”.

The Problem

Consider the following SharePoint list with different field types:

Connecting to this list with Power BI desktop and editing the query returns all of the list fields regardless of their visibility in the user interface. Assuming that we want to work with the above field values for analysis purposes, we can discard the fields that we don’t need, and reorder the remaining, leaving us with only these fields.

However, you can immediately see that we’ll need to do some work in order to get our data in a usable format.

The title field is simple enough – it’s value is immediately available, no issues there, and no changes are necessary. From the Name field, several columns are returned. Two are the ID of the name in the site collection’s user list. It would be possible to connect to the root of the site collection, retrieve the user list, and establish a relationship between the tables, but clicking the expand icon will allow Power Query to do a lookup for each ID and return the desired attribute, in this case, Name.

The same is true for the lookup field type – the column can be expanded in order to include any attribute of the source item. The field using managed metadata works in a similar fashion; it can be expanded in order to retrieve the text value of the managed metadata item. Link fields work the same way – the can be split into two columns, the link itself and the description. However, the field containing multiple values is a little different. The great news here is that it’s possible – previous versions of Power Query couldn’t work with multi value fields, but now the SharePoint data source supports it.

Multiple value field values are returned as a list. With list items, the expand icon will duplicate the entire record for each value on the list. This may or may not be the desired behaviour, but remember, this is Power BI. Everything can be aggregated.

Before After

The conclusion to be drawn here is that in order to represent SharePoint list data that is using any sort of control more complex than text or number, we need to do some work. However, the good news is that someone (I’m not sure if it’s the Office Team of the Power BI team) has added a feature that makes this whole process much simpler.

The Solution

After connecting to your SharePoint list, edit the query. Instead of diving in and performing all of these manual transforms, select your multi value column(s) if you have any (this will make more sense momentarily). Select any rich text columns as well, and then scroll right to find a column named “FieldValuesAsText”. Select this column, then right click on it and select “Remove Other Columns”.

The FieldValuesAsText column is our magic bullet. It automatically converts most (rich text fields being the exception) of the more complex SharePoint data types to simple text that work well within Power BI. Simple click on its expand button, select the columns that you want to include in your analysis. I find it useful to deselect “Use original column name as prefix” as well. We are left with textual representations of our field data.

You will notice that the multiple value fields here have their values separated by commas. For multiple values, I tend to prefer the “raw” approach, which is why we retained the multiple value column above. We can still expand it and create a separate line for each value, and remove the column created by “FieldValuesAsText”.

Finally, you may have noted that the Rich Text field isn’t automatically converted. In order to extract useful text from it, we still need to use Power Queries transformation functions such as Replace Value, Trim, and Clean.

In a nutshell, if you’ve been frustrated by formatting or data type limitations when using SharePoint data in Power BI, have another look, and check out the FieldValuesAsText column. It will make everything a lot simpler.