Power BI: a pleasure to the eye! ;) This modern service is meant for analyzing your data and visualizing it in rich and interactive ways. After telling you we have added .NET development to our list of services, and making a review of .NET core and ASP.NET core 1.0, we have chosen Power BI as the next topic for our blog post.
Best Power BI tips from the interview with Charles Sterling
“Power BI is, I think, one of the most underappreciated tools that we have in our entire arsenal. This thing is so amazingly cool! We’ve been using it for a lot of reporting, and you’re going to show us some things today that are even cooler. We’re going to approach it from the standpoint of how web developers use Power BI in their apps to do some reporting.”
This quote is from Robert Green, the host at Visual Studio Toolbox, as he welcomes Charles Sterling of Microsoft. Charlie came to show us how to use Power BI in a developer’s work — for example, how to call REST APIs without writing any lines of code, or how to create interactive reports by embedding Power BI visualizations into web apps. We will feature the most useful moments from this interview here in the blog post for you.
Great news: freedom from code writing
Robert: “You’re gonna show us some things that Power BI just does, that otherwise you might have to write in code?”
Charlie: “That’s right. I think the last sample I wrote was four hundred lines of code to call one REST endpoint!”
When Charlie discovered it was possible to “call REST endpoints, parse the JSON and make it available in web apps” without writing code, he felt eager to share it. Jokingly, Charlie said that even the semicolon on his keyboard was gone because he had no need to write code anymore.
He starts his story from the beginning. “If you guys can see my screen, what we’re looking at is actually the Power BI desktop”.
Power BI Desktop is meant for shaping your data, Charlie explains. And if you want to make it available and share, there is Power BI Service for that.
Getting data from the web
One of the areas Charlie is going use is to show you how to get data from the web. You can actually grab any interesting table out of a website and bring it into the Power BI. According to Charlie, it’s possible to do almost with any table on any website.
So there is a URL right here that is from Wikipedia.
Wikipedia does a very good job of giving their tables proper names, which make sense. On most websites you get this sort of experience:
Doing regular updates both for the Power BI Service and Desktop every month, says Charlie, they have recently added such feature as the web view:
Let’s see an example. Charlie is kind of interested in presidential elections. He can just load the election figures without changes, or choose the “Edit” option and make some data transformations.
Charlie experiments with adding a percentage sign to the table or removing it, as well as changing the figures to a whole number.
All the steps performed are available in the right-hand list, so you can refresh. Then you can go back “Home” in the left top corner, choose Close and Apply.
At this point you can go to things like “Most recent polling,” expand and select, for example, “State nationwide,” and then drag it over.
Managing meetups
Since Charlie has been doing a lot of user group stuff lately, he is interested in managing meetups, where you can track all the user groups. Go back to “Get data,” then go to the Web. And, what most people don’t know, you can actually give the meeting endpoint with your key.
It’s very easy and doesn’t cost anything — you just say “give me a key” and that joins you to the developer meetup. That’s the RESTful API. It could be a public API, or could be your own.
At this point, it’s going out and saying “Hey, I’ve got some JSON here, what are gonna do with this?” And you are going to say, “I wanna make it into a table,” Charlie explains.
Go ahead and take a look inside the JSon, it’s pretty cool.
You see the elements of the parent root (or the parent node) have expended out. And if you scroll across, you will just see that some of these are just columns inside your JSON parent. There are child members. And once again it’s asking: wanna expand it all out?
Then scroll it across, go to “Groups” and do exactly the same thing.
At this point, do the exact same thing that you did before.
And now you’re done!
Let’s do something with this data. Open a brand-new page so we don’t have to fight with it.
Call this one “Meetup”, so you don’t get confused later, and expand it out. Let’s grab that map that you had before.
Make it a little bit bigger, and you’re gonna see what your meetups are at. Take “venue.lon” and “venue.lat” to see the distribution of the meetings.
Click “venue.name” and see how frequently they are meeting. Let’s put a table below it.
Pull out some of these like “group.name” or others. So here are my meetups that you go to on a regular basis. To get more details about, drag over “link”. It’s not a hyperlink, so you could do some data cleansing: Modelling - Data category - Change URL.
And one of my Charlie’s favorite visualizations is called Attribute Slicer. It looks like we don’t have it over here. Let’s click on “Import a custom visual:”
So let’s drag “Group.name” and “Venue.city”. And you see how busy with the meetups these cities are. Click on the city name to see just the information about it on the map. Or you can narrow your search to a word or even part of a word using the search box. Choose “Save as” in the left upper corner to save your results. Charlie saves it as “toolbox.pbix”. And now go back into your browser: this is the online instance or online experience for Power BI.
Publishing to web
The Power BI Desktop is free, but there are features in there that cost money. And it is one of these that Charlie shows next. It’s “Publish to web.” You need to have a pro license for it. It costs nine dollars a month, but E5 customers already have it as part of “Office 365.”
Сlick “Get data” and grab the file that you’ve been just working on — “Toolbox.pbix.”
By default, everything is secured inside Power BI. Nobody has access to it, except for whoever’s logged in (in this case, Charlie). You can share this, but it has to be at the group level, and they have to have Power BI. When you go ahead and say “share,” you just type in your email address, and they get an email and say “Yes, I accept.” But that’s not really useful for broadcasting your website to the planet.
So we’re looking at the report and going to File — Publish to web.
Click “create an embed code,” and the URL will work for everybody on the planet.
You can copy that to address bar, but there is also an alternative. Grab the embed code, go to WordPress, paste the embed code and click “Update.” And then you can see the report as part of the blog.
This could also be made available to any web app. For those who don’t make web apps, but, for example, desktop apps or mobile apps, Charlie says, there is a mobile client. It is downloadable from the store right now, so you can actually make this available to mobile apps as well. The team is also working on an UWP client.
In terms of security, the Publish to web feature lets you adjust some settings.
You go the Power BI settings, Admin Portal.
And the very first Tenant setting that you’ve got is “Allow sharing content to external users” and “Publish to web,” which you can enable or disable.
These are the best tips from Charlie Sterling. Inspired? As Robert Green has said in the end of the conversation, Power BI really lets you “create amazing reports and embed them in your web apps.” We hope you are ready and eager to do it yourself, and wish you luck with it!