Category Archives: SharePoint

Use Power BI dataflows to warehouse SharePoint list data

Image result for data warehouse

Reporting on SharePoint data has been a requirement for a long time, and there have been many approaches to fulfill this need. Custom web parts, Data View web parts and SSRS direct connected reports have historically been some of the solutions, but they all suffer from the same problem. If you have any serious amount of SharePoint data, you’ll quickly begin to bump into capacity limits and performance limitations, and in some cases, you can impact the performance of the overall system. In order to avoid this problem, it is necessary to warehouse SharePoint data first, as I argued in this post from 2012.

Once your list-based data is in a relational database, the performance issue is taken care of. However, the means of getting it moved there have traditionally been problematic. For a long time, there was a CodePlex project called the SharePoint List Source and Destination. This solution provided read and write access to SharePoint lists from SQL Server Integration Services (SSIS). Unfortunately, it was last updated in 2012, it was unsupported by Microsoft, and it did not support authentication for Office 365. This of course rendered it useless for use with SharePoint Online. In 2015, SQL Server Integration Services got an OData source, and given that SharePoint lists have OData endpoints, this became a viable option, particularly given that it did support Office 365 authentication. The OData connection from SharePoint did however have some limitations as well.

For cloud scenarios, Power BI has emerged as a very competent way of reporting against SharePoint data. It has native connectors for SharePoint list data, both on premises and in the cloud and Power BI reports can be hosted in the cloud through the SharePoint Power BI web part. On premises, the same can be done with Power BI Report Server. The structure of Power BI reports mean that the data is cached in a data model, so reports are not run directly against the list data source. This avoids the performance issues listed earlier.

Earlier this year I published a series of articles detailing how to do exactly this. The only issue with this approach is that the data shaping and preparation is always specific to a single report. If I have 5 different reports that use one list, I must query and shape that data 5 different times – one for each report. This is where Power BI dataflows come in.

In this context, dataflows are essentially a data warehousing layer with transformation capability. Instead of each report connecting back to a source list, the dataflow connects to the list, shapes the data with Power Query online and stores it in a data lake. The Power BI reports then connect to the dataflow as their data source. Transformation and storage only need to happen once.

As of this writing, dataflows are in public preview, so be warned – some things could change.

Creating a dataflow

Creating a dataflow from a SharePoint list is relatively straightforward. In our examples below, we will work with the same sample list from the series of articles on SharePoint data earlier this year. To begin open Power BI and navigate to a workspace (your personal workspace will not have dataflows). Click on the workspace name in the navigation pane and the dataflows tab should be available.

To create a new dataflow, Select the Create button, and click dataflow.

Select the Add new entities button and the data source selection will appear. SharePoint list and SharePoint online list are both options. SharePoint list is for on premises list data which will work with the On-Premises Data Gateway. In our case we are working with SharePoint Online, so we select the SharePoint Online source.

At this point, you enter the URL for the site that you want to connect to (NOT the URL for the list) and select the Next button. Power BI Will connect to the site and you can then select which list you want to work with. In our case, we need our Listings data, so we select that list and click Next.

Finally, we’re in the Power Query editing screen. This should be quite familiar to those used to working with Power Query in either Power BI Desktop or in Excel. From here you can select the columns that you want to include in the dataflow.

Although this experience is similar that building queries in the Power BI Desktop, there are a few noticeable differences. Queries in a PBIX file are referred to as queries, but within a dataflow they are referred to as entities. These entities can be custom, or they can be mapped to Common Data Model object types. The Power Query web editor also does not include the full featured editing ribbon found in Power BI Desktop, but instead has a button bar. Many of the editing options available in Power BI Desktop are not available in the Power Query web experience.

If you have read through some of my earlier articles on working with SharePoint data in Power BI, you will notice that there are fewer columns available than we see in the Desktop Power Query editor. Most notably for us working with SharePoint data is the FieldValuesAsText column which is the convenient way of retrieving the text representation of complex SharePoint list column types. At first glance, this would appear to be quite limiting.

However, by right-clicking on the entity name, we can access the Advanced Editor.

This Advanced editor allows you to write queries by hand using the M language. The side benefit of the Advanced editor is that it makes queries portable between platforms -Desktop, Excel, and now dataflows. You can therefore build your queries in Power BI Desktop using its fully functional editor and then copy and paste it into a new blank query in the dataflow editor. Using this approach allows you take advantage of the SharePoint helpers built into Power BI Desktop as the FieldValuesAsText column, and other columns are available. Using this technique, the Listings example can be transformed into several normalized tables in the dataflow.

Click on Done to save your entities, and then the Save button to save your dataflow. You will be prompted to Refresh Now which is a good idea because by default, the dataflow has no data contained within it. To keep the data up to date, you need to set a refresh schedule by clicking the schedule refresh icon under actions for the dataflow in question. From here, you schedule data refresh in the same manner as you would with ta Power BI Report.

Using the dataflow

Once data is loaded into the dataflow it becomes a source for a Power BI report. You must use Power BI Desktop to create this report, there is no way to connect a report to a dataflow in the pure web interface. Start Power BI Desktop and select “Get Data”. Choose the Power BI blade and then Power BI dataflows.

After clicking Connect, you will be presented with a set of Power BI workspaces that contain dataflows. Opening the workspace will allow you to open the dataflow and select the desired entities.

Once loaded, the report can be built just like any other. When it is refreshed, it will be refreshed from the data stored in the dataflow, NOT directly from the SharePoint list. It is therefore important to keep the dataflow itself up to date.

Any number of reports can be created from the dataflow. Instead of having all the transformation logic tied up within a single report, dataflows allow them to be centralized and consistent. With a little work, these transformations allow you work with your SharePoint data just as though it were relational. Power BI dataflows really are the best way to perform data warehousing with your SharePoint data, whether you SharePoint is on line or on-premises.

Business Intelligence in SharePoint 2019

The recent availability of the SharePoint 2019 public preview, and the supporting information that accompanies it has clarified the status of Business Intelligence features in SharePoint 2019. This release, with one exception, is the culmination of the process of decoupling BI from SharePoint which began in SharePoint 2016 through the removal of Excel Services. This decoupling strategy was initially articulated in the fall of 2015 with the document Microsoft Business Intelligence – our reporting roadmap which stated that SQL Server Reporting Services was to be the cornerstone of their on-premises BI investment (and not SharePoint).

The embedded BI features now run with SharePoint as opposed to on SharePoint. These changes do however require some planning and some effort on behalf of those that have already invested in the current platform and wish to move forward on-premises. With this in mind, and the fact that concise information around these changes is a bit difficult to find, I wanted to put this reference together. This post does not get into migration strategies, only the changes themselves.

The source for much of the below comes from discussions with the relevant product teams, and official information is found (today) in two primary places. The document What’s deprecated or removed from SharePoint Server 2019 Public Preview
which was published concurrently with the SharePoint 2019 public preview, and Christopher Finlan‘s presentation at the Microsoft Business Application Summit 2019 entitled Self-service BI and enterprise reporting on-premises with Power BI Report Server.

A summary of the changes to BI features, and a brief discussion of each is below.

Feature Status
SQL Server Reporting Services Integrated Mode Removed
Power View Removed
BISM file connections Removed
PerformancePoint Deprecated
PerformancePoint – Decomposition Trees Removed
Power Pivot for SharePoint Removed
Scheduled workbook data refresh Removed
Workbook as a data source Removed
PowerPivot management dashboard Removed
PowerPivot Gallery Removed

SQL Server Reporting Services Integrated Mode

SSRS Integrated mode was deprecated in November 2016, as was not a part of SQL Server 2017. However, organizations could continue to use SSRS versions from 2016 and prior in SharePoint 2016. This is not supported in SharePoint 2019, which means that integrated mode isn’t an option at all with SharePoint 2019. The good news is that the recent Report Viewer web part fully replicates the capabilities of the SSRS Integrated mode web part.

Power View

Power View was a feature of SSRS Integrated mode and is available in Excel. When Excel Services was removed in 2016, Power View in Excel required SSRS Integrated mode to work. Both supporting platforms are now gone, and thus Power View is not supported in SharePoint 2019.

BISM file connections

The BISM file connection type was used by Excel and SSRS to connect Power View reports to SQL Server Analysis Services data sources. This connection type has been removed along with Power View.

PerformancePoint Services

PerformancePoint is a combination of capabilities that includes dashboarding, scorecards, and analytic reports. Very few new features have been added to PerformancePoint in the last few versions, and this one even loses a few. Many of of these features are also available in Power BI and Power BI report server, and Microsoft has taken the decision to deprecate this product. This gives customers with a PerformancePoint investment time to migrate their assets but is a clear indication that it will also be removed in a subsequent release.

PerformancePoint – Decomposition Trees

The Decomposition Tree feature in PerformancePoint came originally from ProClarity – one of the three products that made up the original PerformancePoint product. These visuals are based on Silverlight, and have been removed from the product accordingly.

PowerPivot for SharePoint

PowerPivot for SharePoint is not supported in SharePoint 2019. PP4SP was originally a combination of two technologies – a specialized version of SQL Server Analysis Services, and a SharePoint service application. In the 2016 version, these two parts were split into two – the SSAS component became a part of the SQL Server installation media as SSQL – PowerPivot mode, and the service application, which continued the name PowerPivot for SharePoint. To be clear, it is the second of the two that has been removed. SSAS PowerPivot mode continues to be an important component and is used by Office Online Server for working with Excel files that have embedded models.

Scheduled workbook data refresh

This feature allowed for the automatic refresh of the data stored within Excel workbooks in SharePoint. It requires a PowerPivot data model to work, but the refresh operation would refresh all connected data in the workbook on a scheduled basis. This was a component of PowerPivot for SharePoint. It has recently been announced that this capability will soon be available in Power BI Report Server.

Workbook as a data source

With PowerPivot for SharePoint deployed, it is possible to use the data model in a published Excel workbook as the data source for another workbook. This feature will no longer be available, and there are no plans at present to reintroduce it.

PowerPivot Management Dashboard

Originally a part of SharePoint Central Administration, the management dashboard provided status updates on all PowerPivot for SharePoint operations. Being a part of PowerPivot for SharePoint, this has been removed accordingly.

PowerPivot Gallery

The PowerPivot Gallery is a modified SharePoint Document library form that displays worksheet thumbnails contained in published Excel workbooks. This component is Silverlight based, and part of PowerPivot for SharePoint. It has been removed accordingly.

Power View, Decomposition trees, and the PowerPivot gallery were the last remaining features that carried a Silverlight dependency. SharePoint 2019 no longer has any Silverlight dependencies.

These changes are significant for anyone with an existing Business Intelligence investment that plans to move to SharePoint 2019. I intent to write more about migration strategies and will be addressing these topics at various conferences in the future.

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 Lookup Fields in SharePoint

This post is the sixth and final post in a series exploring Power BI and complex data types in SharePoint. This post examines the various options in Power BI for working with lookup fields. The previous posts are:

A lookup field in SharePoint contains values looked up from another list in the same SharePoint site. Strictly speaking, the field contains only the ID from the item in the source list, and the value(s) is/are looked up whenever the field is displayed. The lookup field can also be used to display multiple field values from the target list items.

Defining a lookup field in SharePoint

The List

Consider the following list that contains a lookup field named “Neighbourhood”:

The lookup field neighbourhood in the SharePoint view

We can see from the screenshot above that the text value for neighbourhood is displayed in the view, although only the row identifier is stored in the column. We will be able to get both values and more if desired in a Power BI report, but first we need to build the report using 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 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.

Loading data rom the Listings list

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 columns that you don’t need. We can do this by right clicking on the desired column titles and selecting “Remove”. 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” in addition to the special “Neighbourhood” column at the far right of the columns, as we’ll be needing them for our options below.

Using FieldValuesAsText

Examining our columns, we can see that amongst the simpler text fields, we don’t have a “Neighbourhood” column, but instead, a “NeighbourhoodId” column, with numeric values. We do have a Neighbourhood column further off to the right, but it doesn’t display simple text (we’ll come back to this shortly). If we simply want the text value of our lookup target, we can use the “FieldValuesAsText” column quickly.

Scrolling right in the Query editor view, we find the “FieldValuesAsText” column. The record values represent a one to one relationship with the text values of the list row, so we can click on the column expander at the right of the column title. From there we can extract the text value of any column, including our lookup field, “Neighbourhood”.

Extracting text using FieldValuesAsText

With “Neighbourhood” checked, and nothing else, including the “Use original column name..” option, we can click OK, and the “FieldNameAsText” column is replaced by a new column, “Neighbourhood” that contains the text values for Neighbourhood.

If this value is all that is needed, then this is a totally valid approach, and we can move on to report building. However, this is only one way to achieve this goal. If more information is needed, then other methods may be more suitable.

Retrieving all Lookup Field Values from the Extended Column

Given that the lookup target item is a SharePoint list item, all that item’s properties are available to us. We can access them from the extended column set up for the field. In our case, the original “Neighbourhood” column is the extended column. We can expand this column by selecting its column expander.

Extracting field values using the extended column

We then deselect all of the columns except the ones that we want to use in the report. The fields available are the fields available in the target list. In our case, we select the “Title” field, as it is the one being looked up. We can however retrieve any of the fields that we need from the target list.

Keep in mind that “Title” in our example is a simple text field, so no further action is necessary. The retrieved fields can be complex (person, MMS, etc), but keep in mind that if a complex field type is retrieved, it will need to be transformed just like any from the list in question.

The field name in the target list may not adequately describe its function for the report. In our case, “Title” actually means “Neighbourhood” in this report. It’s a good idea to rename it.

Finally, if multiple field values are to be retrieved, the data model could grow significantly. This is because the values for every field are repeated in every row of data. Given that the original lookup column adds a measure of relational behaviour to SharePoint, using this relationship is the most efficient way to work with this data. Power Query allows us to do just that.

Working with Related Tables

To work with related tables, we need not only the original data table (in our case, “Listings”) but also the table for the lookup list itself. To do this, from the Query Editor, we create a new data source like the one created above for “Listings”, but instead we select the lookup list (“Neighbourhoods”).

Once imported, we can remove any extraneous columns, and then set the data type for the ID field to be “Whole Number”.

Setting the ID column to whole number

We also need to set the data type of the “NeighbourhoodId” column in the Listings table to “Whole Number”. Once these options have been set, we are ready to work with the data model and the report. We select “Close and Apply” from the ribbon to load the data into the model.

Once loaded, we launch the relationship builder in the design pane in order to establish the relationship between the two tables.

Power BI relationship editor with default relationship defined

We can see that Power BI has already detected a relationship. However, it is not correct. The model designer assumes that because both tables contain an “id” column, then they must be related. However, the true relationship is between the “Id” column in our “Neighbourhoods” table, and the “NeighbourhoodId” column in our “Listings” table.

We must first delete the detected relationship by selecting the connector between the two tables and pressing “Delete”. We can then create the proper relationship by dropping one of our related columns onto the other. Once this is created, we also need to ensure that the “Cross filter direction” is set to “Both”. We do this by double clicking on the relationship connector and selecting the appropriate option.

Setting cross filter direction

Power BI relationship editor with correct relationship defined

Once the relationship has been established, we can return to the design pane and construct a rudimentary report. We drag a few fields from Listings into a table, create a calculated measure for the number of listings, and we add the “Title” field (renamed to “Neighbourhood”) to the canvas separately. Once we set the visualization for “Neighbourhood” to a slicer, we can easily slice our listings data by neighbourhood.

Slicing report with the values from a lookup field

We can therefore see that there are several options for accessing data for a lookup field, ranging from simple to complex. The trade-off for simplicity is flexibility. Which approach used will depend on your requirements but storing the lookup table separately is the most efficient as the data is only stored once and referenced.

Using Power BI to Report on Hyperlink or Picture Fields in SharePoint

This post is the fifth in a series exploring Power BI and complex data types in SharePoint. This one details the use of SharePoint Hyperlink or Picture fields. The previous posts are:

A hyperlink or picture field in SharePoint consists of a name-value pair. The value is always a URL, and the name is the descriptor for that URL. When the field is created, the creator can specify the character of the field, whether it is a hyperlink, or a picture.

Hyperlink or Picture field in SharePoint

If picture is chosen, then all values will be treated as images, and SharePoint renders them as such wherever displayed, in forms, views, etc. The name part of the name-value pair is used as the alt tag for the image when it is written. If hyperlink is chosen, the name portion will be displayed wrapped in a link to the value. These behaviours are particularly suitable to the way that Power BI works with both link and images, as we’ll see shortly. In our example below, we’ll be working with a list that contains 2 instances of this field type, one configured as a picture, and the other as a hyperlink.

The List

Consider the following list that contains two of these fields. The first named “Picture” is not surprisingly configured as a picture type, and the other, “More Info” is configured as a hyperlink:

Both Picture and hyperlink fields in a SharePoint view

This view renders a thumbnail of the image for the “Picture” field, and a clickable hyperlink using the link name for the “More Info” field. Behind the scenes however, the data is simply stored as that name-value pair. We will be able to get both field types to render with appropriate behaviours in a Power BI report, but first we need to build the report using 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 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.

Loading the Listings data

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 columns that you don’t need. We can do this by right clicking on the desired column titles and selecting “Remove”. 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” column, as we’ll be needing that to extract the text values.

Using FieldValuesAsText

In our example, both the “Picture” and “More Info” fields are displayed with a linkable value of “record” for every row. We will explore using these columns below, but to use FieldValuesAsText, it is best to remove them to avoid naming conflicts. As with most complex field type, the “FieldValuesAsText” column can be used to extract the URL for the Hyperlink or Picture field.

We scroll right and select the expander icon for the “FieldValuesAsText” column, then deselect all available fields except the “Picture” and “More Info” columns. In addition, we want to ensure that the “Use original column name as prefix” option is deselected to avoid a lot of messy renaming later.

Retrieving data with FieldValuesAsText

We then select OK, and two new columns are added in place of FieldValuesAsText named “Picture” and “More Info”. These columns contain the value portion of the name-value pair that make up the Hyperlink or Picture field, but the name portion is dropped.

Raw data for the Hyperlink or Picture Field

If all that is needed for the report is the URL portion, then this approach is sufficient, and you can continue working with the data model and report as below. However, to retrieve both the name and the value from the field, an alternate method is required.

Retrieving all Field Values

Instead of removing the “Picture” and “More Info” columns as described in the previous section, retrieving all of the values requires us to use them. In this case, we can safely remove the “FieldValuesAsText” column first, as it won’t be needed. The “Record” values shown for the field value on each row are Power BI’s way of expressing a one-to-one relationship. In this case, each relationship is with a record that has 2 fields, “Description” and “Url”. To use them in a report, they must first be flattened. We do this by selecting the column expander in the upper right of the column title, selecting both fields, and clicking OK.

Expanding the Hyperlink or Picture field

All Hyperlink or Picture fields will have the same properties, and in our case, this needs to be done for both the “Picture” and “More Info” fields. Because of this, it’s likely a good idea to check the “Use original column names as prefix” box to help keep everything straight. The columns can be renamed at any time if desired. Once This is done for both columns, we will see both the description, and the actual URL value for both of our “Hyperlink or Picture fields, as seen below:

Extracting all Hyperlink or Picture properties

At this point, we are ready to load the data into the data model by selecting the “Close and Apply” button from the ribbon. Once loaded, we are placed into the report design canvas. From here, we need to do a small amount of model editing.

Using Picture or Link Data in the Report

We can add a new table to the design surface, and then add “Picture.Url” to the table. We can quickly see that the default behavior is not optimal – it only displays the URL, not the rendered picture.

Raw picture data in a Power BI report

This is because the data model only knows the contents of the field to be text. We need to tell the model that this is a picture, and we can do that by selecting the Model tab in the ribbon, selecting the field in the field selection pane,

Setting the field properties in Power BI

Once flagged in this manner, the images will automatically render as images whenever they are used in tables, and several other visuals.

Picture field rendering in a Power BI table

The hyperlink field must be categorized in a similar fashion as the picture field, with one difference – instead of Image URL as the data category, we pick Web URL. Once we have done this, we can add it to our table above along with a couple of other fields, including the link description.

Adding Web URL data to the Power BI table

The hyperlinks are active and clickable, but they’re not the nicest to look at. They also take up a significant amount of space on the visual. Happily, there is a table feature that we can take advantage of to help us with this. To turn it on, open the table properties pane (the paint roller), open the Values section, and turn on the option for “URL icon” . All of the long links in the table will be reduced down to a compact link icon.

Web URL data formatted as an icon

Ideally, I would like to be able to recombine the link description and the link in the visual, the same way that it is rendered in SharePoint. However, this does work well, and it lends us a nice level of interactivity in our reports.

As we can see above, the SharePoint “Hyperlink or Picture” field is not only available to Power BI, but much of its utility can also be brought forward into Power BI reports.