Microsoft Power BI Data Analyst Certification Companion: Preparation for Exam PL-300 (Certification Study Companion Series)

Use this book to study for the PL-300 Microsoft Power BI Data Analyst exam. The book follows the “Skills Measured” outline provided by Microsoft to help focus your study. Each topic area from the outline corresponds to an area covered by the exam, and the book helps you build a good base of knowledge in each area. Each topic is presented with a blend of practical explanations, theory, and best practices. Power BI is more than just the Power BI Desktop or the Power BI Service. It is two distinct applications and an online service that, together, enable business users to gather, shape, and analyze data to generate and present insights. This book clearly delineates the purpose of each component and explains the key concepts necessary to use each component effectively. Each chapter provides best practices and tips to help an inexperienced Power BI practitioner develop good habits that will support larger or more complex analyses. Many business analysts come to Power BI with a wealth of experience in Excel and particularly with pivot tables. Some of this experience translates readily into Power BI concepts. This book leverages that overlap in skill sets to help seasoned Excel users overcome the initial learning curve in Power BI, but no prior knowledge of any kind is assumed, terminology is defined in non-technical language, and key concepts are explained using analogies and ideas from experiences common to any reader. After reading this book, you will have the background and capability to learn the skills and concepts necessary both to pass the PL-300 exam and become a confident Power BI practitioner. What You Will Learn Create user-friendly, responsive reports with drill-throughs, bookmarks, and tool tips Construct a star schema with relationships, ensuring that your analysis will be both accurate and responsive Publish reports and datasets to the Power BI Service, enabling the report (and the dataset) to be viewed and used by your colleagues Extract data from a variety of sources, enabling you to leverage the data that your organization has collected and stored in a variety of sources Schedule data refreshes for published datasets so your reports and dashboards stay up to date Develop dashboards with visuals from different reports and streaming content Who This Book Is For Power BI users who are planning to take the PL-300 exam, Power BI users who want help studying the topic areas listed in Microsoft’s outline for the PL-300 exam, and those who are not planning to take the exam but want to close any knowledge gaps they might have Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Part I: Prepare to Study Chapter 1: Exam Overview Why Certify? When Should You Take the Exam? What Is Reasonably Confident? Taking the Exam Signing Up Online or at a Testing Center? Choosing Your Time Block Marking Your Calendar Canceling or Rescheduling Your Exam The Exam Format General Tips for Exam Taking You’ve Passed. Now What? You’ve Failed. Now What? Chapter 2: PL-300 Coverage The Tools Power BI Desktop Desktop Updates Reports and Dashboards Power Query Editor The Power BI Service Workspaces, My workspace, and Apps Licensing Languages Practicing Before the PL-300 Northwind Excel and CSV Files AdventureWorks Database What and Why? Part II: Prepare the Data Chapter 3: Get Data from Different Sources Getting the Data Entering Your Credentials Change Data Source Settings Changing Data Source Settings When a Data Source Moves Editing Permissions Clear Permissions Privacy Levels Selecting a Storage Mode Import Direct Query For Future Reference: Writing Your Own SQL Code Live Connection Using Different Data Sources Dataverse Dataflows Shared Dataset Local Dataset Using Folders and SharePoint Folders Parameters Defining Parameters Within the Power Query Editor In the Power BI Desktop Chapter 4: Clean, Transform, and Load the Data Accessing the Power Query Editor What Is the Power Query Editor? The M Language Undoing and Redoing a Step Naming and Documenting Data Quality and Distribution Keys and IDs Data Types Matter! For Future Reference: Query Folding Replacing Data Blanks and Nulls Errors Adding New Data Adding a New Column Adding a New Query Keep Only What You Need Combining Queries Append Merging Duplicating a query Referencing a Query Deleting a Query Working with Some Data Only 1000 Rows? For Future Reference: Future-Proofing Your Work Excluding and Including Columns Resolving Errors in Your Query Incorrect Data Type Missing or Renamed Column For Future Reference: Reusing Your Work The Advanced Editor Copying a Query Copying Parts of an M Script Loading Your Queries (or Not) A Few Tips and Tricks Additional Resources Part III: Model the Data Chapter 5: Design a Data Model Define the Tables Dims and Facts Using Fields in Visuals Relationships: A Model’s Connective Tissue One-to-Many Relationship (1-Many) Many-to-Many Relationships (Many-Many) One-to-One (1-1) For Future Reference: Disconnected Tables Active and Inactive Relationships Star Schema Relationships and Directions Single-Direction Relationships Bidirectional Relationships Finally, Cardinality! Adding a Date Table Inactive Relationships Role-Playing Dimensions Critical to Success Chapter 6: Develop a Data Model Wait! What Is DAX? Adding Data to Your Model Calculated Tables Calculated Columns Refining Your Model Hierarchies Hiding Fields Summarization Default Summarization Categorization Setting Up Q&A Functionality Preparing for Q&A Security Multilayer Security Access Credentials Row-Level Security For Future Reference: Object-Level Security Chapter 7: Create Model Calculations Using DAX Measures DAX Syntax: A Quick Review Filter Context Introducing CALCULATE Modifying the Filter Context Modifying the Model (Temporarily) Row Context Implicit and Explicit Measures Quick Measures The Wonderful World of DAX Functions Time Intelligence Functions Semi-additive Measures Statistical Functions DAX Is Simple, but Not Easy Chapter 8: Optimize Model Performance Measuring Report Performance Examining DAX Query Performance Checking Number of Visuals per Page Considering Other Performance Factors Part IV: Visualize and Analyze the Data Chapter 9: Create Reports The Canvas Just Because You Can... Built-In Themes Accessibility Visualizations Standard Visuals Custom Visuals For Future Reference: Build Your Own Visual Selecting the Right Visual Type Formatting Visuals Configuring Visualizations Slicing and Filtering Slicers Copying Slicers, Syncing Slicers The Filters Pane For Future Reference: Controlling the Performance Impact of Filtering Formatting the Filters Pane Drill-Through For Future Reference: Drill Through from One Report to Another Conditional Formatting Web URL For Future Reference: Using DAX to Conditionally Format Other Page Elements Images and Shapes Text Boxes Buttons Button States Using the Navigation Button Paginated Reports Chapter 10: Enhance Reports Specifying a Sort Order Expand Down and Drill Down Controls Interactions Between Visuals For Future Reference: Interactions Impact Page Performance Bookmarks Using the Bookmark Navigator Button Viewing Your Bookmarks For Future Reference: Bookmark Settings Changing What Is Visible on a Bookmark Combining Bookmarks and Buttons Tooltips Tooltip Pages Designing for Mobility Chapter 11: Identify Patterns and Trends The Analytics Pane Using the Analyze Feature Identifying Outliers Categorical vs. Continuous Axes Groupings, Binnings, and Clustering Creating Groups Creating Bins Clustering Using AI Visuals The Key Influencer Visualization The Decomposition Tree Visualization Part V: Deploy and Maintain Assets Chapter 12: Manage Files and Datasets Schedule a Refresh Do You Need a Gateway? For Future Reference: Connecting OneDrive and Power BI Configuring Row-Level Security Build and Share Permissions Chapter 13: Create Dashboards Creating a Dashboard Creating a Dashboard Editing the Dashboard Streaming Data Dashboard Themes Editing a Tile Export to .csv View Insights Pin Tile Pinning Live Pages Managing Tiles on a Dashboard Configuring the Mobile View Use the Q&A Feature Enabling Q&A in the Power BI Desktop Enabling Q&A in the Service Refreshing a Dashboard Chapter 14: Manage Workspaces in the Service Publishing Your Report Logging Into the Power BI Service Choosing the Workspace Overview of the Power BI Service Getting There The Structure of the Service Navigating to Your Workspace The Anatomy of a Workspace Governance Creating a Workspace Premium Capacity Creating Reports in the Service Adding Additional Content Updating a Report A Workspace and Its App Creating an App Deleting an App Subscribing to a Report or Dashboard Other Settings in the Workspace Data Sensitivity Labels Promoting and Certifying Content Promoting Your Work Certifying Content Part VI: Continue Your Learning Chapter 15: Where Do You Go from Here? YouTube Guy in a Cube Havens Consulting (Reid Havens) Ruth Curbal RADACAD SQLBI Chris Wagner Two Alex Explicit Measures Blogs SQLBI Chris Webb Ben Gribaudo Paul Turley Books The Definitive Guide to DAX by Marco Russo and Alberto Ferrari Collect, Combine, and Transform Data Using Power Query in Excel and Power BI by Gil Raviv Master Your Data with Power Query in Excel and Power BI by Ken Puls and Miguel Escobar Super Charge Power BI by Matt Allington Star Schema by Chris Adamson Paid Training Mastering DAX with Marco Russo and Alberto Ferrari Power Query Editor and M with Ben Gribaudo Video Training Courses by SQLBI User Groups Conferences Social Media Twitter Reddit LinkedIn External Tools DAX Studio Bravo Tabular Editor Other Tools Conclusion Index