Learn Excel from Power BI

Vijay Perepa


Within this eBook we will look at how we can use Excel from Power BI with a view of Power Query. We can learn Power Query from both Excel and Power BI, and we can implement it in both products too.

Within this eBook, we will look at how we can use Excel from Power BI with a view of Power Query. We can learn Power Query from both Excel and Power BI, and we can implement it in both products too. There are many things that can be learned from using Power Query in Power BI and then retrospectively implement in Microsoft Excel.

In this eBook companion to the Excel and Power BI Summit session, we will look at three key areas:

The first is dealing with unorganized data and using the Power Query editor in Power BI to clean the data, and then doing the same in Excel to demonstrate how you can use Power Query in Excel effectively.

In the second scenario, we will take some organized data and clean the data using both applications and build on this QnA, machine learning and natural language to ask questions of Power BI to get the answers that you need.

The third thing that we will look at will concern infographics and how you can use graphics to build rich graphs

Transforming Unorganised Data in Power Query Editor

In order to demonstrate how we can take an Excel spreadsheet and transform the data using Power Query, let’s first of all create a new spreadsheet with some data. In our example, we are going to provide sales data, with the year across the top, and two columns of data below called Target and Achieved. In the left column, we have created Product values e.g. P1, P2 etc.

The data in the center can be randomly generated for the purpose of creating test data by using the formula below, which will randomly create values between 400 and 5000:


Once we have created our spreadsheet we can save and close it, as next, we need to return to Power BI.

Transforming the Data in Power BI

Once we’re into Power BI, we can transform the data by using Power Query. We can open the Power Query editor by going to Edit Queries on the toolbar, and then selecting “Edit Queries.”

This will then launch the Power Query editor window and create a blank query. Before we can do anything here, we need to load some source data.

We can import data from a number of different sources by clicking on the “New Source” button in the ribbon, where it will display the most common data sources for you to select. If the option that you want isn’t displayed there, then you can click on “More…” at the bottom to open up the full selection.

In our scenario, we want to import the data from Excel, so when we select Excel it will launch a standard Windows open file dialogue box for us to select our spreadsheet. Just remember that you will need to have closed your Excel spreadsheet otherwise Power Query will tell you that the file is still open.

When we have selected the Excel Spreadsheet, the Navigator window will load which will present any sheets available in the workbook and will also allow you to preview the data that is stored within.

Before we import the data, though, let’s have a look at some of the issues which we can potentially see here.

First of all, notice that Power Query is rejecting the column headers and so has put its own headers in instead. It is also setting null values in the cells where we legitimately have no data, and also the data is sat under two headers. So, we can see that, at the moment, the data isn’t in the greatest shape and needs some work done to it.

When we click ok, this data will be loaded into Power Query so that we can now work with it. Let’s think about what we want to do with this data, i.e. do we want to transpose this data so that we can get P1, P2, P3 and use them as headers instead of the dates.

To do this, I need to select the column which I want to transpose, so in this scenario, it will be column A. Once our column is selected, we need to go the Transform tab on the ribbon and select Transpose.

You will notice now that P1, P2, etc. are all now displayed across the top of the data table; however, our columns are still called Column 1, Column 2 etc.

We actually want our columns to be named after the products, which we can do by promoting the first row to be the column headers. This is really simple to do, because on the Transform menu, we also have a button to “Use First Row as Headers”

Clicking this button will take the values that were contained in Row 1 and move them into the Column names.

The anomaly here is that we still have a couple of columns called Column 1 and Column 2, so we can manually rename them to a more relevant header by clicking into them and editing them. In this scenario, we can change Column 1 to be called Year, and Column 2 to be called Type.

At the moment, our data still isn’t completely clean as there are several null values within the Year column when realistically, there should be two entries for each year. One for the Target, and one for the Achieved. Again, rather than manually doing all of this, we can use Power Query to help us to populate those null fields. By using the right click on the Year column header, we bring up a number of data options, one of which is fill. Fill has two sub-menu options there, fill down and fill up. This will determine the direction in which the data is filled, so in our case we want it to fill down.

Once the fill down option has been selected, it will take the relevant year and copy it into the null field directly below it.

In a few easy steps, we have now created a clean set of data.

What we will do next is to select all of the product columns so that we can reduce the data down into a much more consolidated table of data. So, if we highlight each of the P1 – P16 columns, and then right-click again, I can perform an un-pivot action which will reduce the number of columns into a single column of data. As you can see in the screenshot below, we still have all of the same data just reduced into four columns.

The last thing that we will do here is to change the header of the unpivoted column, which has been called Attribute, to Product. We could continue to manipulate this data, for example, having the target in one column and the achieved in another which can be achieved by creating a pivot column.

In this type of transformation, we can highlight the column that we want to work with, i.e. Type, and in the Transform tab of the ribbon, we can select Pivot Column.

When we select Pivot Column, we will be presented with a dialogue box that will ask us what values we want to use for our new columns. In this case, we are going to select Values, because we want to take the values that are in our Pivot column and use them as column headers.

We will now see that our table has been completely transformed into a really clean set of data, showing us Years, Products, Targets, and Achievements.

Now that we have gone through the exercise in Power BI, we can now look to repeat the same exercise in Microsoft Excel.

Transforming the Data in Excel

Before we start trying to do any manipulation with the data in Excel, the first thing we need to do is to format the data as a table. So, to do this, we need to highlight all of our content, select the Data tab within the Excel ribbon, and then select From Table/Range.

This first step is really important because unless the data is in a table format, then we can’t use it with Power Query.

In this scenario, we will click OK on the dialogue box which appears because we want to highlight some key differences to what we saw in Power BI when the Power Query editor loads. The biggest difference is that Excel has automatically promoted the first row of data to be the column headers, which when we used Power BI, we needed tell it to do that.

If we don’t want these headers in Power Query, then we can demote the headers in a similar way to how we promoted them before. We can just simply go to the Transform tab on the ribbon, and this time we use the drop-down arrow next to “User First Row as Headers” to show the option “Use Headers as First Row.”

This will now move the years out of the column headers, and back into the first row of the data table, just like we had in Power BI. We can then start to follow the same steps as before, by transposing the data, and then promoting P1, P2, P3, etc back to being the column headers.

The difference here, however, is that we don’t have Null values within the year column; instead we have the old Column names, e.g., Column 1, Column 2 etc.

So how can we deal with this? The first thing we can do is set the data type on Column 1. Our year values are whole numbers, so we should set the data type to whole number. We do this by right-clicking on Column 1, hovering over Change Type and then selecting the relevant type i.e. Whole Number.

You will see a dialogue box warning you that there is already a type conversion associated with the column and asking you if you want to replace it. In this scenario, we will click replace; however it will highlight errors as half of our field values are text.

We need to clear our errors, and for this, we can simply replace all instances of “Error” with a value that we actually want. To do this we right-click on the column header and select Replace Errors. This will prompt us to define what we want to replace the errors with, which in our case will be replaced with null.

When we press ok, all of the instances of Error are now replaced by null.

We now have the data in exactly the same state as what we did in Power BI, so we can now follow exactly the same steps to continue to transform and clean the data.

The only thing we have left to do here is to take the table of data back to Excel without using Power Pivot. All we need to do is, go back to the Home tab and select “Close and Load To” which will switch us back into Excel.

Once it has switched back to Excel, you will be prompted for where you want to insert the table, e.g., into the existing worksheet or a new one.

There are other actions that we can undertake while in Power Query to ensure that we are cleaning the data as much as possible.

Other transformations in Power Query

The first action that we will look at in Power Query is the ability to remove a column.

Removing Columns

If there is a column of data that we don’t want to include within our data, then we can simply right-click a column and remove it. The key thing to understand here is that when you remove the column, you are not deleting the data, that still remains in the data source. All you are doing is removing it from the queue and therefore helping Power Query to be a little more efficient by only dealing with relevant data.

Adding Columns

There are times where we will need more data to be added to the query, which doesn’t necessarily already exist within the data source. This can be achieved by selecting the column which we want to transform, and then by clicking on the “Add Column” tab on the ribbon.

As an example, if we used Column From Examples, we would add a column to the extreme right of the data table, and from there, we can create a new data column. But we need to provide an example of the data that is going in there, so using the previous data set, if we want to have FY appended to the year, then we would enter the example as FY 2015 (2015 being the value from the year column).

Reorder Columns

If we want to, we can click and drag our column ordering around to make sure that our data is displaying in the correct place.

Transforming Dates

If we had a date being displayed but we wanted to express it in a different way, then we could change it to show the year quarter which it falls into. So rather than have the date displayed as 02/01/2019 we would have it shown as Q1.

In order to do this, we could, first of all, duplicate the existing date column, since we still want the actual date within the query by right-clicking the column and selecting Duplicate Column. Once that’s done, we can do a basic transformation such as transforming the date to show the month name.

We don’t necessarily want the full month name; however, we may only want the first three characters, for example, January would be expressed as Jan. So, what we can do here is split the column based on a number of characters, and tell it to split Once, as far left as possible.

As it has split the column, you will see that there is another column created as well with the residual text left over. You can remove that column as it is not needed.

In the same way, we can create a column to show just the month number. To achieve this, again we will duplicate the date column and then select the transformation to get the month number so that we get a value between 1 and 12 for the month.

We can then do the same again, but this time select transform to Quarter, however, this will always show the Quarter number. So, we can use the Add column from example technique which we discussed earlier to add the Q into the data, so we have Q1, Q2 etc.

The key thing from this section was to highlight that you can use Power BI to quickly and easily learn more techniques within Power Query, which you can then also apply to Excel.

Creating a Report using Q&A and Machine Learning

In this section, we will look at how we can create quick reports within Power BI using QnA and machine learning and natural language in order to ask Power BI for the report information. The first thing that we need to do, however, is create what is called a Measure.

Creating Measures

A Measure is a calculated field that is kept in the memory of Power BI. We can create the Measures within the table which stores our data, but that is not considered good practice. Instead, we can create a dummy table of data, to store our measures, within Power BI by going to the Data group of the home tab and selecting Enter data.

This will bring up a Create Table dialogue box, which will allow me to enter values if we choose. However all we need to do in this example is to rename the table to “Basic Measures.”

The table will be created and will be displayed on the right-hand side under the Fields header. All we need to do here to start recording the measures is to create it in the table, by clicking on the ellipsis next to the table name and selecting New measure.

Once the measure has been created, we need to define what the measure is, so we can use the formula bar to give it a correct name, e.g. Total Revenue, and then use a formula to give it a value.

One key tip is to change the icon for the measures table to make it slightly more obvious what it is. To do this, we need to hide the default column, named Column 1, so that only our measure column is still visible. Click on the ellipsis next to Column 1 and then select hide. Then if you collapse the entire Fields blade, and then expand it again, you will see that Basic Measures takes the icon associated with the first column.

We can then repeat the actions to create additional measures to create as many as we need. In this example, we have created measures for the Total Customers, Total Duration, and Total Students.

Once these measures are in place, we can start to write questions for Power BI to answer and provide us the information, e.g. what are the top 5 customers by revenue as a donut chart.

Asking Power BI for a Donut

The first thing that we need to do in order to create these natural language interactions with our data, is to add the QnA visualization onto the Power BI canvas, and then writing the question.

When this is on the screen, you can then type in the question, and the data will automatically be displayed on the screen as a result e.g. top 5 customers by total revenue as a donut chart.

This can then be easily changed to a permanent visualization by clicking on the icon in the top right of the visual, next to the cog. Once the final visual is on the canvas, you can then start to edit the visualization format in the normal way, by changing colors, font sizes etc.

Asking Power BI for a Map

In exactly the same way as we just created a donut chart, we can also apply the same thinking to create a map, for example, we could place the QnA visualization and type “total revenue as map by city”

There are lots of visualizations that you can create in this way, and from there, you can format your visualizations so that all of your data is clean, clear and easy to understand.

Creating Infographic Charts in Power BI

In this section, we will look at how we can create custom visualizations using any type of image, but first, we need to access the Power BI marketplace to import a custom visual. This is the ellipsis (…) at the bottom of the Visualization toolbox. We also have the option here to import from a file.

There are a number of additional visuals available from here, which you can quickly and easily add, but the one that we’re interested in at the moment is the Infographic Designer. To add it, simply locate it and click on the yellow Add button.

Once it has appeared in your toolbox, you can then add it onto your canvas.

You can then configure the visualization as you can any others, by dragging the fields from the Field selector, on the right of the screen, into the relevant properties. In the first instance, this will then render a basic chart on the screen, which isn’t really the most interesting way of presenting the data.

We can go out onto the web and find a picture that we want to use to bring our visual to life, in this example, we are using a graphic of a shampoo bottle. When we hover over the visual, a pencil will appear allowing us to edit how the data is represented, by allowing us to upload our own picture. This will launch the standard open file dialogue, allowing us to choose our own picture.

As well as being able to put the picture in the foreground, we can also remove the background colour by using the dropdown in the Mark Designer, selecting the shape, and then removing the background. This then leaves us with just the picture.

Power BI is a very powerful tool, and to get the most out of it, you need to understand and build up your confidence with DAX. However there are small elements such as creating the infographics which allow you to take your Power BI reports to the next level.

0000-00-00 00:00:00

Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}