This is the first edition of this book as a PDF , and I will do my best to publish next editions regularly. The main reason to publish this book online, was that with the fast pace of updates for Power BI Desktop, it is impossible to publish a paperback book, because it will be out dated in few months.
So far, I have more than 60 sections wrote for this book. The book covers all aspects of Power BI; from data preparation, to modeling, and visualization. You can start reading this book with no prerequisite. However, there are some sections, that need an example previously built in another section. These sections have a prerequisite section mentioning this requirement.
After a year and half of writing online, I decided to release this book as a PDF version as well, for two reasons; First to help community members who are more comfortable with PDF books, or printed version of materials.
Second; as a giveaway in my Power BI training courses. Feel free to print this book and keep it in your library, and enjoy. This book is FREE! This book will be updated with newer editions hopefully every month , so you can download the latest version of it anytime from my blog post here:. I will do my best to update any changes in next few editions.
However, to keep you informed; There is a date at the beginning of each section under the header that mentioned the publish date of that section. Reza Rad is the world well-known name in the Microsoft BI field. More than 10 years of his experience focused on training and consulting to the largest BI teams in the world. He is also Microsoft Certified Trainer for years.
He trained more than students in just last few months for Power BI. Reza lives in Auckland, New Zealand, but you will probably see him speaking in a conference, or teaching a course near your city or country time by time. BI Developers and Consultants who want to know how to develop solutions with this technology.
Business Analysts who want to have better tool for playing with the data and learn tricks of producing insights easier. This article describes standard Power BI Desktop. Or for download options, select See download or language options. From the Welcome screen, you can Get data , see Recent sources , open recent reports, Open other reports , or select other links.
Select the close icon to close the Welcome screen. The current view is indicated by the yellow bar along the left, and you can change views by selecting any of the icons. To learn more about accessibility and Power BI, visit our accessibility articles. In Power Query Editor , you can build queries and transform data, then load the refined data model into Power BI Desktop to create reports.
With Power BI Desktop installed, you're ready to connect to the ever-expanding world of data. In this quick tour, you connect to a couple of different Web data sources. Imagine you're a data analyst working for a sunglasses retailer.
You want to help your client target sunglasses sales where the sun shines most frequently. The Bankrate.
If prompted, on the Access Web Content screen, select Connect to use anonymous access. The query functionality of Power BI Desktop goes to work and contacts the web resource. The Navigator window returns what it found on the web page, in this case an HTML table called Ranking of best and worst states for retirement , and five other suggested tables.
You're interested in the HTML table, so select it to see a preview. At this point you can select Load to load the table, or Transform data to make changes in the table before you load it. When you select Transform data , Power Query Editor launches, with a representative view of the table.
Now that you're connected to a data source, you can adjust the data to meet your needs. To shape data, you provide Power Query Editor with step-by-step instructions for adjusting the data while loading and presenting it.
Shaping doesn't affect the original data source, only this particular view of the data. The table data used in this guide might change over time. As such, the steps you need to follow might vary, requiring you to be creative about how you adjust steps or outcomes, which is all part of the fun of learning. Shaping can mean transforming the data, such as renaming columns or tables, removing rows or columns, or changing data types. Each time this query connects to the data source, those steps are carried out, so the data is always shaped the way you specify.
This process occurs when you use the query in Power BI Desktop, or when anyone uses your shared query, such as in the Power BI service. Notice that the Applied Steps in Query Settings already contain a few steps. You can select each step to see its effect in the Power Query Editor. First, you specified a web source, and then you previewed the table in the Navigator window.
In the third step, Changed type , Power BI recognized whole number data when importing it, and automatically changed the original web Text data type to Whole numbers.
If you need to change a data type, select the column or columns to change. Hold down the Shift key to select several adjacent columns, or Ctrl to select non-adjacent columns. Either right-click a column header, select Change Type , and choose a new data type from the menu, or drop down the list next to Data Type in the Transform group of the Home tab, and select a new data type.
Most of the tasks you can select on the Home or Transform tabs of the ribbon are also available by right-clicking an item and choosing from the menu that appears. You can now apply your own changes and transformations to the data and see them in Applied Steps.
For example, for sunglasses sales you're most interested in the weather ranking, so you decide to sort the table by the Weather column instead of by Overall rank. Drop down the arrow next to the Weather header, and select Sort ascending. The data now appears sorted by weather ranking, and the step Sorted Rows appears in Applied Steps.
You're not very interested in selling sunglasses to the worst weather states, so you decide to remove them from the table. The bottom 10 worst weather rows are removed from the table, and the step Removed Bottom Rows appears in Applied Steps. You decide the table has too much extra information for your needs, and to remove the Affordability , Crime , Culture , and Wellness columns.
Select the header of each column that you want to remove. You can also right-click one of the selected column headers and select Remove Columns from the menu.
The selected columns are removed, and the step Removed Columns appears in Applied Steps. On second thought, Affordability might be relevant to sunglasses sales after all. You'd like to get that column back. You can easily undo the last step in the Applied Steps pane by selecting the X delete icon next to the step. Now redo the step, selecting only the columns you want to delete.
For more flexibility, you could delete each column as a separate step. You can right-click any step in the Applied Steps pane and choose to delete it, rename it, move it up or down in the sequence, or add or delete steps after it. For intermediate steps, Power BI Desktop will warn you if the change could affect later steps and break your query.
For example, if you no longer wanted to sort the table by Weather , you might try to delete the Sorted Rows step. Power BI Desktop warns you that deleting this step could cause your query to break. You removed the bottom 10 rows after you sorted by weather, so if you remove the sort, different rows will be removed. You also get a warning if you select the Sorted Rows step and try to add a new intermediate step at that point.
Finally, you change the table title to be about sunglass sales instead of retirement. Under Properties in the Query Settings pane, replace the old title with Best states for sunglass sales. The data about various states is interesting, and will be useful for building additional analysis efforts and queries.
But there's one problem: most data out there uses two-letter abbreviations for state codes, not the full names of the states. To use that data, you need some way to associate your state names with their abbreviations. You're in luck. Another public data source does just that, but the data will need a fair amount of shaping before you can combine it with your sunglass table.
In the Navigator window, select the table Codes and abbreviations for U. The table opens in Power Query Editor. To keep only these columns, hold down Ctrl and select the columns. In the Filter Rows dialog box, drop down the Enter or select a value field next to equals and select State.
Select OK. With extra values like Federal district and island removed, you now have a list of the 50 states and their official two-letter abbreviations. You can rename the columns to make more sense, for example State name , Status , and Abbreviation , by right-clicking the column headers and selecting Rename.
Note that all of these steps are recorded under Applied Steps in the Query Settings pane. Retitle the table to State codes in the Properties field of Query Settings. With the State codes table shaped, you can combine these two tables into one.
Since the tables you now have are a result of queries you applied to the data, they're also called queries. There are two primary ways of combining queries: merge and append. When you have one or more columns you'd like to add to another query, you merge the queries. When you have additional rows of data you'd like to add to an existing query, you append the query.
In this case, you want to merge the State codes query into the Best states for sunglasses query. To merge the queries, switch to the Best states for sunglasses query by selecting it from the Queries pane on the left side of Power Query Editor.
Then select Merge Queries from the Combine group in the Home tab of the ribbon. In the Merge window, drop down the field to select State codes from the other queries available. Select the column to match from each table, in this case State from the Best states for sunglasses query and State name from the State codes query.
If you get a Privacy levels dialog, select Ignore privacy levels checks for this file and then select Save. A new column called State codes appears on the right of the Best states for sunglass sales table. It contains the state code query that you merged with the best states for sunglass sales query. All the columns from the merged table are condensed into the State codes column.
You can expand the merged table and include only the columns you want. To expand the merged table and select which columns to include, select the Expand icon in the column header. In the Expand dialog box, select only the Abbreviation column.
0コメント