Power BI: A simple trick to present KPI's and RAG status in tables

  As some of you might already know, Power BI is a fantastic tool for interactive reports and dashboards. Although, if you've been doing some heavy lifting with it, you might also know that it has its limitations.

  In order to help you with one of them, I'll describe a step-by-step approach to display KPI's or RAG status in simple tables.

Scenario:

  I will be using data pulled from project online, in this specific case, tasks. Each task has a RAG status value in a field called 'RAGSched' (be aware that this is a custom field, hence it can have any other name). The icons I'm going to use, also belong to PWA and are stored in the '_layouts/15/inc/PWA' folder.

  To start, I'm going to create a static table to store the icon's name and url, then, I'm going to create a function to retrieve the url passing the icon's name as a parameter and finally apply a bit of logic in a new field, to get the right image according to the RAG field's values.

1) Tenant and Site Collections parameters - as a good practice :)

2) The static table

This will be just a simple table with the relative path to the icons.

3) The function

Ok, now let's select 'New Source' > 'Blank Query' and then click the 'Advanced Editor' button to insert our DAX function:

let

    IconUrl = (IconName as text) => 

    let

        Source = List.First(Table.Column(Table.SelectRows(Icons, each [IconName] = IconName) as table, "ImageName" as text)),

#"CalculatedUrl" = "https://" & Tenant & ".sharepoint.com" & SiteCollection & "/" & Source

    in

        CalculatedUrl

in

    IconUrl

I called it 'GetIconUrl'.

4) Getting the data and adding the new RAG column

As mentioned before, I'm going to be pulling data from PWA, which means I will add a new OData Feed query. Since it's created in the advanced editor mode, I'll break it step-by-step.

Source = OData.Feed("https://" & Tenant & ".sharepoint.com" & SiteCollection & "/_api/ProjectData/Tasks?$select=TaskId,ProjectId,TaskName,RAGSched,TaskComments"),

Here we define the source, using our parameter 'Tenant' and 'SiteCollection' to build the complete url. I'm querying the tasks feed and selecting the fields I want to work with (notice that 'RAGSched' is the field I'll be looking at).

#"Tasks - Add RAG Column" = Table.AddColumn(Source, "RAG Status", each 

if (Text.Length([RAGSched]) > 0)

                then

  if Text.Contains([RAGSched], "Completed") 

then GetIconUrl("Milestone-Completed")

else

if Text.Contains([RAGSched], "On Schedule")

then GetIconUrl("Milestone-OnSchedule")

else

if Text.Contains([RAGSched], "not critical")

then GetIconUrl("Milestone-NonCritical")

else

GetIconUrl("Milestone-Critical")

else 

"")

The second part will add the new column 'RAG Status' and set the icon url according to the RAG field's value.

Here's the complete code block for the query:

let

    Source = OData.Feed("https://" & Tenant & ".sharepoint.com" & SiteCollection & "/_api/ProjectData/Tasks?$select=TaskId,ProjectId,TaskName,RAGSched,TaskComments"),

    #"Tasks - Add RAG Column" = Table.AddColumn(Source, "RAG Status", each 

if (Text.Length([RAGSched]) > 0)

                then

  if Text.Contains([RAGSched], "Completed") 

then GetIconUrl("Milestone-Completed")

else

if Text.Contains([RAGSched], "On Schedule")

then GetIconUrl("Milestone-OnSchedule")

else

if Text.Contains([RAGSched], "not critical")

then GetIconUrl("Milestone-NonCritical")

else

GetIconUrl("Milestone-Critical")

else 

"")

in

    #"Tasks - Add RAG Column"

At this point, if everything went as expected, the new field will show the icon's url:

And this is how your 'Edit Queries' window should look like:

5) Applying the right data category

The only thing missing now is to tell Power BI that our new column contains an image url. To do that, just navigate to your data, select the new column (RAG Status), click on 'Modeling' > 'Data Category' and select 'Image URL'.

Now go to your report's page, add the table visual, select the columns you want to show and apply some styling... it should look similar to the following table:

Hope it helps!!!

Yammer Analytics: Getting all the relevant information from your company's social network

Hi guys :)

Today I've decided to show you how to put together all the relevant information from Yammer, using the export API (/export) and a few complementary API calls.

The scenario:
Ideally, this would be an Azure web job running each day, pulling information from Yammer and storing it into an Azure Database. After the information being stored, we will use some Power BI magic to show the data in a cool interactive way.

Let's start with the export API, by creating a method to perform the call and retrieve the csv files in a zipped folder.

public ZipArchive GetExport(DateTime exportStartDate)
        {
            var url = String.Format("https://www.yammer.com/api/v1/export?model=Message&model=User&model=Group&model=Topic&model=UploadedFileVersion&model=DocumentVersion&access_token={0}&include=csv&include_ens=false&since=" + exportStartDate.ToString("yyyy-MM-dd") + "T00%3A00%3A00%2B00%3A00", this.accessToken);
            HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;
            request.Headers.Add("Authorization", "Bearer" + " " + this.accessToken);
            using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
            {
                using (var stream = response.GetResponseStream())
                {
                    return new ZipArchive(stream);
                }
            }
        }

Notes:

• Since we're going to run the job everyday, the exportStartDate will be DateTime.Now.AddDays(-1);
• The access token will be defined in the App.config file, as shown below

<appSettings>
    <add key="Token" value="19094-23I3k4uZtdgXXXXXXXXXX" />
</appSettings>

In order not to make this post too big, I will only demonstrate how to process messages, which is the "messages.csv" file entry within the export zip folder.

So here's how our code will start:

//Date to export from
DateTime exportSince = DateTime.Now.AddDays(-1);
 
//Get util methods and classes
YammerUtil util = new YammerUtil(token, permalink);
ZipArchive zip = null;
 
//DB Context
AzureContext dbContext = new AzureContext();
 
try
{
    zip = util.GetExport(exportSince);
 
    //Proceed only if there's data to import
    if (zip != null)
    {
        //Zip archive entries
        ZipArchiveEntry msgsCSV = null;
 
        foreach (ZipArchiveEntry entry in zip.Entries)
        {
            if (entry.Name.Equals("Messages.csv", StringComparison.CurrentCultureIgnoreCase))
                msgsCSV = entry;
        }

Some of the code above was already explained, so I'm going to detail the rest of it:

• YammerUtil is the class where I've created some general methods (like the export one)
• AzureContext is my database context to use Entity Framework with the Azure DB.
• We will declare a ZipArchiveEntry object to get the "messages.csv" file entry, by iterating through all the entries inside the retrieved zip folder.

Now, considering that we've successfully retrieved our messages, let's declare the lists and methods' classes. These methods will help us to store our objects in Azure and also to perform some complementary calls to retrieve some relevant information.

if (msgsCSV != null)
                    {
                        //Lists
                        List<Message> messages = new List<Message>();
                        List<Like> likes = new List<Like>();
                        List<Share> shares = new List<Share>();
                        List<Mention> userMentions = new List<Mention>();
                        List<Mention> tagMentions = new List<Mention>();
                        List<Praise> praises = new List<Praise>();
                        List<string> threadIDs = new List<string>();
 
                        //Methods
                        MessageMethods messageMethods = new MessageMethods(util, dbContext);
                        MentionMethods mentionMethods = new MentionMethods(util, dbContext);
                        LikeMethods likeMethods = new LikeMethods(util, dbContext);
                        PraiseMethods praiseMethods = new PraiseMethods(util, dbContext);
                        ShareMethods shareMethods = new ShareMethods(util, dbContext);

From "messages.csv" we can automatically get values like "message_id", "replied_to_id", "group_id", "user_id", etc. Although, we might need some more information, such as, how many likes does this message have? how many shares? Does it mention or praise any user? Does it contain any relevant tags?

In order to retrieve all this information, complementary calls have to be made.

Let's see some methods...

messageMethods.GetMessageDetails(message);

We will need the message attachments, so let's start by appending the attachments to the current message object:

public Message GetMessageDetails(Message msg)
        {
            try
            {
                var url = String.Format("https://www.yammer.com/api/v1/messages/{0}.json?access_token={1}", msg.id.ToString(), util.accessToken);
                var json = util.GetYammerJson(url);
                Message apiStats = JsonConvert.DeserializeObject<Message>(json);
                if (apiStats != null && apiStats.attachments != null)
                    msg.attachments = apiStats.attachments;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Message ID: " + msg.id + ". Error getting message details: " + ex.Message);
            }
 
            return msg;
        }

Once done, we're going to process our messages:

//process messages
util.ProcessMessages(messageMethods, messages, likes, shares, userMentions, tagMentions, praises);

Here's the code...

public void ProcessMessages(MessageMethods messageMethods, List<Message> messages, List<Like> likes, List<Share> shares, List<Mention> userMentions, List<Mention> tagMentions, List<Praise> praises)
        {
            //do processing based on processing selections
            foreach(var message in messages)
            {
                try
                {
                    //get praises 
                    if (message.attachments != null)
                    {
                        foreach (attachment attch in message.attachments)
                        {
                            if (attch.type.Equals("praise"))
                                praises.AddRange(GetUserPraises(attch,message));
                        }
                    }
 
                    //get mentions
                    userMentions.AddRange(GetUserMentions(message.bodyText, message.id));
                    tagMentions.AddRange(GetTagMentions(message.bodyText, message.id));
 
                    //get likes
                    likes.AddRange(messageMethods.GetMessageLikes(message.id.ToString()).Select(x => new Like() { messageId = message.id, userId = x.id }));
 
                    //get shares
                    shares.AddRange(messageMethods.GetMessageShares(message.id.ToString()).Select(x => new Share() { messageId = message.id, userId = x.sender_id, created_at = x.created_at }));
                }
                catch (Exception)
                {
                    //ignored in the blog's post
                }
            }
        }

... and here's the remaining code for some of the called functions

public static List<Praise> GetUserPraises(attachment attachment,Message msg)
        {
            List<Praise> praisedUsers = new List<Praise>();
            if (attachment.praised_user_ids != null)
            {
                foreach(int userId in attachment.praised_user_ids)
                {
                    Praise praise = new Praise
                    {
                        praisedUserId = userId,
                        praisorUserId = Convert.ToInt32(msg.user_id),
                        description = attachment.description,
                        icon = attachment.icon,
                        praised_at = msg.created_at
                    };
 
                    praisedUsers.Add(praise);
                }
            }
            return praisedUsers;
        }
 
        public static List<Mention> GetUserMentions(string body, int msgId)
        {
            List<Mention> userMentions = new List<Mention>();
            if (!String.IsNullOrEmpty(body))
            {
                if (!String.IsNullOrEmpty(body))
                {
                    //check for user mentions
                    var body2 = body;
                    while (body2.IndexOf("[User:") != -1)
                    {
                        var m = body2.Substring(body2.IndexOf("[User:"));
                        m = m.Substring(0, m.IndexOf("]") + 1);
                        if (m.Length == 0)
                            body2 = "";
                        else
                        {
                            var id = m.Substring(6);
                            id = id.Substring(0, id.IndexOf(':'));
                            var mention = m.Substring(m.IndexOf(id) + id.Length + 1);
                            mention = mention.Substring(0, mention.Length - 1);
                            body2 = body2.Substring(body2.IndexOf(m) + m.Length);
                            userMentions.Add(new Mention() { messageId = msgId.ToString(), mentionId = id, mentionName = mention });
                        }
                    }
                }
            }
            return userMentions;
        }

And finally, we add/update our objects in the database:

//Update messages in DB
foreach (Message msg in messages)
{
     messageMethods.UpdateMessage(msg);
}
//Here's the method (Using EF)
public bool UpdateMessage(Message message)
{
    Message target = dbContext.Messages.Where(entity => entity.id == message.id).AsQueryable().FirstOrDefault();
    if (target == null)
    {
         CreateMessage(message);
    }
    else
    {
         dbContext.Entry(target).CurrentValues.SetValues(message);
    }
 
    return dbContext.SaveChanges() > 0;
}

After updating our data, we can then create some Power BI reports and add individual components to our Dashboards, to create something like the dashboard below:

Notes:

• Some information had to be omitted, as this is a solution in production.
• Most part of the code was based on Richard diZerega's Yammer Analytics post.

Let me know if you have any doubts... happy coding =)

_api/me/getrecentdocs stopped working! How can I replace it?

Hi there,

So, as it seems, one of our favourite endpoints stopped working. This morning I had a call from one of our clients saying they were unable to view their recent documents in the homepage.

I did a quick investigation and I faced this strange error when trying to call my-site.sharepoint.com/_api/me/getrecentdocs:

So I started to read about this and it seems that this API stopped working a few days ago (guess nobody noticed before!?). Waiting for a (possible) fix was not an option, so I started to develop a workaround for this.

Here's the code of our previous solution:

        var uri = String.format("{0}/_api/me/getrecentdocs/?$top={1}&$filter=startswith(LinkLocation, '{2}')&$select=FileName,LinkLocation,Application", serverUrl, rowLimit, encodeURIComponent(belowWebUrl));

        // Query
        jQuery.ajax({
            url: uri,
            dataType: "json",
            type: "GET",
            headers: { "ACCEPT": "application/json;odata=nometadata" }
        })

When the request was completed (.done), we were getting the array of object like this:

        // Render results
        var arrayOfDocuments = result.value;

For each object within the array (d), it was simple to get property values, using "d.PropertyName". Ex: d.FileName

This solution was really simple, but since it isn't working any more, let's jump into the workaround!

Solution (using the search api):

Let's build our request url, passing the base Uri, query text, query template, selected properties, row limit and sort it descending, using the last modified date (getting the most recent on top, to achieve the same result).

        var baseUri = serverUrl + "/_api/search/query";
        var queryText = "querytext='*'";
        var queryTemplate = "querytemplate='(AuthorOwsUser:{User.AccountName} OR EditorOwsUser:{User.AccountName}) AND ContentType:Document AND IsDocument:1 AND -Title:OneNote_DeletedPages AND -Title:OneNote_RecycleBin NOT(FileExtension:mht OR FileExtension:aspx OR FileExtension:html OR FileExtension:htm)'";
        var selectProps = "selectproperties='Path,Filename,SPWebUrl'";
        var uri = baseUri + "?" + queryText + "&" + queryTemplate + "&rowlimit=" + rowLimit.toString() + "&bypassresulttypes=false&" + selectProps + "&sortlist='LastModifiedTime:descending'&enablesorting=true"; 
        

        // Query
        jQuery.ajax({
            url: uri,
            dataType: "json",
            type: "GET",
            headers: { "ACCEPT": "application/json;odata=nometadata" }
        })

Now, the way we are going to get our array will be a bit different:

        var arrayOfDocuments = result.PrimaryQueryResult.RelevantResults.Table.Rows; 

In order to have the same easiness on accessing the object's properties (d.FileName), we will have to simplify the data structure, like this:

        // Simplify the data strucutre
        var arrayOfDocumentsSimplified = [];
        _.each(arrayOfDocuments, function (d) {
            var doc = {};
            _.each(d.Cells, function (c) {
                doc[c.Key] = c.Value;
            });
            arrayOfDocumentsSimplified.push(doc);
        });

And that's it! Our most recent documents are showing up again :)

Hope you find it useful!

RS

Query office graph in Office Add-in using SharePoint search

I've been playing around with Office add-ins lately, so I decided to write a post about it.

In this example we are going to login to SharePoint using Office 365 APIs, as described by Richard diZerega in this blog post.

To authenticate, we will perform a manual OAuth flow, which was really well described by Chaks here.

Transcribing Richard's description on the authentication process:

"Here are the high-level steps for this flow and cross-window communication:

1. Check for a user cookie (which maps to a refresh token in a database)
2. If the user doesn't have a cookie…generate a new GUID and store as cookie
3. Launch the OAuth flow with Azure AD in a new window (passing the GUID as reference)
4. Use the authorization code returned from the OAuth flow to get access and refresh token
5. Store the refresh token in the database with the GUID user reference
6. Prompt the user to refresh the Office app (which can now lookup the refresh token by the GUID user reference that is stored in a cookie)
7. Use the refresh token in the app to get resource-specific access tokens for data retrieval"

Here is the script that is launched for unknown users:

And here is the OAuthController for managing the authorization code response from Azure AD: 

To get the access token using the code retrieved by the authentication flow, the following code will be executed:

All the values must be defined in the web.config, as we're retrieving them using our 'SettingsHelper' class:

After the last action, a view will be retrieved, informing the user to close the prompted dialog. Nothing new till now, all of this was already described by Richard.

Since we're authenticated now, let's start building our File's class to store the values we need:

Now we can create our FileRepository class, to implement the method to get the files from SharePoint using search API...

Our 'GetFiles' method will receive a token and a parameter which will indicate what do I want to retrieve. In order to summarize, I will explain the "My Work" query:

query = "/search/query?Querytext='*'&Properties='GraphQuery:AND(ACTOR(391380\\,action\\:1003)\\,ACTOR(391380\\,OR(action\\:1003\\,action\\:1036\\,action\\:1037\\,action\\:1039)))'&selectproperties='Title,Path,ViewsLifeTime,LastModifiedTime,SiteID,WebId,UniqueId,SecondaryFileExtension,SiteTitle,SPWebUrl,ServerRedirectedURL,EditorOWSUSER'";

• /search/query?Querytext='*' - We're using the search api and defining the text as '*', which is any text.
• &Properties='GraphQuery:AND(ACTOR(391380\\,action\\:1003)\\,ACTOR(391380\\,OR(action\\:1003\\,action\\:1036\\,action\\:1037\\,action\\:1039))) - Here we're saying that we want to specify properties of a graph query (using actor(s) and action(s)). In this example I'm using my own Actor ID "391380", since we're getting access using app permissions, it won't work if I define the actor as "ME".
• &selectproperties='Title,Path,ViewsLifeTime(...) - finally, let's bring the properties we need (specified in the File class).

After our request being processed, we'll store the response relevant information:

With all the business logic done, let's talk about controllers and views...

Controller:

Our controller will get the stored token and get the files using the token and a parameter to indicate that I want "My Work" files (Created and/or modified by me)

View:

Let's call the Index action in our view with a fancy button...

Aaand it's done! A little bit of creativity and you can, for example, to have Delve in an Office add-in:

Happy coding!

Downloading files from OneDrive using the Office 365 API (MVC add-in)

For this example I used the "Office 365 Starter Project for ASP.NET MVC" developed by Microsoft, which you can find here, including all the instructions to set it up.

This sample uses "the Office 365 API Tools to demonstrate basic operations against the Calendar, Contacts, and Mail service endpoints in Office 365 from a single-tenant ASP.NET MVC application.", so when you click in "My Files" button, you get a list of all files and folders within your personal OneDrive and the capability of deleting them. 

What I'm going to demonstrate is how to extend those capabilities, by adding a new link (Download) which will allow you to download the files you want. 

First, we have to create the method to download the file in our FileOperations class, located inside the "Helpers" folder.

Then, we have to create our "Download" action in the file controller. This action will be a FileStreamResult, as we want to send binary content using a Stream instance. 
Finally we will return the result of type File, passing the Stream instance and the file name. 

Note: In order to dynamically retrieve the item content type, we will use MimeMapping.GetMimeMapping method.

To finish, we need to edit the view and add an action link to our controller. This action will be displayed only when the item is a file.

... The result:

Happy coding ;)