Power BI for Jobseekers Learn how to create interactive dashboards and reports, and gain insights from the data (Alan Murray) (z-library.sk, 1lib.sk, z-lib.sk)

📄 File Format: PDF

💾 File Size: 44.4 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

 i

📄 Page 3

 i Power BI for Jobseekers Learn how to create interactive dashboards and reports, and gain insights from the data Alan Murray www.bpbonline.com

📄 Page 4

ii  Copyright © 2023 BPB Online All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor BPB Online or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book. BPB Online has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, BPB Online cannot guarantee the accuracy of this information. First published: 2023 Published by BPB Online WeWork 119 Marylebone Road London NW1 5PU UK | UAE | INDIA | SINGAPORE ISBN 978-93-55518-149 www.bpbonline.com

📄 Page 5

 iii Dedicated to My amazing children: George & Lily

📄 Page 6

iv  Alan Murray is a Microsoft MVP and Excel & Power BI trainer. He has been training and consulting in Microsoft technologies for over 20 years. He loves training and the joy he gets from knowing he is making peoples’ working lives easier. Alan runs his own blog - Computergaga (https:// computergaga.com) and writes for multiple other websites. His Computergaga YouTube channel has over 600 videos and over 40 million views. About the Author He organizes a free monthly Excel & Power BI meetup in London where anyone can come, learn, chat, and enjoy each other’s company.

📄 Page 7

 v About the Reviewers v Dinesh Manoharan is a veteran in the field of Data and Analytics and has a broad range of expertise in Power BI, Qlik, and Tableau reporting and data storytelling. Has traction towards predictive and prescriptive analytics using R, Python, and Azure ML. He helped customers to make data-driven decisions and possesses good hands-on experience in design, data modeling, performance tuning, and turning data into actionable insights. As part of the BI Centre of Excellence team, Dinesh is a certified data analyst working in various domains to drive data culture, with his deep data knowledge reduced data-to-insights turnaround time. He actively handles training sessions on Data literacy and Analytical tools. He likes to play badminton, cycling, listen music and admire nature." v Preeti Sahu is an expert in Power BI and has an extensive background working with SharePoint and the Power Platform. She is currently employed by TSinfo Technologies as a Power Platform specialist for Power BI, Power Apps, Power Automate, and Power Virtual Agents. She is the author of the Microsoft Power Platform A Deep Dive book and the co-author of the SharePoint Online Modern Experience Practical Guide book. Also, she contributed a significant number of technical articles on the Power Platform to SPGuides.com and EnjoySharePoint.com. In addition to traveling, she enjoys spending time with her family during her free time.

📄 Page 8

vi  Acknowledgement I would like to thank the Power BI community – the experts, professionals, learners, and Microsoft. We learn and grow together. Without Power BI and the amazing community providing solutions to others’ problems and pushing Microsoft to constantly make it better, there would be no book. A special thanks to those within my own community. Subscribers of my Computergaga YouTube channel, blog, and members of my London Excel Meetup group. Together we learn and you inspire me to be the best I can be. Thank you for holding me accountable for the quality of my work and for your support. My gratitude to the team at BPB Publications for being supportive throughout the writing of this book. In this constantly evolving space, and with software updating monthly, there were numerous times when an update required that I go back and update chapters. I needed their support through this demanding process. Finally, as always, thank you to my children, George, and Lily. I'm very lucky.

📄 Page 9

 vii Preface Data is everywhere! Data is a precious commodity for businesses and there has never been a more exciting time for those who like to work with data. Businesses store huge volumes of data, which they use to make decisions to stay competitive in their field. This means there is a large demand for people with the skills to make sense of that data. Having data is useless without meaning. The ability to create reports that result in better understanding and therefore drive effective decision-making is in high demand. Businesses want to maximize the value of their data. Power BI Desktop is the best tool for self-service business intelligence and analytics. This free tool makes it easy to acquire, shape, model, and present data in an effective and interactive way. The Power BI Service then provides a cost-effective way to share these reports with other members of your team or with management. Learning Power BI will give you the edge over others who still use over-priced, rigid, and limited tools. The Microsoft data platform is the leader in the analytics and BI market. This book will demonstrate the demand for data analytics skills by businesses today, where you can find work and what employers will expect from you. It then takes you on a practical guide to Microsoft Power BI. No prior knowledge is required. This is a comprehensive beginners guide to Power BI. Power BI for Job Seekers is divided into 19 chapters. Chapter 1: Why Learn Power BI? - The first chapter of this book explains why you should learn Power BI. This is supported by data from job searches, market research and the words of current professionals from recruitment and consultancy firms working with Power BI and data analysis.

📄 Page 10

viii  Chapter 2: What is Power BI? - In this chapter we start to explore Power BI. We start by explaining the different components of Power BI. We then download and install Power Bi Desktop and create a Power BI account. Chapter 3: Getting Started with Power BI Desktop – In this chapter, we get started using Power BI Desktop. We familiarise ourselves with the Power BI Desktop environment, create a new Power BI file, and explore important options and settings. Chapter 4: Creating a Simple Power BI report - Things now get very exciting. In this chapter, we create our first Power BI report. This is a simple, but effective report, to showcase the general process of how to create a report in Power BI. We will connect to external data, perform transformations to the data, build visuals and specify how the reader can interact with them. Chapter 5: Getting and Shaping Data - In this chapter, we look at how to get data from a variety of sources into Power BI. These sources include from PDF files, Excel workbooks, OneDrive, and local folders. Various transformations will be performed to shape the data before loading it to Power BI. This is the beginning of the report that we will focus on creating throughout this book. Chapter 6: More Data Transformations- In this chapter, we explore further data transformations that I feel deserve some coverage in this book. These transformations are incredibly useful. They include merge queries and the different join types, performing date transformations, calculations in Power Query, and the brilliant unpivot columns feature. Chapter 7: Creating the Data Model - In this chapter, we fine-tune the data model ready for the DAX calculations and report visuals that are to come. This includes establishing relationships between the tables of the data model, hiding unnecessary fields, and formatting field values correctly. Chapter 8: Creating a Date Table - In this chapter, we continue building the data model of our report by adding a date table. We begin by detailing why you need a date table in your model and what one consists of. We then create the date table using the DAX formula language and add the columns that we require. We then ensure that the columns are ordered correctly and mark the table as a date table. This ensures that Power BI works effectively with our new date table and does not create its own date tables to perform time intelligence.

📄 Page 11

 ix Chapter 9: Adding DAX Measures - In this chapter, we delve further into the DAX formula language and look at measures. Measures are DAX calculations that aggregate the values for many rows of a table. We will create measures that we require for our report building in the forthcoming chapters. You will learn a few very useful DAX functions including CALCULATE, RELATED, COUNTROWS, SUMX, and DIVIDE to name a few. Chapter 10: Cards and Other Text Visuals - In this chapter, we begin looking at the visualizations available in Power BI to present your data effectively. This chapter focuses on text-based visuals, of which there are four that will be covered – the Card, KPI, Table and Matrix. Chapter 11: Chart Visuals - This chapter will focus on using the chart visuals in Power BI including the column, line, combo, gauge, and treemap visuals. You will see practical examples of their use and learn key formatting attributes that can be changed to increase the effectiveness of the visual. Chapter 12: Using Maps in Power BI Reports - In this chapter, we look at the map visual of Power BI and use it to efficiently visualize geographic data such as countries, cities, and postcodes. You will learn a few tips to get the most out of your Power BI maps. Chapter 13: Other Power BI Visualizations - In this chapter, we dive into the AI visuals of Power BI including the Q&A, Decomposition Tree, and Smart Narrative visuals. We also cover how to import custom visuals from the AppSource marketplace to cater for scenarios when the standard visuals do not fulfil your needs. Chapter 14: Report Interactions, Filters & Slicers - This chapter is about the different ways that we can enable a user to interact and filter the visuals on report pages. We cover visual interactions, slicers, the Filters pane, and Drill through. Chapter 15: Enhancing your Power BI Reports - In this chapter, we will make some final enhancements to our report before we publish it to the Power BI service and share it with others. We start with inserting objects such as text boxes, images, and buttons. Then we move to creating custom tooltips pages and using bookmarks in your Power BI reports. Chapter 16: Publishing & Sharing your Reports - In this chapter, we publish our report and explore the Power BI Service. We look at how to share our report with

📄 Page 12

x  others both inside and outside of the organization and create new reports from existing datasets that have been published. Chapter 17: Datasets, Dashboards & Reports - In this chapter, we will dive into more details on some of the key features of the Power BI service – datasets, dashboards, and reports. We will see how to create your own workspaces and specify the access for different users and groups and the permissions that they have. We will also cover the important topic of how to refresh your data and your reports. Chapter 18: Power BI and Other Apps - This chapter looks at some associated tools to Power BI that are important to know. A key topic is the way that Power BI interacts with Excel. It is very easy to transfer your data models between Excel and Power BI and vice versa. We will also look at how Power BI can interact with PowerPoint and with Microsoft Teams. Chapter 19: Interview Questions, Certifications & Resources - The final chapter of the book looks at how to land your analysis job. We ask recruiters what they are looking for when hiring an analyst including typical interview questions, experience, and skills, so that you can be fully prepared. We also look at useful certifications and further learning and development opportunities.

📄 Page 13

 xi Code Bundle and Coloured Images Please follow the link to download the Code Bundle and the Coloured Images of the book: https://rebrand.ly/dbgic3o The code bundle for the book is also hosted on GitHub at https://github.com/bpbpublications/Power-BI-for-Jobseekers. In case there's an update to the code, it will be updated on the existing GitHub repository. We have code bundles from our rich catalogue of books and videos available at https://github.com/bpbpublications. Check them out! Errata We take immense pride in our work at BPB Publications and follow best practices to ensure the accuracy of our content to provide with an indulging reading experience to our subscribers. Our readers are our mirrors, and we use their inputs to reflect and improve upon human errors, if any, that may have occurred during the publishing processes involved. To let us maintain the quality and help us reach out to any readers who might be having difficulties due to any unforeseen errors, please write to us at : errata@bpbonline.com Your support, suggestions and feedbacks are highly appreciated by the BPB Publications’ Family. Did you know that BPB offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.bpbonline.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at : business@bpbonline.com for more details. At www.bpbonline.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on BPB books and eBooks.

📄 Page 14

xii  Piracy If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at business@bpbonline.com with a link to the material. If you are interested in becoming an author If there is a topic that you have expertise in, and you are interested in either writing or contributing to a book, please visit www.bpbonline.com. We have worked with thousands of developers and tech professionals, just like you, to help them share their insights with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea. Reviews Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions. We at BPB can understand what you think about our products, and our authors can see your feedback on their book. Thank you! For more information about BPB, please visit www.bpbonline.com. Join our book's Discord space Join the book's Discord Workspace for Latest updates, Offers, Tech happenings around the world, New Release and Sessions with the Authors: https://discord.bpbonline.com

📄 Page 15

 xiii Table of Contents INTRODUCTION 1. Why Learn Power BI? ..................................................................................................1 Introduction .................................................................................................................1 Structure .......................................................................................................................1 Objectives .....................................................................................................................2 Gartner Magic Quadrant 2022 ..................................................................................2 Demand for Power BI skills ......................................................................................3 The words of current professionals ..........................................................................6 Conclusion ...................................................................................................................8 Points to remember ....................................................................................................9 2. What is Power BI? ...................................................................................................... 11 Introduction ............................................................................................................... 11 Structure .....................................................................................................................12 Objectives ...................................................................................................................12 The three components of Power BI ........................................................................12 Power BI desktop ....................................................................................................13 Power query ................................................................................................................... 13 Model the data ............................................................................................................... 14 DAX .............................................................................................................................. 15 Visualizations ................................................................................................................ 15 Publish your reports ...................................................................................................... 16 Power BI service ..................................................................................................16 Workspaces..................................................................................................................... 17 Datasets ......................................................................................................................... 18 Reports ........................................................................................................................... 18 Dashboards .................................................................................................................... 18 Apps ............................................................................................................................... 19 Power BI mobile .....................................................................................................19 Installing power BI desktop ....................................................................................19 Downloading from the Microsoft store ..................................................................20

📄 Page 16

xiv  Downloading directly from Microsoft.com ............................................................21 Creating a Power BI account ...................................................................................24 Conclusion .................................................................................................................25 Questions ...................................................................................................................25 3. Getting Started with Power BI Desktop ................................................................27 Introduction ...............................................................................................................27 Structure .....................................................................................................................27 Objectives ...................................................................................................................27 A first look at Power BI Desktop ............................................................................28 Welcome screen ......................................................................................................28 The Report view .....................................................................................................29 The Data view ........................................................................................................31 The Model view ......................................................................................................33 Exploring useful options and settings ...................................................................34 Data load ................................................................................................................35 Preview features .....................................................................................................37 Regional settings ....................................................................................................38 Query reduction .....................................................................................................38 Report settings .......................................................................................................40 Saving a Power BI Desktop file ...............................................................................41 Conclusion .................................................................................................................41 Questions ...................................................................................................................42 4. Creating a Simple Power BI Report .......................................................................43 Introduction ...............................................................................................................43 Structure .....................................................................................................................43 Objectives ...................................................................................................................44 Getting data from the Web ......................................................................................44 Transforming data in Power Query .......................................................................47 Removing columns .................................................................................................48 Splitting columns by a delimiter ............................................................................50 Converting negative values to positive ..................................................................52 Using trim to remove excess spaces .......................................................................53 Renaming columns ................................................................................................54 Changing column data types .................................................................................54

📄 Page 17

 xv Renaming steps ......................................................................................................57 Applying Queries in Power BI ................................................................................58 Creating a column chart ..........................................................................................59 Editing existing queries ...........................................................................................62 Using column from examples .................................................................................63 Modifying the column chart visual ........................................................................65 Changing the order of the X-axis ............................................................................67 Creating a table visual ..............................................................................................68 Interactions between the visuals ............................................................................74 Conclusion .................................................................................................................75 Questions ...................................................................................................................75 5. Getting and Shaping Data ........................................................................................77 Introduction ...............................................................................................................77 Structure .....................................................................................................................77 Objectives ...................................................................................................................78 Creating a New Power BI file ..................................................................................78 Getting data with Power Query .............................................................................79 Import versus DirectQuery mode ..........................................................................80 Getting data from an Excel Workbook ..................................................................81 Removing rows .......................................................................................................83 Splitting columns ...................................................................................................85 Data types and renaming columns ........................................................................87 Getting data from PDF files .....................................................................................87 Appending queries .................................................................................................89 Promote the headers of a table ................................................................................92 Merging columns ...................................................................................................93 Data types and renaming columns ........................................................................95 Importing data from multiple files in a folder .....................................................95 Combine the data from all files into one table ........................................................98 Removing unwanted characters ...........................................................................101 Changing the case of text .....................................................................................104 Data types and renaming columns ......................................................................105 Importing data from files stored on OneDrive/SharePoint .............................105 Combine all tables into one .................................................................................. 110 Data types and renaming columns ...................................................................... 112

📄 Page 18

xvi  Managing data source settings ............................................................................. 114 Data sources in the current file ............................................................................ 114 Changing global permissions ............................................................................... 116 Changing source in the Power Query Editor ...................................................... 117 Conclusion ............................................................................................................... 119 Questions ................................................................................................................. 119 6. More Data Transformations ...................................................................................121 Introduction .............................................................................................................121 Structure ...................................................................................................................122 Objectives .................................................................................................................122 Merge queries ..........................................................................................................122 The different join kinds ........................................................................................122 Left Outer Join—adding a column to a table .......................................................124 Left Anti Join—removing rows from a table ........................................................129 Establishing the date data type .............................................................................132 Using the locale data type ....................................................................................132 Converting ISO 8601 Dates ................................................................................135 Calculations in power query .................................................................................137 Mathematical calculations ...................................................................................138 Rounding values ..................................................................................................140 Adding and editing M code..................................................................................141 Changing the rounding method ................................................................................... 142 Defining a data type..................................................................................................... 144 Date functions—calculating age ..........................................................................145 Inserting conditional columns .............................................................................148 Unpivoting columns ...............................................................................................151 Conclusion ...............................................................................................................155 Questions .................................................................................................................155 7. Creating the Data Model ........................................................................................157 Introduction .............................................................................................................157 Structure ...................................................................................................................158 Objectives .................................................................................................................158 Why create table relationships? ............................................................................158 Cardinality ...............................................................................................................160

📄 Page 19

 xvii Creating table relationships ..................................................................................161 Managing the relationships of a model ...............................................................164 Disabling queries from loading ............................................................................166 Formatting table columns ......................................................................................169 Hiding table fields from the Report view ...........................................................172 Deleting fields from a model .................................................................................175 Conclusion ...............................................................................................................177 Questions .................................................................................................................177 8. Creating a Date Table ..............................................................................................179 Introduction .............................................................................................................179 Structure ...................................................................................................................179 Objectives .................................................................................................................180 Why create a date table? ........................................................................................180 Introduction to DAX...............................................................................................183 CALENDARAUTO and CALENDAR functions ...............................................184 CALENDARAUTO function ..............................................................................186 CALENDAR function .........................................................................................189 Creating additional date columns ........................................................................190 Year .......................................................................................................................191 Month ...................................................................................................................191 Year and month ....................................................................................................193 Day of week name .................................................................................................193 Is it a weekend date or not? ..................................................................................195 Fiscal years ...........................................................................................................195 Fiscal quarters ......................................................................................................196 Fiscal months .......................................................................................................197 Week number of the year ......................................................................................198 Create the date table with one formula .................................................................200 Sorting table columns by another column ..........................................................201 Creating the relationships to the date table ........................................................203 Marking a table as a date table .............................................................................204 Hiding other date fields .........................................................................................205 Conclusion ...............................................................................................................206 Questions .................................................................................................................207

📄 Page 20

xviii  9. Adding DAX Measures ...........................................................................................209 Introduction .............................................................................................................209 Structure ...................................................................................................................210 Objectives .................................................................................................................210 What is a measure in Power BI? ...........................................................................210 Implicit versus explicit measures ......................................................................... 211 The COUNTROWS function .................................................................................216 Organizing your measures ....................................................................................217 How do I move a measure? ..................................................................................218 Creating folders within tables ..............................................................................219 Creating a measure table ......................................................................................221 SUMX and other iterator functions ......................................................................223 Returning the previous months revenue.............................................................225 The CALCULATE function .................................................................................226 The DATEADD function ....................................................................................226 Writing the measure .............................................................................................227 Revenue difference between the two months ....................................................229 Calculating the percentage revenue change .......................................................231 Creating a year-to-date total .................................................................................232 Conclusion ...............................................................................................................234 Questions .................................................................................................................235 10. Cards and Other Text Visuals ................................................................................237 Introduction .............................................................................................................237 Structure ...................................................................................................................237 Objectives .................................................................................................................238 The Card visual .......................................................................................................238 Formatting the callout value ................................................................................239 Category label versus title ....................................................................................240 Adding a border....................................................................................................242 Format painter .....................................................................................................243 KPI ............................................................................................................................245 Inserting a Slicer ..................................................................................................247 Removing the icon ................................................................................................249 Trend axis options ................................................................................................250 Changing the target label .....................................................................................251