Tag: power

Power BI Desktop – Working in Data Model and Creating Visualizations (part 3/3)

Posted on by Paresh Sharma

In this article I would be discussing how to work with data model and create visualizations. I would recommend you read through my previous two topics in this series, for better understanding of the examples we are working with here:

Power BI Desktop – Connecting to Data (part 1/3)

Power BI Desktop – Working in Query Editor (part 2/3)

Data Model

So we had a great progress while preparing a clean data in query editor. If you want to load the data into the data model now then click Close and Apply in the Home tab.

Once you close and apply changes, you will see that the interface changes, because now we are in the actual data model. So, we are now in the report view, where we can create our visualizations. You can toggle between report view and the data view. The data view will show you the actual data.

Now we will go to the Data View, and change the Country column. Power BI has a great functionality to show country specific data, but we will have to tell Power BI that the first column is Country and not some plain text.

To do that, select the first column and then on the Modelling tab select Data Category as Country/Region. Did anything change? You will note a new symbol against Country in the Fields area.

Creating Visualizations

Let us create some visualizations right now. For that we need to be in the report view.

To create a visualization, you can go to the Visualization column and select a visualization type by simply clicking it. Let us create a stacked column chart. You can see the style in the design area, which you can move around.

Let us increase the size of the tile and to add data, you can go to the Fields column and then drag the table fields into the corresponding field of the visualization. So for example, drag:

  • GDP into Value area.
  • Year into Axis area.
  • Country to the Legend area.

Now our chart looks better, but we have too much information in the chart. So, we might setup a filter to only see the Top 10 countries by the total GDP. To do this we keep the Visualization in the report area selected and scroll down in the Visualization column. Under the Country area change the filter type to Top N and specify the Show Items as Top 10, and By Value as GDP (drag-drop). Click Apply Filter.

Now you can see that we have only Top 10 countries in our visualizations.

But maybe we have a better way to visualize the data. You can change the current visualization by simply selecting from the different visualization types in the Visualization column. For example, lets choose a World Map type. You can see the visualization change.

We still see the Annual values.

If you want to see only the total values, i.e. sum of all the years included in the data model, then you can go to the fields column and unselect the Year. If we now click on the formatting area and enable Category Labels, then we can also see the names of the Top 10 countries.

This makes the visualization even better.

That is it. We have now finished the three series of articles discussing how to easily work with Power BI Desktop. Of course, there is more to Power BI. Keep following my blogs @Threadpunter and happy Reading.

Power BI Desktop – Working in Query Editor (part 2/3)

Posted on (Updated by Paresh Sharma

We will be discussing on how to work in query editor. It would be good to refer my previous article Power BI Desktop – Connecting to Data (part 1/3) to get a better understanding of the example we are using here.

So, time to look at the data in the query editor!

Fix the Headers

We are in the stage of data preparation right here and we see that the column names are not good. We see that a better column name would be the second row instead.

To do this you need to first delete the first row. In the Home tab click Remove Rows, Remove Top Rows. Specify 1 (row 1) and click Ok.

Now, click Use First Rows as Headers to set the columns. With that we are finished with the work on our rows!

Fix the Columns

You can select a column by clicking on the header and then navigate with the LEFT and RIGHT arrow keys on the keyboard.

We can keep all the columns but we don’t think we would need “Country/Series-specific Notes” column. So, we can go ahead and delete that. For that you can simply select the column and click Remove Columns on the Home tab. Similarly, you can delete some last columns such as “Column 14“.

Now you will notice that the are some year columns, we think they are not displayed in the best way!

Wouldn’t it be better if we changed simply into two columns, one column displaying all the years in all the corresponding rows of that column and the second column showing the GDP data!

To do that, select the first-year column, press and hold the SHIFT button on the keyboard and select the last column. This will select all the year columns. Now, on the Transform tab click Unpivot Columns.

Now you will notice that we simply turned, multiple columns into one single column where all the years are displayed in the rows of that column.

You can now rename the column names, by double-clicking the columns.

Now we do not want to load any unwanted rows in our data model, for example we can filter out all the non-applicable GDP data from the set.

Now we have a clean data, but the values in GDP column are formatted as text. It would be good to change the format to Decimal number. Lets go ahead and do that as well.

You can also, select multiple columns and click Detect Data Type in the Transform tab.

Now we have finished our work in the query editor, in this data preparation step. The next step now is to load this clean data into the actual data model and create visualizations.

In the following topics, check out what we can do with this data:

Power BI Desktop – Working in Data Model and Creating Visualization (part 3/3)

Power BI Desktop – Connecting to Data (part 1/3)

Posted on (Updated by Paresh Sharma

In the following article I want to show you how easy it is to use Power BI Desktop and how quickly you can create your first visualizations.

Get Data

Open Power BI Desktop and click Get Data function.

So, we need data, but we don’t have any! Why don’t we get some data from the Web?

To do this we can simply click on the Search bar any type Web. In the search result, select Web and click Connect.

Now we need to specify the URL. In this example, you can search for “IMF World Economic Outlook Database” on the web to get the GDP data. Please note that you might get a different result depending on the time you are looking for it, right now October 2017 version is the latest one that we will use in this example.

Click on the search result.

We would like to get data By Countries (country level data).

We would like to get the data for All Countries.

Click Continue.

Select Gross domestic product, current prices in U.S dollars and click Continue.

Click Prepare Report.

The next page will show you a table with result, we can download the result but in this case we would like to use the URL of this page.

You can copy the URL of this page (link) and paste it in the Power BI URL request field and click Ok.

This opens the Navigator, that allows us to select the information that we want to retrieve from the selected Web Page.

Select Table 1 in this example. To view the correct result, you can toggle between the Table View and Web View.

If you click Load, it will immediately load the data into the data model. In case you click Edit, then you will first open the Query Editor. The query editor is a separate tool in Power BI desktop that allows us to prepare data and then load the clean data into the data model.

Click Edit. Here you can see the query editor with the project data.

In the following topics, check out what we can do with this data:

Power BI Desktop – Working in Query Editor (part 2/3)

Power BI Desktop – Working in Data Model and Creating Visualization (part 3/3)

Power BI – Tools & Building Blocks

Posted on by Paresh Sharma

Power BI is not a single tool but consists of multiple tools and additionally building blocks.

Power BI Desktop is a local application, where we can create our datasets, visualizations and reports. We then publish the information to Power BI Service, which is a cloud solution. In there we can collaborate with our colleagues or add dashboards to our existing data. So, this way we have the data in the cloud. Now, if we use the third tool, i.e. Power BI Mobile, the mobile app will allow us to access the data that we loaded to Power BI service, from our mobile devices.

So, Power BI Desktop, Power BI Service and Power BI Mobile the three core tools. However, there is Power BI for Developers as well. This is made to customize the customer experience, which means that you can create your own customized visuals and you can also embed the Power BI results into your own existing apps.

Connect Power BI Desktop to Dynamics 365

Posted on by Paresh Sharma

Do you want to connect to hundreds of data sources, simplify data prep, and drive ad hoc analysis, produce beautiful reports, then publish them for your organization to consume on the web and across mobile devices. Well, then you can use the Power BI suite of business analytics tools that deliver insights throughout your organization. You can create personalized dashboards with a unique, 360-degree view of your business, and scale across the enterprise, with governance and security built-in. For more resources on Power BI, click here.

The following article explains how you can connect Power BI Desktop with Dynamics 365.

Copy URL from Dynamics 365

  1. Log in to Dynamics 365.
  2. Click Settings > Customizations > Developer Tools.
  3. Copy the URL under Instance Web API (save it for further use).

Connect Power BI to Dynamics 365

  1. Open PowerBI Desktop and click Get Data > Dynamic 365 (online) and click Connect.
  2. Enter the Web API URL and click OK.
  3. On the OData Feed, click Organizational Account.
  4. Click Sign in to enter your credentials. When you see the message “You are currently sign in.”, you can click Connect.
  5. On the Navigator, select the entities to load into PowerBI and click Load.
  6. The data and entity fields are loaded into PowerBI Desktop. You can now start building reports.