SharePoint multi-Choice columns in Power BI

I WOULDN'T NORMALLY PRESUME TO OFFER ADVICE ON POWER BI, as it isn't my core skill. However, I was working on a dashboard to help my colleagues in Corporate Comms analyse the views-by-category of their Intranet news stories and ran into some unexpected challenges.

Those working in corporate environments will be familiar with the ideas of company objectives and employee behaviours, and we had been tagging our news stories with those values for some time (using custom columns added to the newshub's Site Pages library), but hadn't really tried to break down views and time-on-page by any of those metadata values. What complicated it massively was that it was possible for my Corporate Comms colleagues to tag stories with more than one value for Company Objective (for example).

So when I started setting up the dashboard, using existing data from the last 12 months, I was seeing some unexpected behaviour.

When I selected a single story in the dashboard, Power BI was telling me that there were 12 tags associated with that item, yet I could see from the SharePoint SitePages library that the item actually had only seven tag values.

In my limited experience of setting up other dashboards I knew that some SharePoint columns return a value of "Table", when viewed in Power BI Query Editor. All I had to do was expand those columns and we'd be on our way, right?

Because some SharePoint columns, like Choice, generate a Table rather than a Value, this is what you see in Query Editor.

But it wasn't that simple. After I had expanded the column, I found it was returning those phantom values to the dashboard.

Clicking on the little icon in the right of the column header brings up this panel. Selecting Label and clicking OK expands the column to show the actual Values.

Where had the five extra values come from? I was having real trouble figuring it out. Like I say, Power BI isn't my day job, so this was a knotty problem for me.

This is what the metadata columns looked like for that one item after expanding in Query Editor. There are 12 rows here when there are only a total of seven metatags applied to the item in SharePoint. What in the hootin' heck was going on?

Figuring that the world is full of smarter people than me, I posted the question in one of the Microsoft User Forums and waited to see if anyone could help.

THE SOLUTION

The Microsoft Community were very quick to explain that what I had ended up with by expanding the Choice columns the way I had was a Cartesian join. Instead of adding the tags from the three columns, it multiplies them. So two News category values, three DNA values and two Strategic priority values give me twelve instances of metadata tags.

2 x 3 x 2 = 12

Instead of expanding each of the columns, what I needed to do was create three separate Tables in Query Editor - one for each of the metadata columns - and connect them to the main table. Then I had to create a Measure to count up the metadata tags in a non-Cartesian way (we'll get to that). That was helpful, but still lacking a bit in detail. After managing to fill in the gaps in the process, I thought I'd post an account of how I did it here to save anyone else hours of fiddling to reach the same result I did.

With your Dashboard file open in Power BI Desktop app, click on Transform Data to open the Query Editor.

Click the image to enlarge.

In the left hand column of the Query Editor, right-click on the existing table and select Duplicate from the fly-out menu.

Click the image to enlarge.

You now have a copy of the main table named Site Pages (2). In the new table, select the News Categories column.

Click the image to enlarge.

Now scroll right as far as you need until you find the new table's ID column. Control-click that column to select that, as well.

Click the image to enlarge.

Now right click on the header of the ID column and from the fly-out menu select Remove other columns.

Click the image to enlarge.

You're left with just the News Categories and the ID columns in the new table.

Click the image to enlarge.

At this point, you might want to think about renaming the new table to, say, NewsCat so you can identify it easily.

Click to enlarge.

The last step of this part of the process is to expand the News categories column, so that the values can be read by the Dashboard visualisations. Click on the arrows icon on the right of the column header, select Label and click OK.

Click the image to enlarge.

Repeat the process for any other Choice columns you need to include in your Dashboard. When that's done, close the Query Editor. Now, in the Home view of your dashboard file, click on the Model View icon, and join the tables (ID > ID) as shown.

Click the image to enlarge.

Final bit of prep work for me was to create a Measure called "idCount" in the main Site Pages table:

idCount = DISTINCTCOUNT('Site Pages'[ID])

Now you can add, for example, a pie-chart visualisation to the Dashboard. Drag the News Categories.Label column from the NewsCat table to the Legend field, and the idCount measure to the Values field, and that should show you accurately how many times tags from each of the three columns have been added to news stories.

Click the image to enlarge.

OPTONAL EXTRA

The other feature I added was a panel in the Dashboard that showed the average number of metatags that had been added to news stories - both by story and by metadata column. The earlier version (see the screengrab at the start of this entry) had been returning the incorrect, Cartesian values. To get it to return the correct values, I needed to add some new Measures to the Dashboard.

First, I had to count the metatags, which required a slightly different approach. Here's the script for that Measure (TagsCount):

TagsCount = VAR NewsCount =
    CALCULATE(
        DISTINCTCOUNT('NewsCats'[News categories.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
VAR StrategicCount =
    CALCULATE(
        DISTINCTCOUNT('StratPrior'[Strategic priorities.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
VAR DNACount =
    CALCULATE(
        DISTINCTCOUNT('DNA'[DNA.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
RETURN
    NewsCount + StrategicCount + DNACount

Click the image to enlarge.

Then I created another Measure (TagsAverage) to calculate the Average Tags per story, which takes the output from the TagsCount Measure and calculates the average I needed:

TagsAverage = AVERAGEX(
    VALUES('Site Pages'[ID]),
    [TagsCount])

Click the image to enlarge.

The final task was to update the Card visual displaying the Average Tags per story with the Measure TagsAverage. And now the Card was displaying the correct count of seven - which is what I'd wanted all along.

Click the image to enlarge..

All-in-all, not terribly intuitive, but the usefulness of the final Dashboard made the challenge worthwhile.

Click the image to enlarge.

I hope that's been helpful to someone.

Link Content Types in Power Apps

AS ANY INTRANET MANAGER WILL TELL YOU, THE LAST THING YOU NEED is for your Site Editors to take copies of documents from elsewhere in the Intranet to add to their own document libraries. This will only result in multiple copies of the same document throughout the Site Collection, throwing up links to outdated versions of the same document in the Search results and causing confusion for the site visitors.

The problem was very real in SharePoint Classic, but with SharePoint Modern, Microsoft introduced the Link Content Type for Document Libraries, which meant that you can include Links to remote documents in your Library and even apply metadata to help organise those Links in the same way you would with actual documents.

Click image to enlarge.

So, if we can get site editors to change their habits and use Link Content Types instead of multiple copies of files, everything will be just great? Well, kind of ...

I ran into a bit of a challenge when creating a Power Apps searchable Gallery for a document library that uses a lot of Link Content Types. While the documents would open readily from the Gallery, the Link Content Types just downloaded shortcut files to the PC's local Downloads folder. Not really the behaviour I was looking for.

So I went to Google to try to find how a Power Apps Gallery could launch a document from a Library Link Content Type. I think that search term captured all the key words. I found just one post in the Microsoft Power Apps Community site that tried to tackle the problem.

In that post, the first solution offered was to add "?csf=1&web=1" to the end of the Launch Expression, which sort of, kind of works. But it's a pretty ugly result.

Click image to enlarge.

Your visitors would need to know to click on the tiny and obscure Open button at the top right of the screen. That wasn't going to work for me. 

Then, right at the foot of that same post, there were some instructions for using an embedded Flow to extract the URL from a Library Link file. The explanation was lacking in detail, so I set about decyphering it to figure out how to make it work for me.

SET UP THE FLOW

Let's start with creating the in-App Flow. Click the ellipsis at the foot of the Power Apps left hand navigation and select Power Automate from the fly-out menu.

Click image to enlarge.

Click Create new flow and select the Create from blank button. Name your Flow now (I called mine getURL). This is important because first time I tried this I didn't and adding the name after working on the Flow proved trickier than it needed to be.

Click image to enlarge.

When the Flow opens, the first Action - Power Apps (V2) - is already there. Expand the panel and add the value for the Document Library we're going to call later. I've labelled it "FilePath", here. Not the best label - it probably should have been "DocLibrary" - but it really doesn't matter. The key info is the pathway to the Library, which I've put in as:

/DocsMNTraining/

Click image to enlarge.

The next Action we need is the Get file content using path. We add the site address and then the output from the previous action. These will then be combined to render the full pathway to the Library Link in the Document Library.

Click image to enlarge.

Finally, we need to gather together everything from the first two actions and write the output into a variable for the Power Apps Gallery to process. Add the Action Respond to a Power App or flow. I called the variable "urlValue" and set its value to:

last(split(base64ToString(outputs('Get_file_content_using_path')?['body']['$content']),'URL='))

You can do this by adding the above script to an expression.

Because the Get file content using path action extracts the Link file's content as Base64, we need to convert it to a string before the Power Apps Launch function can recognise it (no, I didn't know that either). Bear with me, all will become clear.

Click image to enlarge.

I mentioned at the start of this piece that trying to launch a Library Link Content Type from a Power Apps Gallery caused a shortcut to be copied to my Downloads folder. So I opened one of them and took a peek inside. This is what I saw:

Click image to enlarge.

You can see that the actual URL is preceded by "URL=", so our formula above is saying, "grab everything after "URL=" and convert it from Base64 to a text string.

So far, so good.

... AND BACK TO THE POWER APP

When setting up a SharePoint Document Library in a Power Apps Gallery in the past, I've just used an HtmlText box and put in a formula something like this:

Concatenate("<a href='https://xxxxx.sharepoint.com/sites/SiteName/",ThisItem.'Full Path',"' style='Text-Decoration:None;'><b>",ThisItem.Name,"</b></a>")

I use ThisItem.'Full Path' in preference to ThisItem.'File With Extension' because it makes for a cleaner link and the icon (which we'll get to later) is enough to tell visitors what kind of file to expect. And that works fine for "real" files. But with URL links, we have to trigger the Flow to process the link information, so a different approach is needed. And that's where the Launch function comes in.

The formula we need is something that will kickstart the Flow, extract the Link URL and then action it from Power Apps, so something like this:

Launch(getURL.Run(ThisItem.'Link to item').urlvalue)

What that does is fire up the Flow, which compiles the Link's URL from the value ThisItem.'Link to item', converts it back into a string and writes the value to the variable urlvalue. The formula then Launches the value in the urlvalue variable.

That's all fine and dandy for processing URL links stored in a SharePoint Library. But what about the actual files - PDFs .docx, etc? Well, we just need to combine the formula into an If statement and we should be good to go. Like this:

If(
    Lower(Right(ThisItem.'File name with extension', 3)) = "url",

    /* Item is a SharePoint .url file */
    Launch(
        getURL.Run(
            ThisItem.'Link to item'
        ).urlvalue
    ),
    /* Item is a normal file */
    Launch(
        ThisItem.'Link to item'
    )
)

What we're saying here is ... if the last three characters on the right of the filename are "url", run the Flow. If not, just use the regular ThisItem.'Link to item' value. The "lower" is just a precaution in case the Link's suffix is ".URL" instead of ".url".

Then, we need to deploy the solution into the Gallery. My first thought was to just put in a Text box displaying the FileName, then add the above formula to the Text Box's OnSelect property. And that does work. But the problem is, there are no visual indicators that the Text Box is a link - no dynamic underline, no mouse rollover state, so not ideal. And there aren't any Cursor or OnHover properties in a Text Box.

After a bit of head-scratching I finally settled on a Button, making the fill transparent, adding an underline to the text. The cursor change on rollover is part of the default Button setup.

An optional extra I thought worthwhile was to also tackle the icon displayed next to the Library item. A quick Google will throw up the formula for extracting the Microsoft file icons and the PDF one. But there is no default icon for Library Links. So I made one. And because it's a custom icon, I stored it in the Site Assets library, then wrote an If statement for the Image box.

If(Lower(Right(ThisItem.'File name with extension',3)) = "url",
    https://xxxxx.sharepoint.com/sites/SiteName/SiteAssets/Link.png,
    https://res-1.cdn.office.net/files/fabric-cdn-prod_20220127.003/assets/item-types/32/ & Last(Split(ThisItem.'File name with extension',".")).Value & ".png"
)

That should be everything you need to know to get a Power Apps Gallery to display functional Link Content Types (from a SharePoint Library) alongside regular files.

Click image to enlarge.

I hope this helps someone.