Getting Started With PowerBI: Analyzing Data and Sharing Insights

If we could do a show of hands for “Who’s heard of PowerBI?”, I’m pretty sure everyone would raise their hand at this point. However, as much as people have heard about it, there often remains some confusion as to how it functions and how to use it. In a time where analytics are no longer saved only for the Database Managers, it’s important to be able to take raw data, create insights, and share it in a collaborative fashion with the rest of your team. But before getting to the basics, it helps to start from the beginning.

What people may not realize is that PowerBI is based off the Power Pivot engine, which fewer people have heard of. Power Pivot is the language developed to work with large amounts of data to give you summarized tables. In terms of the timeline between the two, Power Pivot has had nearly a decade of development work since 2009, while PowerBI truly picked up most of its adoption in 2016.

However, that is only one half of the story. The other half rests with Power Query. Whenever you went to Excel and used the “Get & Transform” button, you were using Power Query. This allows you to extract data with filtering sources, transform it, and load it to the new data model. Once the data is in a reportable format, Power Pivot can then relate, expand, calculate, measure, and summarize the data in tabular form. After that, it’s ready to be turned into a visual format—in comes PowerBI.

To start, we will need to choose which data we want to use. For the sake of the example, we will be using Dynamics NAV and using inventory data, but you can choose whichever set of data that piques your curiosity.

First, we’ll need to create a link for which PowerBI can use to consume the data.

We’ll need the page number for this list, so go to your Items list in NAV. In the top left corner of the page click on the Drop Down Menu > Help > About This Page. Take note of the page number in parentheses in the Page Information section of the newly opened window.

Next, go to the Web Services area of NAV and create a new web service. In the Object Type list, choose Page and fill in the page number in the Object ID area. It will know it’s the Items page, so just fill in the Service Name and then click on the Publish checkbox. NAV will then create the link that PowerBI will use to consume the data under the OData URL field.

Since we need something to compare the data against, and for the sake of this example, go through the same process and create a link for Value Entries.

Load the PowerBI desktop application. Now you have to tell it where to go to get the data, which is where the previously made links come in. Click on Get Data > OData Feed. This is where you paste the links you previously made. One tip for this step is rather than pasting the full link, you can keep the link at a higher level which would include all of the OData feeds, and then choose from that list.

Copy the link up to the word “OData”, as seen below, paste that into the feed box, and click OK. It will give you all of the published OData feeds under that URL.

In the Navigator window that popped up, choose your fields. In our case, Items and Value Entries. Notice that on the right-hand side, you’ll see a preview of the data included. Click Load.

From here, you will find your lists on the right-hand side of the screen. You can open each of them to choose which values to include. It functions a lot like the Pivot table in Excel where you can choose which fields you want added. But before creating your visualizations, you first have to create a relationship between the two lists.

Click on the Relationships tab on the left-hand side and you will see there are no connections between the two lists, so we must add them. Click on Manage Relationships. If you click on Autodetect, it can figure out the relationships, but they’re not always correct so you’ll want to audit it.

You can also create your own by clicking New and choosing the two lists in the drop down menus in the next window, as shown below. Then highlight the two fields that match each other in the separate lists and confirm their Cardinality, whether it’s a One-to-Many relationship or otherwise, and click OK and Close. You’re now ready to begin your journey on visualizing your data!

So you’ve finished creating a dashboard that you now want to share with your colleagues. Doing so is as easy clicking Publish on the top of the screen. If you haven’t yet, you’ll be prompted to save your document. After that, you can pick which workspace you will want to publish to, at which point it will start pushing your dashboard to the cloud.

Your dashboard will then be accessible through a link that anyone can use. The difference between this and other ways of sharing large files, even currently, is that you would have to send it via OneDrive or one of a thousand other file sharing systems. It takes time to log on or create an account on them if you haven’t already, so being able to share a dashboard through an accessible link adds to Microsoft’s vision of having a collaborative environment.

As they say, a picture is worth a thousand words. For a more detailed walkthrough of getting setup with PowerBI and creating visualizations for your dashboard, you can watch my webinar titled, Getting Started With Power BI: With Great PowerBI Comes Great Responsibility.