#LondonPUGDataVizLondon PUGLondonPUG

LondonPUGDataViz -2

by Prathy Kamasani
written by Prathy Kamasani

The dataset for the next LondonPUGDataViz is The Newyork time bestseller data from Wikipedia. It looks like only data between 2000 to 2018 years seem better. So it’s up to you, you can go for one year or ten years, your wish!

Dataset:

https://en.wikipedia.org/wiki/Lists_of_The_New_York_Times_Fiction_Best_Sellers

https://en.wikipedia.org/wiki/Lists_of_The_New_York_Times_Non-Fiction_Best_Sellers

When you click on the URL, it gives you the list. If you wish you can use power query to import data from all years or use the model from here https://1drv.ms/f/s!Avm7gbgZtlMlv3B9wc7d5cico0p- which already has Power Query to import data from Wikipedia.

Once you finish, please email your model to Prathy@outlook.com, we will showcase all models at next London Power BI User Group. You can find previous LondonPUGDataViz models here – LondonPUGDataViz

Prathy

0 comment
0 FacebookTwitterPinterestEmail
#LondonPUGDataVizLondon PUGLondonPUG

LondonPugDataViz – 1

by Prathy Kamasani
written by Prathy Kamasani

As some of you already know, we started #LondonPugDataViz at London Power BI User Group for completing two successful years. I am hoping LondonPugDataViz by all of us lead to more success in the years to come.

Thank you Mariusz and Sean for taking part in #LondonPUGDataViz, there were not many submissions, but I am sure it will grow over the time. Here are all submissions:

Dataset: https://github.com/fivethirtyeight/data/blob/master/bob-ross/elements-by-episode.csv

Submission by Mariusz Repczynski:

Submission by Sean Green:

Submission by Prathy Kamasani:

You can download all models here: https://1drv.ms/f/s!Avm7gbgZtlMlv2-1fgdQM6CWw8qh

Hope these models give some inspiration with your storytelling. If you are interested, why don’t you participate in our next one – https://prathy.com/2018/05/londonpugdataviz-2/

Regards,

London PUG

0 comment
0 FacebookTwitterPinterestEmail
#LondonPUGDataVizLondon PUG

#LondonPUGDataViz

by Prathy Kamasani
written by Prathy Kamasani

With inspiration from many DataViz blogs, especially from Story Telling With Data and DataDuo, I thought to start #LondonPUGDataViz.  Mainly to encourage interactivity of our User Group. Its been almost two years since we began our User Group, our next event is the Second Anniversary. Since we started, we followed the same format so that this new activity will be something exciting :).

Many of our attendees come to us saying how the Power BI User Group helped them. I am hoping this new activity will help them more. At our last event, many of our User group attendees were excited about this new challenge. So, the plan is every month we come up with an open dataset, and we ask our attendees to create something using Power BI. There are no limitations; Creativity is the limit!

At every event, we dedicate 30 min to showcase these reports. Recently I watched a video related to photography, where they mentioned how working in a group could help each other with the creativity angle. You will be surprised to see how different people shoot the same place with different perspectives and how that perspective entirely transforms the story behind the picture. In the same way, we are hoping this activity will encourage us to learn from each other with their different perspectives of storytelling.

People who know me in person knows that I like painting, maybe because of that,  for the very first LondonPUGDataViz, I’ve gone for Bob Ross Paining data set. I promise we go for a more sensible dataset for the next one. Not only because it is related to painting, but it is a small, simple csv. Something simple to start!

I am really hoping people get involved, if anyone out there wants to get involved, here is the dataset – http://bit.ly/LondonPugChallenge1 . I will write a blog post with all submissions, at least there will be mine, and I have few friends whom I am going to push to create some reports 😉

Till next time,

Prathy 🙂

0 comment
0 FacebookTwitterPinterestEmail
REPLACE ALL NUMERIC VALUES IN MULTIPLE COLUMNS USING #POWERQUERY
Power BIPower Query

Replace all numeric values in multiple columns using #PowerQuery

by Prathy Kamasani
written by Prathy Kamasani

A quick post more for my future reference. Hoping it helps some one out there!

While I was working on a Power BI report with Commonwealth Games dataset, I came across an issue where I had to replace all numeric values with a text value. There are some very useful blogs posts about replacing multiple values in Power Query. With my issue, I wanted to replace only numeric values. Instead of replacing each column, I tried to replace all!
My data looked like below:

I looked upon all available Replace functions of Text, Lists and Tables. As Imke mentioned on her blog, I decided to go for List.ReplaceMatchingItems. As I wanted to replace only numeric values, I used Value.Is(Value.FromText(_), type number)  to check the Type of value for each row. I replaced value only when my condition returns true. So I combined all required columns into a single column and used Text.ToList function to convert to a List. The condition I used for Type checking works great. But, when I converted the combined columns to Text, then to a List; each character in Text value converted into List as a different List Item. E.g. 12 would convert to {{1},{2}}. When I replace each by checking the data type, I will be replacing 1 and 2 individually, which will cause duplication in the result, like below:

 

I have to admit; I searched so much to figure out a way to covert Text value to a List using a delimiter.I felt silly after I looked at one my earlier post where I was using Text.Split() function. After a good number of failed attempts, I used Text.Split() function and then I used that List for replacements. And my condition Value.Is(Value.FromText(_), type number) works great.

 

Here is the script:

let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/2018_Commonwealth_Games")),
    Data4 = Source{4}[Data],
    #"Replaced Value" = Table.ReplaceValue(Data4,"#(cr)#(lf)"," -",Replacer.ReplaceText,{"April", "April2", "4 #(lf)Wed", "5 #(lf)Thu", "6 #(lf)Fri", "7 #(lf)Sat", "8 #(lf)Sun", "9 #(lf)Mon", "10 #(lf)Tue", "11 #(lf)Wed", "12 #(lf)Thu", "13 #(lf)Fri", "14 #(lf)Sat", "15 #(lf)Sun", "Events"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","#(lf)","",Replacer.ReplaceText,{"April", "April2", "4 #(lf)Wed", "5 #(lf)Thu", "6 #(lf)Fri", "7 #(lf)Sat", "8 #(lf)Sun", "9 #(lf)Mon", "10 #(lf)Tue", "11 #(lf)Wed", "12 #(lf)Thu", "13 #(lf)Fri", "14 #(lf)Sat", "15 #(lf)Sun", "Events"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value2", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"April_1", "Type"}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Renamed Columns",1),
    #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Type] <> null)),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows", each {"4 -Wed", "5 -Thu", "6 -Fri", "7 -Sat", "8 -Sun", "9 -Mon", "10 -Tue", "11 -Wed", "12 -Thu", "13 -Fri", "14 -Sat", "15 -Sun"} ,each if Value.Is(Value.FromText(_), type number) = true then "_" else "_",Replacer.ReplaceText,{"4 -Wed", "5 -Thu", "6 -Fri", "7 -Sat", "8 -Sun", "9 -Mon", "10 -Tue", "11 -Wed", "12 -Thu", "13 -Fri", "14 -Sat", "15 -Sun"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each Value.Is(Value.FromText([#"15 -Sun"]), type number)),
    ColumnNames = List.Select(Table.ColumnNames(#"Filtered Rows"), each Text.Contains(_,"-")= true),
    CombineColumns = Table.CombineColumns(#"Filtered Rows",ColumnNames ,Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Custom1" = Table.AddColumn(CombineColumns, "Custom", each Text.Split([Merged],",")),
  #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each List.Transform([Custom], each if Value.Is(Value.FromText(_), type number) = true then "GME" else _)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"April", "Type", "Events", "Custom.1"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Other Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ColumnNames )
in
    #"Split Column by Delimiter"

This may not be worthy to be a blog post especially when there are already some good posts covering the similar topic, but I spent a fair bit of time on this so thought of writing a post!

Keep Smiling,

Prathy 🙂

0 comment
0 FacebookTwitterPinterestEmail
Power BIPower BI TemplatePower Query

#PowerBI Audit log using #Office365 Management API

by Prathy Kamasani
written by Prathy Kamasani

I love automating things, I have read somewhere, a good developer always tries to automate things, but in my case, I am just lazy, so I try to automate! That is why I started exploring various Power BI related API’s. I was working towards a single resource which I can use to answer questions related to Power BI Tennant. During this process I have written different posts:  Power Bi Template to document the Power Bi Service, HOW TO FIND ALL POWER BI USERS IN AN ORGANISATION? I would recommend reading those posts as they cover how I created Azure Authorization Token a bit more in detail. So, after exploring all those API’s, I still had few unanswered questions like:

  • What’s happening on my tenant?
  • Who is doing what?
  • Users interest in?
  • Should I disable export or print etc.?
  • Does anyone ever users dashboards?
  • Using workspaces is a good idea?
  • Who updated the App?
  • Many more

These questions guided me to Power BI Admin Auditing; it has all the data I wanted. But, I didn’t want to do search and export every single time I wanted to do something, and I am rubbish with PowerShell, that’s out of scope for me, there I went for searching for another API …

Office 365 Management API:

Office 365 management API enables you to do many management tasks using API, like Audit search.

Office 365 Management API and Power BI:

I don’t know why I play with API’s, most of API service providers are very good at sending pointless error information, and I had no special luck with Office 365 API. Among all Office 365 API was a bit forgiving one. The root URL of the API is https://manage.office.com/api/v1.0/{tenant_id}/activity/feed/{operation}. All API requests require an Authorization just like Power BI API. This page  https://msdn.microsoft.com/en-us/office-365/office-365-management-activity-api-reference covers all information about requests and various parameters.

The Activity API supports the following operations:

  • Start a subscription to begin receiving notifications and retrieving activity data for a tenant.
  • Stop a subscription to discontinue retrieving data for a tenant.
  • List current subscriptions
  • List available content and the corresponding content URLs.
  • Receiving Notifications sent by a webhook when new content is available
  • Retrieving content by using the content URL.
  • List notifications sent by a webhook.
  • Retrieve resource friendly names for objects in the data feed identified by guides.

From <https://msdn.microsoft.com/en-us/office-365/office-365-management-activity-api-reference>

To get data from API, you first need to enable a subscription. For this, we need to POST requests using Power BI. This is where I stumbled most, Chris Webb has a fantastic blog post explaining POST Requests using Power BI – https://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/

Calling office 365 API using Power BI/ Power Query

I first created several parameters: TennantID, ClientID,ClientSecret,UserName,Password,publisher identifier. The first thing is to get authorization token. Getting started with Office 365 management API explains how authentication works

let

    //App Details

    client_id = client_id,

    client_secret=client_secret,

    //Authentication


    //URI's

    redirect_uri = "http://localhost:13526/redirect",

    token_uri = "https://login.windows.net/common/oauth2/token",

    authorize_uri = "https://login.windows.net/common/oauth2/authorize",

    resource="https://manage.office.com",


    //User Details 

    username=username,

    password=password,
 

   tokenResponse = Json.Document(Web.Contents(token_uri,         

                                [

                                    Content=

                                    Text.ToBinary(Uri.BuildQueryString(

                                    [

                                        client_id = client_id

                                        ,client_secret=client_secret

                                        ,username=username

                                        ,password=password

                                        ,resource=resource

                                        ,grant_type = "password"

                                        ,redirect_uri = redirect_uri

                                    ]))

                                    ,Headers=

                                        [Accept="application/json"]

                                        , ManualStatusHandling={400}

                                ])),

                                access_token = tokenResponse[access_token],

    token = tokenResponse[access_token]

in

    token

To make sure I have a valid connection, I create a query to pull all services on my Office 365

let

    getgroups_uri ="https://manage.office.com/api/v1.0/"&TennantID&"/ServiceComms/Services ",

 GetGroups = Json.Document(Web.Contents(getgroups_uri,

                [

                Headers= [                    Authorization="Bearer "& O365RefToken                    ]
                                  ]

                )),

    value = GetGroups[value],

    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "DisplayName", "Features"}, {"Id", "DisplayName", "Features"})

in

    #"Expanded Column1"

Then starting a subscription. URL to start subscription is https://manage.office.com/api/v1.0/”&TennantID&”/activity/feed/subscriptions/start?contentType=Audit.PowerBI. Below query did not work at all for a while and it started working suddenly, I genuinely don’t know why. But below is the query I used to start the subscription. When I run this script, it returns an error, but subscription was any way enabled.

let
   getgroups_uri ="https://manage.office.com/api/v1.0/"&TennantID&"/activity/feed/subscriptions/start?contentType=Audit.General",

   content = "{

        ""webhook"" : {

        ""address"": ""https://webhook.myapp.com/o365/"",

        ""authId"": ""o365activityapinotification"",

        ""expiration"": """"

        }

    }",

Auth = "Bearer "&O365RefToken,

     GetGroups =  (Web.Contents(getgroups_uri,

            [

                Headers = [#"Authorization"=Auth

            //              ,#"Content-Type"="application/json;charset=utf-8"

            ]
                ,

                Content = Text.ToBinary("")
            ]
                )),

    #"Imported JSON" = Json.Document(GetGroups,65001)
in
    #"Imported JSON"

You can view enabled subscriptions using following script

let
   getgroups_uri ="https://manage.office.com/api/v1.0/"&TennantID&"/activity/feed/subscriptions/list",


 GetGroups = Json.Document(Web.Contents(getgroups_uri, 
                [
                Headers=
                    [
                    Authorization="Bearer "& O365RefToken
                    ], 
                ManualStatusHandling={400}
                ]
                )),
    #"Converted to Table" = Table.FromList(GetGroups, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"contentType", "status", "webhook"}, {"contentType", "status", "webhook"})
in

    #"Expanded Column1"

Office 365 Management API for Power BI Audit Logging:

I am interested only in Power BI Audit, but you can use this to view other Audit logs too. As per API Schema, all Power BI Activities has RecordType 20, So I used following script to pull all Power BI Content.

let
   getgroups_uri ="https://manage.office.com/api/v1.0/"&TennantID&"/activity/feed/subscriptions/list",


 GetGroups = Json.Document(Web.Contents(getgroups_uri, 
                [
                Headers=
                    [
                    Authorization="Bearer "& O365RefToken
                    ], 
                ManualStatusHandling={400}
                ]
                )),
    #"Converted to Table" = Table.FromList(GetGroups, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"contentType", "status", "webhook"}, {"contentType", "status", "webhook"}),
    contentType1 = #"Expanded Column1"[contentType],
    #"Converted to Table1" = Table.FromList(contentType1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Content"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "GetContent", each GetContent([Content])),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Invoked Custom Function", {{"GetContent", null}}),
    #"Expanded GetContent" = Table.ExpandTableColumn(#"Replaced Errors", "GetContent", {"contentUri", "contentId", "contentType", "contentCreated", "contentExpiration"}, {"contentUri", "contentId", "contentType", "contentCreated", "contentExpiration"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded GetContent", each ([contentUri] <> null) and ([contentType] = "Audit.General")),
    #"Invoked Custom Function1" = Table.AddColumn(#"Filtered Rows", "Fn_GetURIData", each try Fn_GetURIData([contentUri]) otherwise null),
    #"Added Custom" = Table.AddColumn(#"Invoked Custom Function1", "Custom", each List.Count(Table.ColumnNames([Fn_GetURIData]))),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Errors", "Custom.1", each Table.Contains( [Fn_GetURIData],[Workload="PowerBI"])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = true)),
    #"Expanded Fn_GetURIData" = Table.ExpandTableColumn(#"Filtered Rows1", "Fn_GetURIData", {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType", "UserKey", "Workload", "UserId", "ClientIP", "UserAgent", "Activity", "ItemName", "WorkSpaceName", "DatasetName", "ReportName", "WorkspaceId", "ObjectId", "DatasetId", "ReportId"}, {"Id", "RecordType", "CreationTime", "Operation", "OrganizationId", "UserType", "UserKey", "Workload", "UserId", "ClientIP", "UserAgent", "Activity", "ItemName", "WorkSpaceName", "DatasetName", "ReportName", "WorkspaceId", "ObjectId", "DatasetId", "ReportId"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Fn_GetURIData",{{"Content", type text}, {"contentUri", type text}, {"contentId", type text}, {"contentType", type text}, {"contentCreated", type datetime}, {"contentExpiration", type datetime}, {"Id", type text}, {"RecordType", Int64.Type}, {"CreationTime", type datetime}, {"Operation", type text}, {"OrganizationId", type text}, {"UserType", Int64.Type}, {"UserKey", type text}, {"Workload", type text}, {"UserId", type text}, {"ClientIP", type text}, {"UserAgent", type text}, {"Activity", type text}, {"ItemName", type text}, {"WorkSpaceName", type text}, {"DatasetName", type text}, {"ReportName", type text}, {"WorkspaceId", type text}, {"ObjectId", type text}, {"DatasetId", type text}, {"ReportId", type text}, {"Custom", Int64.Type}, {"Custom.1", type logical}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Content", "contentUri", "contentId", "contentType", "contentCreated", "contentExpiration", "RecordType", "OrganizationId", "UserKey", "Custom", "Custom.1"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "UserId", "UserId - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "UserId - Copy", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"UserId - Copy.1", "UserId - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"UserId - Copy.1", type text}, {"UserId - Copy.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"UserId - Copy.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","."," ",Replacer.ReplaceText,{"UserId - Copy.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value",{{"UserId - Copy.1", "Name"}, {"WorkSpaceName", "WorkSpace Name"}, {"ReportName", "Report Name"}, {"DatasetName", "Dataset Name"}, {"CreationTime", "Creation Time"}})
in
    #"Renamed Columns1"

And we can create beautiful reports like this

Power BI Template:

I also created a template; you can download the template here –  https://1drv.ms/u/s!Avm7gbgZtlMlvkMAQEGMyQdLsfHu

When you open the template it will ask for loads of the parameters:

  • Get data for last 24 months  – A boolean value to pull data for last 24 hours or the given date. If this value is true, Power BI excludes values given in the next two parameters
  • AuditlogStartTime & AuditLogEndTime -Start Time of the Audit, Audit start and end has to be with 24 hours apart as per API documentation. and date has  to be in below format

YYYY-MM-DD
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS

  • TennantID – Office 365 Tennant ID
  • PubisherIdentifier – I have not used this, but it is recommended to use this for API calls
  • ClientID – Azure AD Client ID
  • ClientSecret – Azure AD App Client Secret
  • UserName& Password – User who has permission to view Office 365 Audit log

So does it answer my questions:

Well, I still have many unanswered questions, but this does help me in many ways. Using this template I can see the user behaviour, not only how many views etc. but interesting information about user activities. For example, in a certain workspace, if most of the users are exporting the report, then users are not happy with the way I presented the data.  Ideally, I don’t encourage users to be added to the workspace, using this I can see who are adding members to groups. I can see when a Power BI App created or updated. I can see how many users are printing the report, if many people are printing reports, probably I should not use many bookmarks, drill through functionality and add more Titles.

Summary:

There are many things I learned doing this process, PowerQuery Web.Contents can be used to POST and ZoomCharts introduced a logo underneath the visual to encourage users to buy their visuals 🙂

This entire model has many limitations, first of all, I can not schedule it as I am using two different API’s one to get Token and other to call API, but if you have Power BI Report Server, this can be scheduled. Next, data is always only for 24 hours, this is a limitation from API and I can go back to 7 days only.  Users need access to the Azure AD App and make sure Azure AD App has access to Office 365 Management API access. This page explains all steps – https://msdn.microsoft.com/en-us/office-365/get-started-with-office-365-management-apis . 

Hope this helps someone out there

Keep Smiling,

Prathy 🙂

25 comments
4 FacebookTwitterPinterestEmail
Power BIQuick Tip

Where is my Power BI theme file?

by Prathy Kamasani
written by Prathy Kamasani

Have you ever ended up in a situation where someone already did all the work to get the right brand colours, created a Power BI theme file and left the company. Then you go there to do some quick reports; business wants that long time signed off  theme that they used in other reports, but no one knows where that theme file exists? I have!  I was asked to create Power BI files which looked similar to the ones they had; existing ones had themes and backgrounds, but no one knows where they were.

Where are my Power BI theme file and background images?

By now, many of us know Power BI file can be renamed to a .zip file to see the contents. I previously blogged here how to zip PBI file and how we can use this for Drill Through functionality. So, I was pretty sure; if I imported a Jason file as a theme into Power BI Desktop, it should be saved somewhere inside. I can unzip the  model and find all imported files; I was not wrong 😊

So the steps to see imported files are simple:

  1. Copy the existing Power BI file
  2. Change file type to .zip
  3. Unzip it
  4. In the unzipped file, go to \Report\StaticResources\RegisteredResources
  5. That’s all; that’s where your theme file, background images or any other images you used in the model resides

Hoe this helps!

Prathy 🙂

0 comment
2 FacebookTwitterPinterestEmail
Power BIPower Query

Split multiple columns into rows in #PowerQuery

by Prathy Kamasani
written by Prathy Kamasani

Recently, I worked on an interesting problem. Datasource I was working with was a SharePoint list, working with SharePoint lists always turns out to be a difficult task than I first assumed. On top of that, with this particular data source; data was coming as semicolon delimited text. Basically, there were many columns like Mile Stones and Sub Milestones, their related data and so on.

Initially, when I looked at it, I thought, I could just split columns in a jiffy; apparently, I didn’t think through it. To cut a long story short, I had to find a way to split multiple columns into rows at the same time so that I can visualise it in a Gantt chart format.

Split multiple columns into rows in #PowerQuery:

Here is an example dataset. My data looked something like below; the real data set has far too many columns and much more data in each cell.

Name Milestones Sub Milestones
Prathy m1;M2;M3;M4;M5 s1;s2;s3;s4;s5

If I split Milestones and Sub Milestones one column after other by using Splitter.SplitTextByDelimiter function, this is how my data will look like. There is no way I can find the which sub-milestones belongs to what.( Well, there must be away, but def, not an easy way)

So, instead of splitting each column, I Split each row using function Text.Split and added it as a new column. Text.Split returns a list. Later, I used all these lists to create a column using Table.FromColumns() function which returned a table for me. When I expand the table, I have all my columns split nicely the way I want.

Power Query Script:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCihKLMmoVNJRyjW09jWy9jW29jWx9jUFChQbWhcbWRcbWxebWBebKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Milestones = _t, SubMilestones = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Milestones", type text}, {"SubMilestones", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Milestones],";")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([SubMilestones],";")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.FromColumns({[Custom],[Custom.1]})),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Custom2", "Custom.2", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.2",{"Name", "Column1", "Column2"})
in
    #"Removed Other Columns"

I did this with multiple steps for better readability, but you can fit all statements in a single statement as well. I kind of feel like, there must be an inbuilt function which does this, but I haven’t discovered it yet, if you knew, please do let me know 😊

You can download the sample file here – https://1drv.ms/u/s!Avm7gbgZtlMlvjUCCvb981Ga_AVB

Keep smiling 😊

Prathy

 

 

 

 

 

5 comments
4 FacebookTwitterPinterestEmail
Power BIPower BI TemplatePower Query

Power BI Template to document the Power BI Service

by Prathy Kamasani
written by Prathy Kamasani

Back in September, I promised I would write another blog post with a beautiful looking Power BI template. Even though I did the model long ago, I did not get a chance to write a post until recently one of my blog readers reminded me. Thanks, Martin 🙂

Quick Recap:

As I mentioned in my previous blog post Calling Power BI API using Power BI Desktop to document Power BI Service, we can get a lot of metadata from Power BI service. I feel like, it is essential to have an overview of what’s happening on your tenant. The only place I know to get this information is Power BI API. I used Power BI API a data source in Power BI, I needed a bearer token to get any data from Power BI Service, so I used “https://login.windows.net/common/oauth2/token” URL to get the token. Which mean I looked at two different URL’s which restricted me to manual refresh in Power BI Desktop than having an automated schedule. Hopefully, it will change shortly.

You can use this template to answer many questions like:

  • Show me all dashboards in my organization
  • Total number of reports in the organization
  • Users who have admin rights
  • Number of refreshes
  • Number of failures
  • Why some refresh failed
  • When was the last time a certain dataset refreshed, if it’s failed why
  • And many more…

Template to Document the Power BI Service:

You can download the template here – https://1drv.ms/u/s!Avm7gbgZtlMlqGprbKHS6zHd0aPf

Above download link has a zip file with PBIT file and a CSV file. CSV file has user details required for authentication. This blog post covers the approach I used to call the API – https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-register-app/.Once you updated “UserDetails.CSV” file with essential information, save it to your preferred location.

When you open the template, you will see a pop message asking Full path of the UserDetails file, e.g. C:\Users\xxxx\OneDrive\xxx\PBI Portal\PBI\UserDetails.csv. Enter file location and click on the Load button. That’s all; it should just work. Sometimes, you may get a timeout error. Most of the times refreshing again will do the trick if not go to edit queries and choose “Refresh All queries”.

The first page has drill through functionality enabled where it shows the lil driller icon. Are you impressed with above screenshots, want to know how I designed this model? Find it here – https://wp.me/p5u6Pw-AQ 

Summary

Remember, you can see all data points only if you have enough rights and level of access is maintained through Azure App. Also, you can only retrieve the data from workspaces where the username you used to call API has the access. This Power BI model gives exposure to a lot of information but not all. Using this model, I can find all users who have access to a Workspace but not to Power BI Apps. If you want to know all users on your tenant, use Graph API as I showed here – http://prathy.com/2017/11/find-power-bi-users-organisation/. Ideally, I would love to see an OData compatible API like Microsoft Graph API so that I can schedule the refresh. Also, I don’t think we have an endpoint to get Power BI Apps or username of data source connections. Power BI hides Username in data source connections, so this would be something handy to understand which username we used to set up the data source. You may have noticed many placeholders, I would like to add much more information to it, and design reports a bit nicer.  If I make any changes to the template, I will keep this post updated or write another with changes. Hope this helps someone out there! Thanks for reading 🙂

Keep smiling,

Prathy 🙂

 

10 comments
2 FacebookTwitterPinterestEmail
Power BIPower BI Template

How I designed “Power BI Template to document the Power BI Service”

by Prathy Kamasani
written by Prathy Kamasani

When it comes to writing, I have the least trust in my writing skills. I always wanted my blog to sound more like a personal journey than some docs, whenever I look back mostly it doesn’t. I hope at least this post will sound like a journey. I am not an expert with visualizations; this post is all about my thought processing when I designed this template. Presentation slides or Power BI templates are like babies, you like yours. So, I would really like to hear your feedback on my approach. Feel free to use the comments section or send an email to Prathy@outlook.com

For me, one of the hidden pleasures of working with Power BI is working with colours and choosing the layouts. Of course, primary usage is the ability to design something to promote data-driven decisions in an organisation. I am not encouraging anyone to splash lots of colours on to your report. Designing reports in Power BI remind me of my Digital scrapbooking days. An empty page looks like a blank canvas except, the gesso smell. While I work with business obviously I follow branding and give importance to the user input; when it comes to my templates like my previous blog post “How to find all Power Users” and “POWER BI TEMPLATE TO DOCUMENT THE POWER BI SERVICE“, it gets difficult I keep on change things. This Template is still an unfinished project. I don’t think it will ever get finished. However, I learned a lot while creating this one. Hopefully, I will use my learnings in the future blog posts.

Let’s look at the first Page:

I usually create background images, decide the layout and then use that image as back ground in Power BI. I used image background for this template too. The background colour and left pane are part of image background. I could use frame and all other things as an image as well. That will look and work better. Although for this template, I kind of went with the flow and it was too late to change to the image. Frames: The sole reason behind the Frames was just because I like Frames. Anyone who worked with Power BI can easily guess why I went for the frame which has a lil gap on the top. It’s because of that little bar you get on top of every visual in Power BI  https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/12247677-optionally-remove-focus-mode-box

I tried following a pattern with the look and feel of below image

Most of the times, I used left pane for slicers or any valuable information I wanted to show. I kind of followed inverted pyramid concept, but excluding the left pane, I suppose. Colours: I have various data categories and I wanted to differentiate all those categories with different colours. I looked for popular colour combos and went for the colours I used in the below image. You can find popular colour combos here – https://www.colorcombos.com/popular-color-combinations/2

Pushpin affect, that’s just to show off my digital scrapbooking tact. I used treemap visual and layered that with a pushpin image.

The left pane, there is a lot of information here. Bottom table is something unnecessary, I needed something to fill up that space so I added by creating a calculated table. It’s a bit misleading or needs a better naming. It has Failed, Failures
Today and I also have a dynamic title with failure information. I was duplicating the same content which was not a wise choice.

Next comes the drill through affect: I added a drill through icon to show users that it has Drill Through functionality. Not sure that’s doing any justice. When I googled for drill through, that was the first image came up in my search feed also it was a perfect image to add a background colour. As I wanted to follow my pattern, so I went for colours which were good to show black foreground. When the user uses navigates to drill through page, that page will have the drill through icon background colour theme. Each frame which has some characters are separate table visuals. I formatted them to show like labels.

First, I changed Column Headers Font colour and Background colour to the background I used in the left pane, next increased Row padding to 11 so it aligns nicely with my drill icon. Then bottom to top, I formatted them to send Back, so when the user right clicks to choose drill through it will not interfere. Only when a user clicks on the table, they will be able to see the column header interfering with previous one otherwise it looks nice. Then I used a ribbon chart to show the number of refreshes and number of failures by Date to get an overview. Another pattern I followed is on each page, I added Last refresh data time, obviously to show when was the last time report refreshed.

rest of the pages are pretty much similar apart from few extra affects.

Page Workspaces:

Noticeably assorted colour pattern. Then, I used Bookmarks functionality.

I added an effect like clicking check boxes. Basically, I had several visuals on this page, I created three bookmarks by selecting the visuals I wanted to show. Then on each relevant bookmark, I went for ticked checkbox image than an unticked one. So when users access it using Power Bi service, it will look like a proper checkbox. You can find more information about bookmarks here – https://docs.microsoft.com/en-us/power-bi/desktop-bookmarks

Page Datasets:

Two noticeable approaches here are, using images and numbers to highlight some measures. I used https://www.iconfinder.com/ to get all my icons and I think, they do stand out quite well. Then, I used slicers as a lil button with full frame and I edited interactions to work only with the chart. I also separated them with other visuals using lines so it kind of explains they belong to chart only.

Page Gateways:

I have a lot of placeholders here. I do want to add more information from Gateway Data source query. One noticeable thing I did here is, in the Gateway contact information, I was showing a green circle to show it is live.

I am using an image reference here. I created a measure with following DAX

Status = IF(Gateways[Gateway Status]="Live","https://upload.wikimedia.org/wikipedia/commons/0/0e/Ski_trail_rating_symbol-green_circle.svg",Blank())

Then, I updated table properties to use image height as 24, 24 is the minimum in the current version.

All rest of the pages are similar, nothing too exciting. Hope it gives some ideas to someone out there. Let me know what you think about it.

Prathy

2 comments
2 FacebookTwitterPinterestEmail
Power BIPower BI TemplatePower Query

How to find all Power BI users in an organisation?

by Prathy Kamasani
written by Prathy Kamasani

If you are the goto person for Power BI in your organisation, then I am sure at least once you may have been asked about Power BI users question, I get asked many times. Of course, I can go to Power BI Service, check each workspace and Power BI Apps and see who has access to what; not a productive way when I am working with many workspaces. Or as I blogged before, I can use Power BI API to get all users data, probably a better way when I am working with many workspaces. If I share content using Power BI Apps and given access using security group, It gets difficult to understand who has access to what, who is a member of what groups.

If you have enough rights, you can view users information and members of which groups in Office 365 portal, again not a very productive way.  However, by using Power BI and Graph API, we can create a report to show all users information. I will be able to see all users who have Power BI license. I can slice this data by Location, Team and many more. It’s not only Power BI; I can view all other licenses. For example, Power Apps and Microsoft Flow. Now I can easily see who has access and monitor users and license, If you want to know about Graph API and how to use it with Power BI, check out my earlier blog post – Access Microsoft Graph API using Power BI.

The Template:

By using Microsoft Graph API, I created a template, where you can see all users, their groups and their license details. You can download the template here – https://1drv.ms/u/s!Avm7gbgZtlMlqB12UF4WXFGzzgXk .

Using template:

When you open the Pbit file, you will see a pop up window with two parameters.

Enter these values Graph API URL = https://graph.microsoft.com/  and Graph API  Version =  beta. After a while, based on size, the template should get populated with data.  If you want to understand the logic behind the template, carry on reading …

Continue Reading
10 comments
2 FacebookTwitterPinterestEmail