Getting Up and Running with Power BI and SharePoint Lists

Power BI

A few weeks back when I was at the Digital Workplace Conference in Melbourne – a most excellent conference – I saw Adam Cogan (@adamcogan) demonstrate Power BI in his session Improve Your Business Intelligence with Power BI. I’d seen Power BI before a few times, but the way Adam showed it, it finally “clicked” for me. I may be hooked, and I *know* I’m going to be able to do some amazing things for clients with it.

I had to try it out. I have a client where I think Power BI could be an excellent part of the over all solution, so I tried a few things out in their tenant. The client is a pharma startup and we’ve built a site where they can log all of their experimental data. There’s plenty of data stored across a set of lists and libraries we built to match their scientific processes. The main storage location is a SharePoint Document Library made up of hundreds of Document Sets.

If I tried to connect to the SharePoint Document Library using the “SharePoint Online List” connection, I end up with this as the query:

let
  Source = SharePoint.Tables("https://tenant.sharepoint.com/sites/siteName", [ApiVersion = 15]),
  #"57cb07a8-e7aa-4401-a778-699941bfe12b" = Source{[Id="57cb07a8-e7aa-4401-a778-699941bfe12b"]}[Items]
in
  #"57cb07a8-e7aa-4401-a778-699941bfe12b"

which gave me the following error (DataSource.Error):

DataSource.Error

Trolling the Power BI community forums, I came across the suggestion to switch from [ApiVersion = 15] to [ApiVersion = 14]. When I made that switch, the error changed, but still no joy, as I got this error (Expression.Error):

Expression.Error

I figured maybe since I was working with a Document Library instead of a list that was part of the problem. (It didn’t really make sense to me that it would, but…) I figured I’d try an OData call to the REST endpoint instead.  When I connected to the Document Library as an OData source without specifying anything for the $select, the query ended up looking like this:

let
  Source = OData.Feed("https://tenant.sharepoint.com/sites/siteName/_api/web/lists/getbytitle('List Name')/items")
in
  Source

…and I got the same error (DataSource.Error) as above.

DataSource.Error

The column in question here is a multi-select choice column and it became clear that Power BI doesn’t seem to like multi-select columns. I verified this in a conversation with my buddy John White (@diverdown1964). John is a fellow MVP and one of the best BI experts out there. I figured if he didn’t know how this worked, no one would. (Follow John’s Blog ‘The White Pages‘ for all sorts of BI goodness – and more.)

As John and I were talking, it occurred to me that I could just request the columns I really needed in the REST call. Not only might it solve the problem, it would certainly be more efficient. I’ve learned to only ask for the columns I need when I’m making Web Services calls in general.

When I added the $select clause and just asked for the Title column:

let
  Source = OData.Feed("https://tenant.sharepoint.com/sites/siteName/_api/web/lists/getbytitle('List Name')/items?$select=Title")
in
  Source

Joy! I saw the data in Power BI just fine.

Just the Title

So I think the moral of the story is to build a REST call to only retrieve the data you need, excluding any multi-select columns right up front. As I mentioned, the problem column was a multi-select choice, so there was no option there. I simply couldn’t use that column in my analysis. Luckily that column didn’t really matter to us – yet.

I had better luck with lookup columns – as long as they weren’t multi-select. By using $expand on those columns, I could retrieve the values from the associated lookup list.

Finally, since we only get 100 items back from a REST call by default, I added $top=5000 so that I could retrieve the maximum allowable number of items per request.

Unfortunately, there are some drawbacks to using Power BI right now, and the team will need to solve these for people to really start using it to its full potential in the SharePoint / Office 365 space. Multi-select columns are pretty common in SharePoint lists, and Power BI needs to handle them more gracefully, if not actually use them for analysis. The error messages I got were certainly misleading, and the suggestions about how to fix them were interesting, but not effective in the end.

And it’s very yellow.

How to: Selecting Multi-Select Choice Columns in Forms with jQuery

As I’ve written many times in the past, I get a lot of questions via this blog. I try to answer some of them, but often they simply come in too fast and furious for me to get to them all.

Answering the questions gives me some benefits, too, or course. First, I often learn something I didn’t know (always a good thing), and second, I often turn the question and its answer into a blog post like this.

This one came in a few weeks back and I just found it as I was cleaning out my inbox.

Your blogs, post really helpful to me. [Nice – always start a blind question with some sort of pleasant preface. Really.]

Currently I am working in a project with SharePoint Server 2013. I am using jquery/javascript for few custom operations. I have a list “Task Assignment” where I am using a choice type column with “checkboxes multiple selection” enabled named “Task Category”. I need to trigger onchange or click event when user check/uncheck any items of the choice field.

But I could not get the element by id or tag name. I have worked with radio button using below statement.

$('input:radio[name$="0b57f_$RadioButtonChoiceField"]').bind("change", reviewedChanged);

I have tried to change this to work with multiple selection choice field.

$('input:checkbox[name$="f7c37_MultiChoiceTable"]').bind("change", assignTask);

But it is not working. Can you please suggest me.

Thanks in advance. [Also nice. It’s amazing how few people ever say please or thank you.]

When I answer, I always preface my answer with something like this. (I have a signature set up in Outlook with this text because I use it so much.)

First off, thanks for turning to me with your question.  I’m happy to try to help, but you should really use a public forum to post your question. Some of the forums that can be useful are:

* SharePoint forums on MSDN (http://social.msdn.microsoft.com/Forums/sharepoint/)

* SharePoint on StackExchange (http://sharepoint.stackexchange.com)

Many people are active in these forums and you’re likely to get prompt responses. Additionally, others can benefit from finding any useful answers you get in the forums.

I was off the grid in Africa when this question came in. If it had any sort of time sensitivity, then sending it just to me wasn’t a good strategy. Plus, I may simply have never answered.

9-17-2013 2-19-59 PMThen I get to the answer. In this case, in my SharePoint Online (2013) test environment, a multi-select checkbox renders as a set of input elements like so:

<table id="test_2b0bc958-232c-4e77-a8b1-7b3a1745f84d_MultiChoiceTable" cellspacing="1" cellpadding="0">
  <tr>
    <td>
      <span title="a" class="ms-RadioText">
      <input id="test_2b0bc958-232c-4e77-a8b1-7b3a1745f84d_MultiChoiceOption_0" type="checkbox">
      	<label for="test_2b0bc958-232c-4e77-a8b1-7b3a1745f84d_MultiChoiceOption_0">a</label>
      </span>
    </td>
  </tr>
  <tr>
    <td>
      <span title="b" class="ms-RadioText">
        <input id="test_2b0bc958-232c-4e77-a8b1-7b3a1745f84d_MultiChoiceOption_1" type="checkbox">
        <label for="test_2b0bc958-232c-4e77-a8b1-7b3a1745f84d_MultiChoiceOption_1">b</label>
      </span>
    </td>
  </tr>
...

My column name is “test”, so we see ids like “test_2b0bc958-232c-4e77-a8b1-7b3a1745f84d_MultiChoiceOption_0”. This means the selector could be something like:

$("input[id^='test_'][id*='_MultiChoiceOption_']").change(function() {
  alert($(this).attr("id"));
});

The code above is what I used to test. I find that this syntax makes more sense from a readability perspective over using bind, but that’s more of a stylistic thing.

The selector breaks down like this:

  • find input elements – $(“input
  • where the id starts with “test_” – [id^=’test_’]
  • and the id contains “_MultiChoiceOption_” – [id*=’_MultiChoiceOption_’]”).

The most important message here is that you can’t make any assumptions about the way ids are constructed in SharePoint because they are all over the map. You simply have to get good at using the various DOM inspectors, including the Internet Explorer Developer Toolbar and Firebug.

Keep in mind that some controls (not this one) may be rendered differently in IE than in other browsers – even in SharePoint 2013 – so you need to test your selectors in multiple browsers. Once you get familiar with how things like ids and “controls” – on the browser side, controls are collections of HTML elements and script – are rendered, you’ll feel more comfortable knowing when you’ve got it right.

Displaying Multi-Select Column Values in CrossList DVWPs

This is one of those things that I’m amazed I haven’t run into before. Perhaps I did in the past and wrote it off to my own lack of understanding. It turns out that this is a known, if rarely mentioned, limitation not only for Data View Web Parts (DVWPs) but also Content Query Web Parts (CQWPs).

You can easily reproduce this issue as follows:

  • Create a new Custom List (or any type of list you choose)
  • Add a Person or Group column called Project Manager. Do not allow multiple values.
  • Add a new item to the list with whatever values you want
  • Create a page with a DVWP on it with your list as its DataSource
  • Display some columns from the list, including Project Manager
  • Add a filter so that you are sure you’ll only get the item(s) you’ve added to your list
  • Convert the DVWP to DataSourceMode=”CrossList”

At this point, you should see the item(s) in your DVWP just as you would expect.

Now go back into the list settings and change the Project Manager column settings so that it allows multiple values. Next go back into your page with the DVWP and Refresh the data view. You should now see no items at all.

This simple test should prove that the issue is only the multiple value selection which essentially acts as a “filter” which you didn’t ask for. You can probably pretty easily think of reasons you’d want to display data like this. In my little example, you might have multiple Project Managers in Projects lists in sites across the Site Collection. If you wanted to show all of the projects rolled up somehow, you might well want to display the Project Manager(s).

Unfortunately, in my testing, this works exactly the same in both SharePoint 2007 and 2010.

Once I realized this was what was going on, I turned to the Interwebs and found some old posts from Waldek Mastykarz and others that mentioned the limitation. I could only find a few posts, but when the people who’ve done the posts are as smart as Waldek, I take their word for it – it’s not me this time, it’s a SharePoint limitation.

This is truly one of those “features” which feel an awfully lot like a “bug”.

I did find one trick to at least allow the items to be displayed, even though the multi-select column values will not be displayed. If we add Nullable=”TRUE” to the ViewFields settings in the CAML in the SelectCommand, then we do get the items to display, albeit with blank values for the multi-select columns.

This ends up looking something like this. Note that I have added the Nullable attribute to the Project Manager FieldRef.

&lt;ViewFields&gt;&lt;FieldRef Name=&quot;Title&quot;/&gt;&lt;FieldRef Name=&quot;Project_x0020_Manager&quot; Nullable=&quot;TRUE&quot;/&gt;&lt;FieldRef Name=&quot;ID&quot;/&gt;&lt;FieldRef Name=&quot;PermMask&quot;/&gt;&lt;/ViewFields&gt;

Now I can see all of the items, but the Project Manager is simply blank. A step forward, but not far enough.

image

Time for a kludgy fix, don’t you think? Well, I think I’ve got one for you. We can use script and my SPServices jQuery library to “fill in” the values after the page loads. Since we can display the items, but not the values for the multi-select column, we can use the Lists Web Service and specifically GetListItems, to go and grab the items, parse out the multi-select column values, and place them into the DOM where they belong.

This isn’t the type of thing that I like to use jQuery for, really, as it really feels like a kludge. On the other hand, if it plugs a hole in SharePoint’s functionality, maybe that’s not so bad?

To make this work, you’ll want to create good “hooks” in the markup you render for the items in your DVWP or CQWP. I always try to do this, anyway, if I’m going to use script on the page so that my selectors can be very “tight” and efficient.

In the DVWP, I simply add three new attributes for the table detail cell (TD):

  • id – This is a unique id for the TD element, which I create by concatenating the string “ProjectManager_” and the current item’s ID. I’ll use these ids to find the empty cells in the DOM.
  • ListId – This is the GUID for the list which contains the item. The @ListId “column” contains this value. (This “column” only exists after you switch to CrossList.)
  • ItemId – This is the ID for the item itself. We could parse it out from the id above, but it’s easier to store it as its own attribute.

You may not realize that you can create any attributes that you want for HTML elements. They aren’t standards compliant, of course, but by adding your own attributes you can store any values you might need.

<td class="ms-vb" id="ProjectManager_{@ID}" ListId="{@ListId}" ItemId="{@ID}">
  <xsl:value-of select="@Project_x0020_Manager" disable-output-escaping="yes"/>
</td>

Now that I have markup which makes it pretty easy to both select the right DOM elements as well as the data I need to make the Web Services call, I can use this script:

$(document).ready(function() {

  var projectManager;

  // For each empty Project Manager column value...
  $("td[id^='ProjectManager']").each(function() {

    // ...call GetListItems to get that item
    $().SPServices({
      operation: "GetListItems",
      listName: $(this).attr("ListId"),
      CAMLQuery: "<Query><Where><Eq><FieldRef Name='ID'/><Value Type='Counter'>" + $(this).attr("ItemId") + "</Value></Eq></Where></Query>",
      async: false,
      completefunc: function(xData, Status) {

        // Parse out the Project Manager value
        projectManager = $(xData.responseXML).find("[nodeName='z:row']").attr("ows_Project_x0020_Manager");
      }
    });

    // Create the links and the column value into the DOM
    $(this).html(userLinks(projectManager));

  });

});

function userLinks(columnValue) {

  var userArray = columnValue.split(";#");
  var numUsers = userArray.length / 2;
  var out="";
  for(var i=0; i < numUsers; i++) {
    out += "<a href='/_layouts/userdisp.aspx?ID=" + userArray[i*2] + "'>" + userArray[(i*2)+1] + "</a>";
    out += i < numUsers ? "<br/>" : "";
  }
  return out;
}

Using this simple bit of script fills in the values for the Project Manager by getting the right items and plugging the values into the DOM, like so.

image

I decided to simply show the names as links to the userdisp.aspx page, with each one on a new line. This link will show either the information for that user in the User Information List or their My Site profile, depending on how the environment is configured.

Depending on what your data looks like (how many items you are displaying, how many multi-select columns you have, etc.), there are obviously some inefficiencies in my example, because I’m calling GetListItems once per item. You could also batch your calls together per list to get all of the items from that list, or whatever made sense in your situation.

Finally, if using script like this gives you a bad feeling, then you could try using a third party Web Part like the Bamboo List Rollup Web Part or just develop your own custom Web Part. But it seems that if you’ve gotten this far, you’re probably trying to stick to the Middle Tier, so the script approach might make sense.

References:

Setting Multi-Select Picker Widths in a SharePoint Form Reliably

In an old post, I sketched out how you could set go about Setting Multi-Select Widths in a SharePoint EditForm.aspx Using JavaScript.  The code I posted there worked just fine, but took some manual tweaking every time you wanted to use it.  The basic idea is this: When SharePoint renders the multi-select control, the <div>s which contain the selects have a fixed width of 143px.  (Who knows where *that* number came from?!?!)  This is all well and good in the example below.  All of the states fit well inside the <div>s and are quite legible.

image

But what about the case where the values in your multi-select column are much longer? In the example below, I’m showing a multi-select column which takes its values from the list of Web Services operations which the jQuery Library for SharePoint Web Services currently supports. (Yes, I keep track of things in SharePoint lists.  Isn’t that wonderfully recursive?)  As you can see, it would be very easy to choose the wrong value from among those which start with “GetGroupCollection” unless you scroll to the right.

image

So what I’d like to do is build a function into the jQuery Library for SharePoint Web Services to deal with this better.  It will become an option on $().SPServices.SPCascadeDropdowns, but clearly it will have usefulness just as a standalone function.  I want this new function to work automagically regardless of the font family and font size.

I’ve asked some of the smart design folks I know, like Michael Greene (@webdes03) and Marcy Kellar (@marcykellar) whether they have any ideas.  Since these bright folks didn’t come up with an instant “Oh, you just…” answer, I don’t feel too bad about not seeing an approach immediately.

It’s pretty easy to figure out what the longest possible value is, and the code below works well. The issue is that the 10px multiplier is just a hack. What I’d like to be able to do is determine the average width of a character in the select and multiply by that. For instance, if the select font-size is 20px, then the 10px isn’t going to work well. (Note that I’ve intentionally left the code very verbose below just to show what I’m doing.)

var maxWidth = 0;
$("select:[Title='Web Service Operation selected values']").find("option").each(function() {
  if($(this).html().length > maxWidth) maxWidth = $(this).html().length;
});
$("select:[Title='Web Service Operation possible values']").find("option").each(function() {
 if($(this).html().length > maxWidth) maxWidth = $(this).html().length;
});
$("select:[Title='Web Service Operation possible values']").parent().css("width", 10 * maxWidth + "px");
$("select:[Title='Web Service Operation possible values']").css("width", 10 * maxWidth + "px");
$("select:[Title='Web Service Operation selected values']").parent().css("width", 10 * maxWidth + "px");
$("select:[Title='Web Service Operation selected values']").css("width", 10 * maxWidth + "px");

image
So, does anyone out there have any good ideas about how to decide what that 10px multiplier ought to be? Remember that it needs to work whatever the font-family and font-size are.

UPDATE 2009-12-18 21:30 – I had a thought on this.  It might work to add a hidden <div> to the page that has 100 characters styled exactly the same as the selects, check the width of that <div>, divide by 100 and then use that result as the multiplier.  It wouldn’t be exact, but it ought to be darn close.  I’ll pursue this approach until I hear something smarter, which I no doubt will!

Setting Multi-Select Widths in a SharePoint EditForm.aspx Using JavaScript

<UPDATE date=”2009-01-25″>
I now have a function in my jQuery Library for SharePoint Web Services called SPSetMultiSelectSizes which accomplishes this in a more robust way, taking into account the font, font size, etc.
</UPDATE>

When you have a multi-select lookup column in a list, SharePoint provides you with a control that shows two select boxes next to each other on EditForm.aspx.  There are two buttons (‘Add >’ and ‘< Remove’) that let you move values between the two, selecting or deselecting the values.

By default, the select boxes are a fixed width and, in many cases, not wide enough to let your users see the values very well.  The following JavaScript will set the widths of the select boxes based on the length of the longest value available in the lookup.  It isn’t fully multipurpose, as I wrote it for a specific instance with a particular set of branding (font size, spacing, etc.) but it ought to give you a very good start to use it yourself.  (With the fonts that I was using, I found that 7 times the number of characters in the longest value worked well to calculate the right width.  You’ll probably need to experiment.)  Pass in the name of the column you want to adjust.

// setSelectWidth: Set the width of a lookup column's select box based on the values it contains
// Arguments:
// columnName: The name of the column which is being displayed in the select box
//
function setSelectWidth(columnName) {

  var charFactor = 7; // Approximate pixels per character

  // Left side
  leftColumnSelect = getTagFromIdentifierAndTitle("select","",columnName + " possible values");
  if(leftColumnSelect != null) {
       leftColumnSelectDIV = findParentElement(leftColumnSelect, "DIV");
  }

  // Right side
  rightColumnSelect = getTagFromIdentifierAndTitle("select","",columnName + " selected values");
  rightColumnSelectDIV = findParentElement(rightColumnSelect, "DIV");

  // Find the longest value
  var longestValue = 0;
  for (var i=0; i  leftColumnSelect.options.length; i++) {
    if(leftColumnSelect.options[i].text.length > longestValue) {
      longestValue = leftColumnSelect.options[i].text.length;
    }
  }
  for (var i=0; i < rightColumnSelect.options.length; i++) {
    if(rightColumnSelect.options[i].text.length > longestValue) {
      longestValue = rightColumnSelect.options[i].text.length;
    }
  }

   // Set the widths of the two selects and their containing DIVs
   var newWidth = charFactor * longestValue;
   leftColumnSelectDIV.style.width = newWidth;
   leftColumnSelect.style.width = newWidth;
   rightColumnSelectDIV.style.width = newWidth;
   rightColumnSelect.style.width = newWidth;
}

This function builds on the getTagFromIdentifierAndTitle function provided in an excellent blog post I’ve referenced before over at the Microsoft SharePoint Designer Team Blog.  I’ve also created a findParentElement function you’ll see called above, which finds a specific parent element for a tag.  The code for this is below:

// findParentElement: Find an object's specified parent element
// Arguments:
// thisElement: The element you want to search from
// parentTag: The parent tag you want to find
//

function findParentElement(thisElement, parentTag) {
  var currentElement = thisElement;

  while(currentElement.tagName != parentTag) {
    currentElement = currentElement.parentNode;

    //alert(currentElement.tagName);
    if(currentElement.tagName == parentTag) {
      //alert('HIT:' + currentElement.tagName);
      return currentElement;
    }
  }
  return null;
}