Power BI is without question the best way to report on data in SharePoint lists. The query tools available in Power Query make working with SharePoint data relatively painless, an the cached dataset means that reports are run against an optimized copy of the list data, not the data itself.
This latter distinction, while removing the performance issues of systems that query lists directly, also introduces problems with data latency. The report will never be fully “up to date”, as it needs to be refreshed on a periodic basis.
Consider the following scenario. A Power BI report has been built that uses data from a SharePoint list. That report has been embedded on a SharePoint page in the same site. A user adds an item to the list, and then navigates to the page to see the updated report. Unfortunately, that report won’t get updated until the next scheduled refresh.
This has been a significant problem, until the recent release of the new “Refresh a dataset” action in Microsoft Flow.
It is a relatively simple procedure to add a simple 1 step flow to any SharePoint list that is triggered when an item is created, updated, or deleted. This flow simply needs to add the “Refresh a dataset” action, that is configured for the relevant dataset, and these embedded reports will be updated very shortly after the data is modified.
Alternatively, the flow can be triggered by a timer, allowing you to create your own schedule (every 5 minutes, etc) that is not hardwired to run at the top or bottom of any given hour.
A few caveats should be kept in mind when using this action however.
While this action gives us much finer grained control over when refreshes happen, all of the current license restrictions remain in place. For datasets located in the shared capacity, only 8 refreshes per day are allowed.
For datasets in dedicated capacities (Premium), there are no limits to the number of refreshes. The limit of 48 per day is a UI restriction, not a licensing restriction. However, refresh can utilize significant resources, particularly memory, so you’ll want to ensure that you have significant resources to support the update frequency.
Finally, the load on the source data system should be considered. Refresh will pull a significant amount of data every time it is run.
Caveats aside, this new flow action is a welcome relief to those that need greater control of how their reports are updated.
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.
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:
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.
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.
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.
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.
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:
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.
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,
Once flagged in this manner, the images will automatically render as images whenever they are used in tables, and several other visuals.
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.
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.
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.
This post is the third in a series exploring Power BI and complex data types in SharePoint. The first post explores working with multi-value columns, and the second post covers working with person fields. In this one, we’ll explore some of the nuances of working with managed metadata fields
Managed metadata fields in SharePoint are implemented as a special case of a lookup field. As managed metadata is used in SharePoint lists, a hidden list in the root of the site, TaxonomyHiddenList is populated with the values used. The Power BI SharePoint list connector is aware of this, and it provides helpers to make it relatively easy to get the value of these fields. It is also possible to get retrieve some of the extended properties of these fields, specifically all the language variants of the managed metadata values. We’ll examine several approaches to extracting managed metadata information.
The List
Consider the following list that contains a managed metadata field named “City”:
This view displays the value of the managed metadata field in the language of the site. If multiple language values have been defined for the term, the appropriate one will be used. While not shown in the view, these multiple language values, along with other attributes of the term are available to Power BI as needed. Depending on requirements, there are several ways to access the terms values and attributes in Power BI. To do so, the report must be built 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.
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” column (we’ll come back to that shortly). Our simplest requirement will be to extract the value of the metadata column in the default language of the site, the same way that it is represented in the SharePoint list view. We can accomplish this using the FieldValuesAsText column.
Extracting the Term Value
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, our managed metadata column, “City” shows a clickable “Record” value for all rows. Clearly, “Record” is not what we are looking for, but this is how Power BI represents a one-to-one relationship. In this case, the relationship is between the source data row, and the row representing the managed metadata term. We will explore using this column in the next section, but if all we want is the value of the term, we can have Power Query (the Power BI Query editor is really a user interface for Power Query) automatically extract it for us using the “FieldValuesAsText” column.
We scroll right and select the expander icon for the “FieldValuesAsText” column, then deselect all available fields except the “City” column, as well as the “Use original column name as prefix” option.
We then select OK, and a new column “City.1” is substituted for the original column with the value of the term.
The reason that “.1” is added to the column name is that we already have a column named City. If the value is all that is needed, it can safely be removed, and this column renamed. However, if additional term data is required, a more complex approach is required.
Extracting the Managed Metadata Properties
As noted above, the “City” column links to the managed metadata term. It can be used to get at the underlying term properties by clicking on the column expander to the right of the column title and selecting the TermGuid column.
The Term column will display the id of the label of the managed metadata field, which is not what we need here. TermGuid will ultimately link to the term properties that we need, but when we click OK, we can see that the resulting values are simply a collection of GUIDs. Since we will use these values to link to the data we need, we must first set their data type. We do this by selecting the column, then the “Transform” tab in the query editor and selecting the “Text” data type.
At this point, we are ready to link to our terms list.
Linking to the Hidden Taxonomy List
As mentioned above, SharePoint adds items to a hidden list whenever managed metadata terms are used. This list, “TaxonomyHiddenList” is used for all lists in the site. We therefore need to add this list to our query. To do so, we repeat the procedure used above to get our listings data, substituting “TaxonomyHiddenList” for “Listings”.
As mentioned above, this list will contain values for all instances of managed metadata in the site, not just the field that we’re after. While not strictly necessary, it’s a good idea to filter out the values that are unnecessary, particularly if we want to use the term as a slicer. If we don’t all sorts of values could appear that make no sense to our report. In our example, we have listings with two cities, “Guelph” and “Elora” but there is also a value for “North America” that comes from another managed metadata instance. Examining the data we can see that the “IdForTermSet” column uniquely identifies the “City” column. Filtering on the GUID for our column will remove all extraneous data.
The Unique ID of the term in question is stored in the “IdForTerm” column. The term’s value is stored in the “Term” column, and the hierarchical path to the term is stored in the “Path” column. These columns will hold the value of the term in its default language. Scrolling right, we can see that there are many columns titled “Termxxxx” and “Pathxxxx”. These correspond to the term’s value in various languages, the language being identified by the xxxx value – it is the code of the language in question. For example, the language code of French is 1036, so if we want the French values for the term, we will need to use the columns Term1036 and Path1036.
If the term is not in a hierarchy, both the Term and the Path values will be identical. If values have not been defined for he additional languages, then the default language value will be returned. It is important to identify the data needed, and to remove all redundant column data at this point to avoid bloating the data model. In our case, we only need the default language, and there is no hierarchy, so we remove all columns with “Path” in the name, and all columns with “Termxxxx”. In addition, we can remove all the extended columns, and system columns. In the end, in our case we are left with the GUID for the term (named IdForTerm), and the term itself in the default language.
The final step is to change the data type of the “IdForTerm” and the “Term” columns (and any other columns necessary) to Text, using the procedure for “TermGuid” above. Given that “Term” is generic, and we have filtered ours down to the city terms, we also rename the column to “City”.
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 Taxonomy (renamed from TaxonomyHiiddenList). We then select the relationship editor tab. By default, Power BI may assume a relationship for us. In our case, we retained the id field from the taxonomy list, so it established a relationship between the id fields in the two tables, which is incorrect. We need to select the relationship by clicking on the line between the two tables, and then pressing delete. Once deleted, we need to establish a relationship between “TermGuid” in the Listings table, and “IdForTerm” in the Taxonomy table. We establish this relationship by dragging one column 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 this way, we can slice a listing report on the value of the managed metadata column, in any language that we have defined for it.
Clearly, it this case, if we simply want to get the default language value of the term, the FieldValuesAsText approach is far simpler, but linking to the hidden taxonomy list makes it possible to access all available attributes of the managed metadata term.
