Home Power Query
Category:

Power Query

Another quick, more of “Note to self” blog post. While I was working with FIFA Football Model, one of the queries has a dataset like below

But what I wanted was group every three rows into one group. So I can do further transformations to achieve a result like below.

So what have I done, I created a blank query to create my duplicate values. As I knew I needed eight groups, I created a base list in my blank query with following M

= {“a”..”h”}

Then I added a custom column with List.Repeat()

{List.Repeat({[Column1]},3)}

 

Then I added an index column which I used to join my base query, which gives me the following result after expanding.

Grouping values were easy with above state of data

Here is full M code:

Base Query

let

Source = #"2018 FIFA World Cup Wiki (2)",

#"Removed Other Columns" = Table.SelectColumns(Source,{"Column2"}),

#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

#"Promoted Headers" = Table.PromoteHeaders(#"Removed Duplicates", [PromoteAllScalars=true]),

#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 1, 1),

#"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},Query1,{"Index"},"Query1",JoinKind.LeftOuter),

#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Custom"}, {"Custom"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Query1",{"Custom", "Round of 16"}),

#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Round of 16", "R"}}),

#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Custom"}, {{"Count", each [R], type table}}),

#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each [Count]),

#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"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)),

#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Custom", "Count"})

in

#"Removed Columns"

Referenced Query

let

Source = {"a".."h"},

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

#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each {List.Repeat({[Column1]},3)}),

#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),

#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Custom"}),

#"Expanded Custom1" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),

#"Added Index" = Table.AddIndexColumn(#"Expanded Custom1", "Index", 1, 1)

in

#"Added Index"

I genuinely feel like there must be an elegant way of grouping that data instead of creating another query, but then, this was the only way I could think off. Maybe with a bit of thinking, I could use List.Generate() to integrate and create duplicate values in the base query itself. Instead of using List.Repeat(), I can add a column by with list {1..3}, which also creates duplicate rows.  Like any other language, there are various ways but, I used List.Repeat() the very first time, hence this blog post. Hope it helps someone out there 🙂

Keep smiling,
Prathy 🙂

2 comments
2 FacebookTwitterPinterestEmail
REPLACE ALL NUMERIC VALUES IN MULTIPLE COLUMNS USING #POWERQUERY

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

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

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

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

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 …

10 comments
2 FacebookTwitterPinterestEmail

Recently I came across Microsoft Graph API through Power BI Custom data connectors documentation. Amazed by the amount of information provided by this API. I didn’t find an appropriate use case right away, but later I found a very interesting one “a Power BI template to find all Power BI users and their Groups”. In this post, I am going to cover a bit about Graph API and how to extract data into Power BI, more like an antecedent for my next blog post.

Microsoft Graph API is a RESTful Web API; we can use this to get access to data from the Microsoft Cloud services like Active Directory, Sharepoint, Onedrive and much more. You can find all documentation here – https://developer.microsoft.com/en-us/graph/docs/concepts/overview. What one of the main benefits of this API compares to many other Microsoft API’s is it has a single endpoint https://graph.microsoft.com.  I could use this as a data source in my Power BI Desktop, publish the Model to Power BI service and schedule the data source. Currently, we can not schedule Power BI custom data connectors, but once this feature gets implemented, I think it is better to access Graph API using custom data connector as documented here –  https://github.com/Microsoft/DataConnectors/tree/master/samples/MyGraph

For now, if you want you can extract data from this API using OData Connector. The best place I found useful to start with Microsoft Graph API is the Graph Explorer – https://developer.microsoft.com/en-us/graph/graph-explorer

Microsoft Graph now supports two versions. I found beta useful as it has more data than version v1.0. You can switch between versions easily in Graph explorer and see what data available in each version. A user needs the right level of permission to get access to the correct data, and these permissions are at very granular level. I don’t think; I understood permissions very well if you want you can find permissions information here – https://developer.microsoft.com/en-us/graph/docs/concepts/permissions_reference

You can use the Graph Explorer, to see what data is available to view, modify permissions or just to get an overview of the API.

Access Microsoft Graph API using Power BI

Microsoft Graph has a unified OData endpoint, so I can just use this as OData Feed data source connection, and with few simple clicks, I could import data int Power BI Desktop.

I just copied the API URL from Graph Explorer, pasted in the OData feed data source. I used organizational account authentication. That’s all; I can see all users 🙂

You can download a simple template here – https://1drv.ms/u/s!Avm7gbgZtlMlqCk02OFlwX2ANOfs 

Summary:

This is a fascinating data source, and it gets more interesting with Power BI. As I mentioned earlier, I started playing with this API to get an overview of all Power BI users available. One can get a lot of insights from this data source, hope you find it useful.

Prathy 🙂

 

9 comments
1 FacebookTwitterPinterestEmail

SharePoint is a killer; front end looks nice and beautiful. Once you start exploring behind the screens, that is when it shows the true colours. Whenever you view a list in the SharePoint mostly, you are looking at a SharePoint list View, so it does not show many underlined columns, not even columns with their real names. When you use SharePoint list as a source for a Power BI model, apparently it extracts all the information from the backend, which means you see far more columns than you expected and most of the times column names look obscure.

For example, look at following columns, how anyone can interpret s6hu meant to be column Jan-17

Original Name  Display Name
Title AI Number
cybf Aug-17
Notes1 Notes
s6hu Jan-17
a6ua Feb-17
dnxw Mar-17
uv0g Apr-17
yqkw May-17
ContentType Content Type

If you have faced this problem, want a quick fix, then just download this template. It should rename columns with display names wherever possible, or you may want to read rest of the post, you will know how I created the Template.

SharePoint List Power BI Template

Step 1:

First I created two parameters one for SharePoint URL and one for SharePoint list ID.

[ Description = "Sharepoint List URL" ] SPListURL = null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true];
[ Description = "Sharepoint List ID" ] ListID = null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true];

If you are not sure about getting List ID follow this URL or download this template – Get Sharepoint List ID’s. I used following M Code in the Get Sharepoint List ID’s template.

#"SharepointList IDs" = let
    Source = SharePoint.Tables(SPListURL, [ApiVersion = 15])
in
    Source;

Bingo, you get all lists from the given SharePoint site.

Step 2:

Once I know the URL and ID, I created another query with following M Code

 SPList = let
//SPListURL is the URL of sharepoint list
Source = SharePoint.Tables(SPListURL, [ApiVersion = 15]),
/* ListID is the ID of Sharepoint list */
SPList = Source{[Id=ListID]}[Items],
#"Keep only FieldValuesAsText column" = Table.SelectColumns(SPList,{"FieldValuesAsText"}),
RecordColumnNames= Record.FieldNames(#"Keep only FieldValuesAsText column"{0}[FieldValuesAsText]),
ExpandColumn= Table.ExpandRecordColumn(#"Keep only FieldValuesAsText column", "FieldValuesAsText",RecordColumnNames)
in
ExpandColumn;

I navigated to the list I want to import data from, chose FieldValuesAsText column only. FieldValuesAsText has text representation of all list data. Then I used function Record.FieldNames to get all values from Record. This makes it easy for future proofing. If any user changes columns in the future, Model will be clever enough to know the new column names. Then Expanded FieldValuesAsText column with the RecordColumnNames list.

Step 3:

I referenced the previous query and this time extracted all available column names using function Table.ColumnNames. Later I am going to use this query to join the query from Step 4.

 ListColumnNames = let
    Source = SPList,
    ColumnsNames= Table.ColumnNames(Source ),
    #"Converted to Table" = Table.FromList(ColumnsNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table";

Step 4:

I created another query with the same source as step 1 but in this query, I navigated to field ContentType and then Fields. Apparently, this is where SharePoint hiding all its secrets 🙂

 Title = let
    //SPListURL is the URL of sharepoint list
    Source = SharePoint.Tables(SPListURL, [ApiVersion = 15]),
    /*    ListID is the ID of Sharepoint list       */
    SPList = Source{[Id=ListID]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(SPList,{"ContentType"}),
    ContentType1 = #"Removed Other Columns"{0}[ContentType],
    Fields = ContentType1[Fields],
    #"Removed Other Columns1" = Table.SelectColumns(Fields,{"EntityPropertyName", "InternalName", "StaticName", "Title"})
in
    #"Removed Other Columns1";

Step 5:

I think this step is where the important stuff is.

First, I did a left outer join with ListColumnNames (Step3) and Title (Step 4) on EntityPropertyName and Column1. Then expanded and removed unnecessary columns. I ended up with Origin Name and Title (Display Name name in the SharePoint list). Then I created a custom column with a List of original column names.

In step 6, I want to rename all columns based on the display columns, but if we have duplicate columns or if there is already a column with the same name, Table.RenameColumns function will fail.

So I created a custom column with a list of original column names to see if the column already exists. Then I used List.Contains to check if the column already exists and I filtered out all columns which already exists.

In the end, I left again with Original Name and Title but excluded duplicates and columns already exist in the SharePoint List, then I transformed rows to list using Table.TransformRows function.

RenamedColumns = let
    Source = Table.NestedJoin(ListColumnNames,{"Column1"},Title,{"EntityPropertyName"},"Title",JoinKind.LeftOuter),
    #"Expanded Title" = Table.ExpandTableColumn(Source, "Title", {"EntityPropertyName", "Title"}, {"EntityPropertyName", "Title.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Title",{{"Column1", "Original Name"}, {"Title.1", "Title"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([EntityPropertyName] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Original Name", "Title"}),
    AddListColumns= Table.AddColumn(#"Removed Other Columns", "Custom", each Table.ColumnNames(SPList)),
    CheckNameAlreadyExists = Table.AddColumn(AddListColumns,"TF",each List.Contains([Custom],[Title])),
    #"Filter already existing column names" = Table.SelectRows(CheckNameAlreadyExists, each [TF] = false),
    #"Removed Duplicates" = Table.Distinct(#"Filter already existing column names", {"Title"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Removed Duplicates",{"Original Name", "Title"}),
    MakeAList= Table.TransformRows(#"Removed Other Columns1", Record.FieldValues)
in 

    MakeAList;

Step 6:

The final step for the template!

Again I referenced my query from Step1, SPList. Then used function Table.RenameColumns to rename columns as display names.  That’s all! Your lovely SharePoint list is ready for the transformations and analysis.

 #"Sharepoint List" = let
    Source = SPList,
    RenameColumns= Table.RenameColumns(Source,RenamedColumns,MissingField.UseNull)
in
    RenameColumns;

Summary :

Lately, one slogan I have heard among people is “We are in digital transformation”. The first place they start or probably the easiest place to start is moving all important Excel workbooks to SharePoint Online then analyse them using Power BI. But it was not that simple to make it work, at least not for me. I could write stories about it. I hope I will find some time to write about my adventures of successfully scheduling a Power BI Model with SharePoint list data source…

Sometimes you face irritating problems, If a user changes a column, it breaks the model, because of renaming columns to friendly names as it looks like in the Sharepoint List. I can’t imagine the frustration of a user when a model brakes with a name change error. Luckily I did not work with really wide lists. But with the very first model I designed with SP list, I realised this issue. My best friend google betrayed me, and I left with no choice other than exploring all the metadata I extracted from Sharepoint List.

This Template helped me to speed up the process. Now I don’t need to create Models for users; if they want they can design their own models, the way they want. It’s a win win 🙂

It’s not only me; there are other are others had similar issues

https://community.powerbi.com/t5/Desktop/Column-Name-Changes-when-Sharepoint-list-is-imported-in-PBI/td-p/183814

https://community.powerbi.com/t5/Issues/Sharepoint-list-connector-pulling-in-incorrect-column-names/idi-p/138924

I hope this helps someone out there. This is just a workaround; it worked great for me, hope it works for you too 😊

Sharepoint Power BI Template  – https://1drv.ms/u/s!Avm7gbgZtlMlqCHE55X7yJaDuBx0

Until next time,

Prathy 🙂

 

6 comments
2 FacebookTwitterPinterestEmail