Use Power View report exported from SharePoint in Excel – #excel #powerview

One of the nice features of Power View on SharePoint is the ability to export the report to PowerPoint and maintain the possibility to interact with the report directly in Power Point.

However, one interesting thing is that you can actually copy this functionality to Excel and interact with the Power View report in Excel.

Here is how you do it

Select the report section in the PowerPoint presentation you want in Excel

.

Right click the object and select Copy

 

And switch to Excel and paste the object into to the sheet you want the dashboard.

And the dashboard directly in Excel – 🙂

The object can be manipulated if you switch to the Developer tab and enter Design mode and select the properties of the object

 

Notice the InitParams property – If you change the AllowSectionNavigation from False to True – This will give you the Navigation arrows at the bottom on the object and the user can switch between the sections.

You might have to close and reopen the workbook to see the change.

 

 

 

 

Power View Maps differs from Excel and Excel services and how to solve it

I have created a model based on data from http://www.scb.se/en_/ to visualize the number of households in Sweden.

The data was split by municipalities and I could also find a list of municipalities and their link to Swedish regions – and then it was no problem plotting these in a power view sheet in Excel

And the user can double click the region to drill down to municipality level

Very nice – and I was ready to deploy the model to Sharepoint – Excel Services so the users could browse the model online.

But what a surprise when I double clicked the Stockholm region in Excel Services…

Suddenly I have a municipality in Unites States – Salem – it turns out that the map in Excel services ignores the map hierarchy information from the parent – so it picks the first Salem returned by the Bing Maps Service.

So I added a calculated field in my Map table where I combine Municipality and Country – and mark it as County in the Reporting Properties – Data Category.

And know it works in Excel Services as well when drilling down in the Stockholm Region

 

It works like expected in Excel services as well.

So you might consider to add the country to all your locations just to be on the safe side, when you don’t have latitude and longitude for the locations.

Specifying JSON Query in Power Query – Example Statistics Sweden

Not all data API provides you with the same help as Statistics Denmark (see a previous blog post link) where you get help to construct the URL to retrieve the data via a GET Request (http://api.statbank.dk/console#data)

In another project, I was interested in retrieving data about Sweden – and quickly found Statistics Sweden – http://www.scb.se/en_/. The site has a lot of interesting data and facts about Sweden and they also provide access the the data via and API (http://www.scb.se/en_/About-us/Open-data-API/)

Unfortunately the Swedes doesn’t provide the same help as their Danish colleagues, so we have to use a little Power Query magic to retrieve the data – J

Here is how its done.

 

If you click the example link to retrieve the population for 2011 and 2012 – http://api.scb.se/OV0104/v1/doris/en/ssd/BE/BE0101/BE0101A/BefolkningNy – you will get a JSON response but this will only contain the variables that can be used to query the result and no data.

So I started to search the documentation and found that in order to get the data you have to create a GET request to the API with a JSON Query to get data.

But how can we do this in Power Query.

The Help on Web.Contents() in Power Query doesn’t provide much help besides the remark about POST/GET under the content option field (http://office.microsoft.com/en-us/excel-help/web-contents-HA104112310.aspx?CTT=5&origin=HA104122363 – but I found the inspiration/solution via Chris Webb’s blogpost http://cwebbbi.wordpress.com/2014/04/19/web-services-and-post-requests-in-power-query/ – if I could specify the JSON Query as the Content it should be possible.

So lets try

First on the PowerQuery tab select “From Web” and insert the address – http://api.scb.se/OV0104/v1/doris/en/ssd

 

This will give you a list of all the different variables you can set via the API, but not the data

In order to specify the JSON Query we have to switch to the advanced editor and modify the PowerQuery.

First we need a variable that holds the JSON Query – but remember to replace the ” with “” as the text in the query has to be stated properly.

Then we can use the content variable in the call to the Web.Contents –by adding “, [Content=Text.ToBinary(content)]))” to call.

When we click ok – we get the data J

Then we navigate the JSON result by expanding the list, convert to a table and expand the different columns in the query.

 

The result is now a bunch of keys for the years and regions in Sweden. We could then create other queries to the API in order to get these key values but there is actually an easier way. We can ask the API to return the data returned by JSON query as CSV instead of JSON, and remember to modify the Source to only web.contents and not a Json.Document.

Example to retrieve the population for Sweden in 2013 split by region and ages

 

let

PostContents= “{

“”query””: [

{

“”code””: “”Kon””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”1″”,

“”2″”

]

}

},

{

“”code””: “”Alder””,

“”selection””: {

“”filter””: “”all””,

“”values””: [

“”*””

]

}

},

{

“”code””: “”ContentsCode””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”BE0101N1″”

]

}

},

{

“”code””: “”Tid””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”2013″”

]

}

}

],

“”response””: {

“”format””: “”csv””

}

}

“,

Source = Web.Contents(“http://api.scb.se/OV0104/v1/doris/sv/ssd/START/BE/BE0101/BE0101A/BefolkningNy”,[Content=Text.ToBinary(PostContents)]),

#”Imported CSV” = Csv.Document(Source,null,”,”,null,1252),

#”Removed Top Rows” = Table.Skip(#”Imported CSV”,1),

#”Renamed Columns” = Table.RenameColumns(#”Removed Top Rows”,{{“Column1”, “RegionKey”}, {“Column2”, “Age”}, {“Column3”, “Gender”}, {“Column4”, “Population”}}),

#”Split Column by Delimiter” = Table.SplitColumn(#”Renamed Columns”,”RegionKey”,Splitter.SplitTextByEachDelimiter({” “}, null, false),{“RegionKey.1”, “RegionKey.2”}),

#”Inserted Text Length” = Table.AddColumn(#”Split Column by Delimiter”, “Length”, each Text.Length([RegionKey.1]), type number),

#”Filtered Rows” = Table.SelectRows(#”Inserted Text Length”, each ([Length] = 4)),

#”Renamed Columns1″ = Table.RenameColumns(#”Filtered Rows”,{{“RegionKey.1”, “RegionKey”}, {“RegionKey.2”, “RegionName”}}),

#”Replaced Value” = Table.ReplaceValue(#”Renamed Columns1″,” år”,””,Replacer.ReplaceText,{“Age”}),

#”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,”+”,””,Replacer.ReplaceText,{“Age”}),

#”Filtered Rows1″ = Table.SelectRows(#”Replaced Value1″, each ([Age] <> “totalt ålder”)),

#”Replaced Value2″ = Table.ReplaceValue(#”Filtered Rows1″,” “,””,Replacer.ReplaceText,{“Population”}),

#”Changed Type” = Table.TransformColumnTypes(#”Replaced Value2″,{{“Population”, Int64.Type}, {“Age”, Int64.Type}}),

#”Inserted Custom” = Table.AddColumn(#”Changed Type”, “EndOfYear”, each Date.From(“2013/12/31”)),

#”Removed Columns” = Table.RemoveColumns(#”Inserted Custom”,{“Length”}),

#”Filtered Rows2″ = Table.SelectRows(#”Removed Columns”, each ([Population] <> 0)),

#”Changed Type1″ = Table.TransformColumnTypes(#”Filtered Rows2″,{{“EndOfYear”, type date}})

in

#”Changed Type1″

 

This result can be returned to the datamodel

And we can start using PowerView, PowerMap to visualize the population.

OBS – As the API returns data with Swedish number format – you have to change the Power Query local to Swedish (Sweden) in order for the Query not to fail.

 

And then we can start analyzing the data in a pivottable

 

Or in PowerView in Excel

 

Here is the scary scenario for men when turning 70 …. Looks very dangerous. And Power Map lets us plot by county easily.

 

Or use the new PowerMap filter function to create a heatmap of the regions with 0-3 year old

 

If you sell diapers or sell children’s shoes in Sweden, you can see where to focus.

You can download a copy of the workbook – from here, and let me know if you have questions or like it.

 

 

 

Fun with Power Query and public data from Copenhagen Open data

Copenhagen municipality is exposing open data via this site – http://data.kk.dk/ . There are some interesting datasets but also some quite strange ones – for example a dataset containing all the bollards in the city.

Even though the data seems irrelevant at least for me – it is fun to use Power Query to extract the data and then use Power View and Power Map in Excel to visualize the data.

During the process, I learned a nice feature in Power Query that you might find useful as well.

Retrieving the data

The data can retrieved as a CSV file via this link

http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:pullert&outputFormat=csv&SRSNAME=EPSG:4326

This data set contains the point for each bollard as a text

I could do a lot of transformation to extract the data but then I noticed that the url contained an parameter called Output format –

http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:pullert&outputFormat=json&SRSNAME=EPSG:4326

And by changing it to JSON – I could retrieve the data as a JSON document.

I then clicked the list of records to expand all records

Converted it into a table

Expanded the Column1

Expanded the Column1.properties to get all meta for the bollards

And then expanded the Column1.geometry to get the point information

This gave me a challenge because the coordinates was a list of values containing the Latitude and longitude and when expanding the column the list values will create two rows per bollard.

 

The useful Power Query tip

 

I didn’t want that as I wanted a separate column with longitude and latitude – but this can actually easily be done in Power Query.

Add a Custom Column and add the following formula

Where 1 refers to the row number in the list – Remember its zero based to 1 will be row 2.

And then I have to columns

The rest is renaming columns, deleting unwanted columns and one very important thing specify the Longitude and Latitude columns as decimal numbers otherwise Excel will import them into the datamodel as text values with no option of plotting them in Power View or Power Map.

And then we can start visualizing the data using Power View and/or Power Map.

One import thing about plotting the bollards in Power View is that it doesn’t handle many unique points (locations) – so instead I added the Road Name as location and then Power View will group them on the roadname – then add the Bollard ID in location as well and the user has a drill down option by double clicking the bubble.

In this case I double clicked the Dag Hammerskjölds Alle – with 239 bollards and can see all is placed on the pavement (fortov)

Another options is of course to use Power Map

 

In this case, I modified some settings in order to view the point better.

You can download the file from here – http://1drv.ms/1opZHgq

 

 

 

Create the leaderboard of the U.S. Open Golf Championship in Excel with Power BI #usopen #powerbi

Here is an example of how you can use Power Query and Excel’s Power BI features to create a leader board of the U.S. Open Golf Championship 2014.

First I found a website that had a leaderboard …

Then switch to Excel and activate Power Query and choose to Get external data from Web

The navigator will then give us the opportunity to select element from the page and I can see that Table 0 on the page contains the leaderboard table

A double click on the table will take you to the Power Query window and we can start transform the data.

 

In order to make the values ready to import into a table or directly into the datamodel I perform a number of transformations

  • The POS column – Search and replace the T for tied with nothing
  • The TO PAR, TODAY columns – Search and replace E to 0 to convert the equal par to 0
  • The columns R1-R4 and TOT – Search and replace – to 0
  • The column THRU – Search and replace FOR F to 18
  • The columns POS, TO PAR, TODAY, R1-R4 and TOT is the datatype changed to Number

The steps is the Advanced Editor is like this

let

Source = Web.Page(Web.Contents(“http://espn.go.com/golf/leaderboard&#8221;)),

Data0 = Source{0}[Data],

ChangedType = Table.TransformColumnTypes(Data0,{{“POS”, type text}, {“START”, type number}, {“CTRY”, type text}, {“PLAYER”, type text}, {“TO PAR”, type text}, {“TODAY”, type text}, {“THRU”, type text}, {“R1”, type text}, {“R2”, type text}, {“R3”, type text}, {“R4”, type text}, {“TOT”, type text}}),

ReplacedValue = Table.ReplaceValue(ChangedType,”T”,””,Replacer.ReplaceText,{“POS”}),

ReplacedValue1 = Table.ReplaceValue(ReplacedValue,”E”,”0″,Replacer.ReplaceText,{“TO PAR”, “TODAY”}),

ReplacedValue2 = Table.ReplaceValue(ReplacedValue1,”F”,”18″,Replacer.ReplaceText,{“THRU”}),

ReplacedValue3 = Table.ReplaceValue(ReplacedValue2,”-“,”0”,Replacer.ReplaceText,{“R1”, “R2”, “R3”, “R4”, “TOT”}),

ChangedType1 = Table.TransformColumnTypes(ReplacedValue3,{{“POS”, type number}, {“TO PAR”, type number}, {“TODAY”, type number}, {“THRU”, type number}, {“R1”, type number}, {“R2”, type number}, {“R3”, type number}, {“R4”, type number}, {“TOT”, type number}})

in

ChangedType1

Then we can name the query and select Apply and Close from the File menu.

 

And now I have the leader board in Excel

 

 

And to make it update automatically every minute you can set the connection to refresh every 1 minute

 

And then we can start use Excel and powerview to create over own design of the leaderboard.

 

And add databars to To Par column

 

You can download the model from here – Link.

Nice shortcut keys when working with Power View (SharePoint)

I am currently working on creating a reports in Power View on Sharepoint.

And here is a list of some keyboard short cuts I have found useful.

CTRL + M – Creates a new view (just as inserting a new slide in PowerPoint)

SHIFT + F9 – switches to Full Screen mode

TAB – Cycles through the different elements in the current view

SHIFT + F8 or CTRL + F8 – Will enable accelerator letters in the ribbon

ARROW UP/Down – Cycles through the different views

CTRL + F6 – Enters the Fields List

Here is a full list of the Keyboard Shortcuts – Link

Analyzing foursquare data using Excel Power Query and Power View

I have been on foursquare for a couple of years now, and have also created venue for my workplace where I am the administrator. This is required for making the report in this blog post but there are many API end points where you can access your own data to create reports on.

You can read a lot about the different possibilities her – https://developer.foursquare.com/docs/ .

I wanted to analyze the stats on my Venue – Knowledge Cube A/S – and used this endpoint to retrieve the data from Foursquare – link

You need to find the Venue_ID to retrieve the stats and you can do that by Search API – or by checking in on the venue and use https://developer.foursquare.com/docs/explore#req=users/self/checkins to find the venue id in the response.

So let’s get the data using Power Query – Switch to Excel and on the Power Query tab choose the From Web in the Get External Data group.

This will open up the Power Query window and you can construct/design the query.

The Foursquare api will be return as a JSON document and we can browse through the response using the navigator.

But first start by naming your query to something meaningful instead of Query1 – do that by double clicking the name and type the new one.

When we navigate through the response we can see the different elements we get returned for Foursquare.

And could choose to see the checkins by agebreakdown by simply clicking on “list” it will return a list of records and in order to extract those you convert it into a table.

Choose None as delimiter and choose ok

Expand column1

And you get a table with the Age groups.

In my example I want the top visitors so I use the navigator to go back in the reponse and Power Query will want me about this – but its ok in this case.

So to get the topvisitors I choose that from the stats and convert it into a Table.

Expand the Column1 to get the users

And then expand the user to get the meta data about the users.

In this case I didnt pick all but just a few informations that I wanted to visualize – Name, Photo, home city and number of check ins.

When I click ok in Power Query the data is returned to Excel as a table containing the top 10 visitors of my venue.

I then add this Power Query to the data model by click “Load to data model”

As I would like to have a picture of the person I construct a url to the profile picture – BE AWARE that you need to add “Original” between the prefix and suffix.

Be sure to check that the datacategory for the calculated column is set for Image URL

For the HomeCity column you should specify City as Data category.

Finally add a measure to SUM the Checkins.

Switch to Excel and insert a Power View Report

And after a few clicks you could have this reports showing the Top Foursquare visitors of your Venue.

PS – Haven’t spent time on renaming the column names but this should offcourse be done