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

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)

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)

Data Migration Using Assisted Setup

Click here to view all NAV related blog posts.

With Microsoft Dynamics NAV 2017 you can use assisted set up to perform certain set up tasks based on the wizard. The data migration is one of these tasks. This is what we call the data migration using assisted set up. This is based on a wizard.

You can import from Excel, but you can also import from other finance solutions, for example QuickBooks. This is using the extension management in Microsoft dynamics NAV, which means that in real life you can use these extensions and also build extensions to use other finance software applications.

Click Departments/Administration/Application Setup/General/Assisted Setup.

Select Migrate Business Data and Start Setup. Follow the instructions:

The import process is the same as using Rapid Start. The only difference is that here we are working with a wizard.

Data Templates

Click here to view all NAV related blog posts.

One of the important tasks that you need to perform when setting up a new company in NAV is, of course, a data migration. You need to migrate a lot of data such as customer data, vendor data, item data and so on. To migrate data as efficiently as possible you can use Data Templates.

How to access Data Template?

On the RapidStart Services Implementer role centre, click Templates on the navigation menu.

Typical Process?

  • If we take domestic customers we could predefine the number of fields because all domestic customers might have the same customer posting group, might have the same language code, might have the same general business posting group and so on and that is the whole idea and that is the intention of using these data templates.
  • You will divide our master data that you have to migrate in different groups, such as based on item groups, based on customer groups and so on. And for each group you will then specify all the fixed fields and all the fixed values of the different fields.
  • When you have to migrate data using, for example, Excel, you can then link such a data template. This means that in Excel you can leave out all these fields from your Excel template and focus only on the variable data.