There are three views in Power BI Desktop: Report view, Data view, and Relationships view. In Query Editor , you can build queries and transform data, then load that refined data model into Power BI Desktop, and create reports. The following screen shows the three view icons along the left of Power BI Desktop: Report , Data , and Relationships , from top to bottom.
The currently displayed view is indicated by the yellow bar along the left.
From Excel workbook to stunning report in no time
In this case, Report view is currently displayed. You can change views by selecting any of those three icons. In the next few sections, we take a tour through each in turn.
There are all sorts of data sources available in the Query window. For example, perhaps you want to help your sunglasses retailer target sales where the sun shines most frequently. Query contacts the Web resource, and the Navigator window returns what it found on that Web page. In this case, it found a table Table 0 and the overall Web Document.
How to use this guide
The Navigator window displays a preview. At this point we can edit the query before loading the table, by selecting Edit from the bottom of the window, or we can load the table. When we select Edit , Query Editor launches and a representative view of the table is presented. In the next section, we adjust the data so it meets our needs.
The process of adjusting connected data is called shaping data.
Get data from Excel workbook files
Sometimes adjusting means transforming the data — such as renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, and so on. The Query editor in Power BI Desktop makes ample use of right-click menus, in addition to having tasks available on the ribbon. Most of what you can select in the Transform ribbon is also available by right-clicking an item such as a column and choosing from the menu that appears. The original data source is not affected; only this particular view of the data is adjusted, or shaped.
The steps you specify such as rename a table, transform a data type, or delete columns are recorded by Query Editor , and each time this query connects to the data source those steps are carried out so that the data is always shaped the way you specify.
This process occurs whenever you use the query in Power BI Desktop, or for anyone who uses your shared query, such as in the Power BI service. Those steps are captured, sequentially, in the Query Settings pane under Applied Steps. For starters, most ratings were brought into Query Editor as whole numbers, but not all of them one column contained text and numbers, so it wasn't automatically converted.
We need the data to be numbers. If we needed to choose more than one column, we could first select a column then hold down SHIFT , select additional adjacent columns, and then right-click a column header to change all selected columns. You can also use CTRL to select non-adjacent columns. You can also change, or transform, those columns from text to header by using the Transform ribbon.
Note that in Query Settings , the Applied Steps reflect the changes that were made. If I want to remove any step from the shaping process, I simply select that step, and then select the X to the left of the step. Feel free to check out that page, or keep going in this document to see what you would do next. The next section picks up after the changes above are applied. That data about various states is interesting, and will be useful for building additional analysis efforts and queries. We need some way to associate state names with their abbreviations.
To summarize those steps, here's what we do:.
Use the first row as headers — since we removed the top three rows, the current top row is the header we want. This is a good time to point out that the sequence of applied steps in Query Editor is important, and can affect how the data is shaped. Rename the columns, and the table itself — as usual, there are a couple ways to rename a column, you can choose whichever you prefer. In this case we want to merge queries.
To get started, we select the query into which we want the other query to merge, then select Merge Queries from the Home tab on the ribbon.
How to Create a Pivot Table in Excel: A Step-by-Step Tutorial (With Video)
A NewColumn is created at the end of the query, which is the contents of the table query that was merged with the existing query. All columns from the merged query are condensed into the NewColumn , but you can select to Expand the table, and include whichever columns you want. To expand the merged table, and select which columns to include, select the expand icon.
The Expand window appears. In this case, we only want the State Code column, so we select only that column and then select OK. State Code the original column name, or NewColumn , then a dot, then the name of the column being brought into the query. Want to play around with how to bring in that NewColumn table? We now have a single query table that combined two data sources, each of which has been shaped to meet our needs.
This query can serve as a basis for lots of additional, interesting data connections — such as housing cost statistics, demographics, or job opportunities in any state. For now, we have enough data to create a few interesting reports, all within Power BI Desktop. Additional changes can be made after the table is loaded, and you can reload a model to apply any changes you make.
- Upload your Excel file into Power BI!
- Getting started with Power BI Desktop - Power BI | Microsoft Docs.
- What types of workbooks does Power BI support?.
- How to Make a Chart or Graph in Excel [With Video Tutorial];
- Das Glitzern des Schleiers (German Edition).
- Silent Discourse?
- How to Make a Graph in Excel.
But for now this will do. The Visualizations pane, where you can change visualizations, customize colors or axes, apply filters, drag fields, and more. The Fields pane, where query elements and filters can be dragged onto the Report view, or dragged to the Filters area of the Visualizations pane.
Pivot tables are particularly useful if you have long rows or columns that hold values you need to track the sums of and easily compare to one another. In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data together in different ways so you can draw helpful conclusions more easily.
The "pivot" part of a pivot table stems from the fact that you can rotate or pivot the data in the table in order to view it from a different perspective. To be clear, you're not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you're simply reorganizing the data so you can reveal useful information from it. If you're still feeling a bit confused about what pivot tables actually do, don't worry. This is one of those technologies that's much easier to understand once you've seen it in action. So, here are two hypothetical scenarios where you'd want to use a pivot table.
Say you have a worksheet that contains monthly sales data for three different products -- product 1, product 2, and product 3 -- and you want to figure out which of the three has been bringing in the most bucks. You could, of course, look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears.
You could then do the same for product 2, and product 3, until you have totals for all of them. Piece of cake, right? Now, imagine that monthly sales worksheet of yours has thousands and thousands of rows. Manually sorting through them all could take a lifetime. Using a pivot table, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 -- and calculate their respective sums -- in less than a minute.
In this scenario, you've just completed a blog redesign and had to update a bunch of URLs. Unfortunately, your blog reporting software didn't handle it very well, and ended up splitting the "view" metrics for single posts between two different URLs. In order to get accurate data, you need to combine the view totals for each of these duplicates.
That's where the pivot table comes into play. Now that you have a better sense of what pivot tables can be used for, let's get into the nitty-gritty of how to actually create one. If you've already entered data into your Excel worksheet, highlight the cells you'd like to summarize in a pivot table, click "Insert" along the top navigation, and select the "PivotTable" icon. You can also click anywhere in your worksheet, select "PivotTable," and manually enter the range of cells you'd like included in the PivotTable.
Prepare your data
This will open an option box where, in addition to setting your cell range, you can select whether or not to launch this pivot table in a new worksheet or keep it in the existing worksheet. If you open a new sheet, you can navigate to and away from it on the bottom of your Excel workbook. Once you've chosen, click "OK. Alternatively, you can highlight your cells, select "Recommended PivotTables" to the right of the PivotTable icon, and open a pivot table with pre-set suggestions for how to organize each row and column.
If you're using a version of Excel earlier than Excel , "PivotTables" may be under "Tables" or "Data" along the top navigation, rather than "Insert. After you've completed Step 1, Excel will create a blank pivot table for you. Your next step is to drag and drop a field -- labeled according to the names of the columns in your spreadsheet -- into the "Row Labels" area.
This will determine what unique identifier -- blog post title, product name, and so on -- the pivot table will organize your data by.
Related Creating Reports with Microsoft Excel: The Video Guide
Copyright 2019 - All Right Reserved