Power Query is a Excel add-in that is developed by Microsoft which helps to load data from any external sources to excel.
Given that Power Query is just one part of the Power BI suite, it’s important to look at all of the components of Power BI so you can put Power Query in context, understand what it does, understand when you should use it and when another tool is more appropriate.
That’s because the first step in creating any kind of report or dashboard is to get hold of the source data for it. Power Query allows you to connect to a wide variety of different data sources, extract data from them quickly and easily, and define a series of repeatable steps to clean, filter, and otherwise transform your data before it gets loaded into Excel.
As stated earlier, in the beginning of this post, power query is a add-in in the Excel which allows us to take data from a variety of sources and then model and transform the data before loading it into excel. So, for this purpose we will have to install the Office 201o or later versions. Then you have to install the add-ins for this excel to enable the tabs Power Query, Power Pivot, Power Map & Power View.You can search in google for the add-ins and then download them.
Step 1: Open an excel Spreedsheet then you can see all the add-ins.
Here you can see the Power View, Power Query, Power Pivot etc on the ribbon as tabs.
Power View: Power View is a new feature of Excel 2013 that allows you to create attractive, interactive dashboards as new worksheets inside your workbook.
Power Pivot: The Power Pivot add-in still remains but only as a user interface. You have to use the Power Pivot add-in to be able to use certain, more advanced functionality, but you do not need it if you only want to perform basic tasks.
Power Map: Power Map is an Excel add-in that allows you to overlay geographic data onto 3-D maps.
To enable the Add-Ins follow the instructions and image below:
- If not available then you can click on the file tab then options.
- In the Excel options click on Add-Ins, in the add-ins manage dropdown select COM-Add-Ins and click Go.
- In the Com add-Ins Dialog box, make sure that all the add-ins are checked then click OK in the same dialog.
- Then click OK in the Excel options dialog box.
Step 2: After enabling the add-ins now click on the Power Query tab and then select a source for loading the data for any external or internal sources you wish for. I will be choosing data from a csv file named global_superstoreMPBI. You will be redirected to the Query Editor window there you can edit the column and the values before loading it into the excel sheet. You can split the column values by the delimiter, you can append the columns and also choose the no of rows to keep or discard. You can use the ‘Use first rows as headers’ if the headers of your table are place in the first row and the header names are labelled as column 1,2,… etc. You can use other tabs as Transform, Add Column or View to do the editing of the data from source before loading.
Step 3: After editing the data you can click on the Close and Load To button which is present on the extreme left of the Query Editor window. Select the Only Create Connection radio button and then select the check box Add this data to the Data Model and click Load.
Step 4: After loading the data to the model you can now manage the data model and then you click on the Power Pivot tab and from there you can manage the data model by clicking on the manage data model icon on the ribbon and a new Power Pivot window opens there you can click on the pivot table drop down and then the pivot chart then you will be asked to choose if you want the chart in a new worksheet or in the existing worksheet. You can select the way you want your chart to be in the dashboard.
Step 5: After you click on the pivot chart you will be prompted with Create Pivot Chart dialog box and select the Existing worksheet and click OK.
After you click OK you can see a chart created in the worksheet and PivotChart fields on the right hand side of the worksheet and from there you can drag and drop tables to the area below and then you can create different type of charts and visualisations using thePivotChart Tools which appears in the top of the ribbon besides the PowerPivot. The PowerPivot contains three more tabs i.e. Analyze, Design and Format.
In the Analyze tab, you can insert filter for the chart refresh and change the data source you add calculations in you chart.
In the Design tab, you can add the chart elements, styles, colors, chart types, you can also move the chart.
In the Format tab, you can change the shapes size style of the text elements insert shapes format the selection etc in this tab.
Step 6: In this step, you can choose the tables from your Pivot chart Fields option and then you can add it to the legends, filters, Axes or Values area which will show you the graphs.
In the image below you can see that the chart is now visible when we add the fields to the area we can then see the graph and we can change the style color design appearance of the graph using all the pivot chart tools. You can add the fields for the chart by just dragging and dropping it in either the chart directly or in the area below. You can also add formatting to the chart in the Format Chart area. Inside the chart you can see the legends and filters added in the charts. This is how you can create a chart in excel using the Power Query and Power Pivot.
Step 7: After preparing the chart with the help of Power Pivot and Power Query now we move on to the next component named the Power View. It enables us to create attractive, interaction dashboards as new worksheets inside the workbook. Now we can see how power view works with the excel.
After completing the chart you still have the data model in your workbook. So, you can click on Insert tab and then click on the Power View button in the ribbon. After clicking the power view button a new sheet opens with the name power view1 in that sheet you can choose the Power view tab now and then you can add the fields from the power view fields and then you can click on the map in the design button in the ribbon. and you will see that the map view can also be embedded in the excel sheet. You can also change the design and shape in the Format shape in the extreme right.
Here is what you do after clicking the power view:
- Go to the power view fields and there select the fields on which you want to build the view. Here I have selected the Country State and Quantity.
- In the sheet then you can see that the fields you have selected is shown in columns.
- Now if you want to see the mp view you can directly click on the map button in the ribbon or you can continue with the building of the table.
- After click on the map button you can see that the sheet is now changed in to the map view and there you can see the fields that you have selected.
- You can also add the Title but double clicking on the “Click here to add title”.
Step 8: Moving on to the next step that is now I will show the use of another component of Power BI that is an excel add-in. Go to the Insert ribbon and click on the Power map which is present right beside the Power View. Click on the drop down and then click on Launch Power Map.
After you launch the map you can see that the Power Map opens in a new window and there you can see the Power Map which is a globe actually here you can add the fields such as country, region, state, city, postal code, latitude, longitude etc. in the first step. Here the column country is added as you can see the map in the image below.
In the image below you can also see the properties where you can choose geography from the data model and add it in the field list of the layer1 you can also change the name of the layer by clicking on the pencil icon.
As you can see there are three tabs the layer manager the field list and the settings. using these three tabs you can manage the map. In the Geography and map level select the type field for the display. After your customization you click next on the bottom right corner of the workbook.
Step 9: After clicking on next you can see that the layers and the formatting you have added reflects in your map. Here I have added two layers one for the State and one for product category. You can also change the themes of the map. And if you select the map label then you can see the names of the country and state respectively. The layers you add are shown on the map as legends. You can remove and add the legends anytime you want by clicking on the cross button or add button.
In the next image you can change the type of graph for the layer in the Field List, choosing the fields for the value category and time.
After doing all this you can view the map as a tour, you can click on play tour to see the globe rotating as a moving image you can format the settings in order to change the way it is represented. Now you can save the workbook.
Here in this post we started with a Power Query which we fed into the Power Pivot data model which we used to create a Pivot Chart and then we created a Power View visualisation and then we ended the post showing another feature that is the Power map.