Advanced Analytics with Power BI and Excel
Description
Empowering You to Master Business Intelligence and Solve Real-world Analytical Problems.
¿¿¿DESCRIPTION
In today's analytics landscape, proficiency in Excel and Power BI is practically a prerequisite for a successful career. This book provides a concise yet comprehensive exploration of these foundational elements of modern Business Intelligence (BI).
Comprising ten chapters, this book covers the entire development journey of a Power BI analytical solution, spanning from data modeling and preparation to report creation, publication, and dashboard and app deployment. It offers insights into security measures and performance optimization, ensuring a well-rounded understanding of the BI ecosystem. Furthermore, it delves into advanced topics, such as leveraging data science algorithms within reports, offering readers an extensive learning experience.
The book takes a holistic approach to these technologies, offering a contrast to the narrow perspectives often adopted by contemporary BI professionals who rely solely on a single tool or language. The book allows data enrichment through calculations that can be achieved using various languages, including SQL queries at the source, Power Query Formula Language, M, Python, R, and DAX. By the end of this book you will master these options but be able to also weigh their pros and cons to make informed decisions.
TABLE OF CONTENTS
1. Introducing the Theoretical Background for Democratizing Analytics
2. Acquiring and transforming data from multiple sources
3. Power Query Transformations and Formula Language (M) Overview
4. Building a simple analytical solution with Power Pivot in Excel
5. Enhancing the model and business analysis with DAX
6. Creating reports in Power BI Desktop
7. Using the Power BI Service
8. Data Science in Power BI
9. Optimizing Power BI models and reports
10. Deploying, Maintaining, and Securing Power BI Assets
Index
More details
Content
- Cover Page
- Title Page
- Copyright Page
- Dedication Page
- About the Authors
- About the Reviewers
- Acknowledgements
- About the AdventureWorksDW Database
- Preface
- Errata
- Table of Contents
- 1. Introducing the Theoretical Background for Democratizing Analytics
- Introduction
- Structure
- Logical design - star schema
- Dimensions and fact tables
- Columnar storage
- Introducing Power BI and its history
- Conclusion
- Key terms
- Questions
- 2. Acquiring and Transforming Data From Multiple Sources
- Introduction
- Structure
- Copying data versus connecting to data sources
- Classic Excel functionality
- Getting into connectivity
- Integrated data systems
- Power Query in relation to Excel and Power BI
- Connecting to data sources
- Data sources in Power BI Desktop
- Data sources in Excel
- Data sources in other Microsoft resources
- Power Query Editor
- Current view, Queries pane, and Home tab
- Other tabs in ribbon: Query Settings and Status bar
- Conclusion
- Key terms
- Questions
- 3. Power Query Transformations and Formula Language (M) Overview
- Introduction
- Structure
- Data transformation exercise
- Let's transform the CSV file
- Power Query Formula Language (M) Overview
- Elements and Data Structures in M
- Functions and data types in M
- Custom functions, Operators and Conditions in M
- Improvement of M code from the Exercise
- Let's perform some additional transformations
- Conclusion
- 4. Building a Simple Analytical Solution with Power Pivot in Excel
- Introduction
- Structure
- Scenario
- Connecting to data with Power Query
- Enabling the Power Pivot add-in
- Power Pivot data view
- Introduction to calculated columns
- Introduction to measures
- Introduction to the diagram view
- Fact and dimension tables
- Establishing relationships between tables
- Primary keys and foreign keys
- Creating a pivot table based on the data model
- Calculating averages
- Exploring functions for counting
- Editing existing measures
- Calculating ratios with DIVIDE
- Understanding the filter flow
- Introduction to the X functions
- Key performance indicators (KPIs)
- Conclusion
- Key terms
- Questions
- 5. Enhancing the Model and Business Analysis with DAX
- Introduction
- Structure
- Scenario
- The first look at the Power BI user interface
- Importing existing data models
- Connecting to data
- Exploring the Power BI Desktop views
- Delving deeper into the Model view
- Using the Data view
- Marking as date table
- Evaluation contexts revisited
- Row context
- Query context
- Filter context
- Evaluation context in calculated columns and measures
- Calculated columns
- Measures
- The mighty CALCULATE function
- Semi-additive measures
- Creating hierarchies
- Conclusion
- Key terms
- Questions
- 6. Creating Reports in Power BI Desktop
- Introduction
- Structure
- The Power BI environment overview
- Screen elements
- Using filters
- Filters on this visual
- Using slicers instead of the Filters pane
- Hiding excessive options from end users
- Filters on this page and Filters on all pages
- Filters' hierarchy
- The Data and Visualizations panes
- Drillthrough and Tooltips
- Creating visualizations
- Header icons/visual headers
- Upper ribbon tabs in the Report view
- The Bookmarks, Selection, and Sync slicers panes
- The Table view on the left ribbon
- The Model view on the left ribbon
- The ever-changing interfaces: On-object interaction
- Conclusion
- Questions
- 7. Using the Power BI Service
- Introduction
- Structure
- The need for the Power BI Service
- Power BI Service basic components
- Workspaces
- Reports
- Dashboards
- Datasets
- Managing workspaces
- Working with reports
- Publishing reports from Power BI Desktop
- Creating reports in Power BI Service
- Using finished reports
- Creating dashboards
- Managing dashboards
- Endorsing content
- Organize content into apps
- Managing data with dataflows
- Modeling data with datamarts
- Creating paginated reports
- On-prem reporting with Power BI Report Server
- Conclusion
- Key Terms
- Questions
- 8. Data Science in Power BI
- Introduction
- Structure
- Analytics in base visualizations
- Advanced Power BI visuals
- Adding custom visualizations
- Using R and Python in Power BI
- Leveraging Azure AI
- Conclusion
- Key terms
- 9. Optimizing Power BI Models and Reports
- Introduction
- Structure
- Optimizing the data model
- Query folding
- Performance of reports
- Aggregate and dual storage tables
- Conclusion
- Key terms
- 10. Deploying, Maintaining and Securing Power BI Assets
- Introduction
- Structure
- Well-maintained BI system
- Organizing fields into display folders
- Forming the naming conventions
- Keeping the Selection pane organized
- Gateways for on-premises data sources and scheduled refresh
- Power BI Service security
- Tenant-level security settings
- Row-level security
- Defining the roles in Power BI Desktop
- Assigning users to roles in Power BI Service
- Power BI Service Apps for end-user experience
- Conclusion
- Key Terms
- Questions
- Index
