Transitioning to Microsoft Power Platform : An Excel User Guide to Building Integrated Cloud Applications in Power BI, Power Apps, and Power Automate

Welcome to this step-by-step guide for Excel users, data analysts, and finance specialists. It is designed to take you through practical report and development scenarios, including both the approach and the technical challenges. This book will equip you with an understanding of the overall Power Platform use case for addressing common business challenges. While Power BI continues to be an excellent tool of choice in the BI space, Power Platform is the real game changer. Using an integrated architecture, a small team of citizen developers can build solutions for all kinds of business problems. For small businesses, Power Platform can be used to build bespoke CRM, Finance, and Warehouse management tools. For large businesses, it can be used to build an integration point for existing systems to simplify reporting, operation, and approval processes. The author has drawn on his15 years of hands-on analytics experience to help you pivot from the traditional Excel-based reporting environment. By using different business scenarios, this book provides you with clear reasons why a skill is important before you start to dive into the scenarios. You will use a fast prototyping approach to continue to build exciting reporting, automation, and application solutions and improve them while you acquire new skill sets. The book helps you get started quickly with Power BI. It covers data visualization, collaboration, and governance practices. You will learn about the most practical SQL challenges. And you will learn how to build applications in PowerApps and Power Automate. The book ends with an integrated solution framework that can be adapted to solve a wide range of complex business problems. What You Will Learn Develop reporting solutions and business applications Understand the Power Platform licensing and development environment Apply Data ETL and modeling in Power BI Use Data Storytelling and dashboard design to better visualize data Carry out data operations with SQL and SharePoint lists Develop useful applications using Power Apps Develop automated workflows using Power Automate Integrate solutions with Power BI, Power Apps, and Power Automate to build enterprise solutions Who This Book Is For Next-generation data specialists, including Excel-based users who want to learn Power BI and build internal apps; finance specialists who want to take a different approach to traditional accounting reports; and anyone who wants to enhance their skill set for the future job market. Table of Contents About the Author About the Technical Reviewer Introduction Chapter 1: Power BI First Report Business Scenario Power BI Software Licensing Build Your First Power BI Report Data and Requirements Install Power BI Desktop Report Building Add a Text Box Add Slicers Add a Pie Chart Add a Matrix Table Add a Line Chart Clean up the Report Conclusion Chapter 2: Power BI Core Components Build a Report with Power Query Business Scenario Data Processing Automation Create a New Query Combine Power Query Tables Add a New Column Seamless Data Integration Add a New Report Page Power Query Summary Building a Report with a Data Model Why a Data Model? Minimize Data Redundancy Facilitate Data Reuse Filter Across Multiple Fact Tables Implement the Data Model Create a Basic Data Model Reduce Data Redundancy Customized Calendar Table Use Common Filter Table on Multiple Fact Tables Data Model Summary DAX: Data Analysis Expression Create a Basic Measure in DAX Filter Context in DAX Row Context in DAX Create a Calendar Table Using DAX The Calculate Function in DAX Ignore the Filter Context Add a Filter Context Update the Filter Context Date Intelligence with CALCULATE Variables in DAX Organize DAX Measures DAX Summary Conclusion Chapter 3: Power BI Service Publishing and Sharing Reports Business Scenario Sharing Risks My Workspace Sharing Links Share with Others in Your Organization A Better Way of Sharing Local Power BI Support Get Power BI Pro License Determine Sharing Method Publish into Non-Personal Workspace Sharing via Direct Access User Access Approval and Review Processes Report-Sharing Summary Automation Data Connection Export Flat Files in a Shared Folder Data Transformation Scheduling with Power BI Gateways Power BI Gateway Conclusion Mini-Hackathon Hackathon Data Objectives Time Limit Chapter 4: Data Visualization Chart Selection Pie and Donut Charts Column and Bar Charts Line Charts Table vs. Matrix Visuals Scatter Charts Sunburst Diagrams Summary Data Explanation vs. Exploration Data Exploration Export Underlying Data Setting 1: Power BI Desktop Options Setting 2: Power BI Service Options Data Explanation Summary Report Design Business Scenario Structure the Reports Report Navigation Power BI Dashboard Design Template for Exploration Reports Color Selection The 60-30-10 Color Rule Color Association RAG Colors Color Blindness Conclusion Mini-Hackathon Hackathon Data Objectives Time Limit Chapter 5: Power BI Governance Why Governance? A Business Scenario Adoption-Driven Governance Framework Usage Data Workspace Usage Report PowerShell cmdlet and API Drive User Adoption Identify a New Usage Process Identify Consolidation Process Track Benefits Effort Saved Value Adds Improve Development Efficiency Report Prioritization Report Standardization Navigation Method Data Connection Standardized Data Model Standardize DAX Measures Colors and Background Image Development Standardization Agile MVP Approach Jira for Report Development and Maintenance Report Documentation Whole-Life Cost and Optimization Steps Workspace Structure Support Framework Support Levels Data Gateway Data Security Access Points Analysis Data Source Connection Process User Access Management Workspace Access Register Report Access Register Conclusion Chapter 6: SQL for Power BI Set up a SQL Server for Practice Set up a Free Azure Account Set up a Sample SQL Database Log in to Azure Query Editor Load SQL Query in Power BI SQL Query Basics Query Construction Process Identify the Core Table(s) Review the Table Columns Search for Additional Tables Apply Filter Logic SQL Query Syntax SELECT WHERE TOP and ORDER BY JOIN SQL Functions Data Types Common Scalar Functions Group By and Aggregate Function Aggregate Function Group By SQL Basics Summary Predefined Variables and Tables Create Variables Create Variables from Data Create Table from Data (WITH) Conclusion Mini-Hackathon Objective Objective 1 Objective 2 Time Limit Chapter 7: SharePoint Lists Business Scenario Create a SharePoint List Create a SharePoint Site Create a SharePoint List Create a New List Add Columns Column Property Guideline Add Columns for a Ticketing System Edit a SharePoint List Standard Edit Bulk Edit Edit in Grid View Edit Using Microsoft Access User Access and Security Permission Level Groups List Permissions Share the List with Users List Views Columns Selection Sort Options Filter Options Connection with Power BI Difference in Content Improve Speed with Default View Conclusion Chapter 8: First Power Apps Business Scenario Power Apps Licensing Check Access to Power Apps Power Apps Connectors Premium Connectors Cost and Licensing Strategies Power Apps with a SharePoint List Build the First Power Apps Create an App from a SharePoint List Understanding Power Apps Studio Understanding the Default App Browse Screen Detail Screen Edit Screen Share the Default App Build the App from a Blank Canvas Create a Blank Canvas App Agile Mindset for App Development Typical Approach Agile Approach Establish the Initial Requirements Add Data Main Screen Gallery Create a Blank Vertical Gallery Gallery and Data Table Relationship Organize a Gallery Edit Screen Edit to Display Switch Add a New Record Button Save to a SharePoint List Use Patch to Add New Data Use Patch to Modify Data Conclusion Chapter 9: Improve Power Apps The Business Scenario Variables and Delegation Variables in Power Apps Local Variable Global Variable Collection Create Variables Delegation Reason for Row Limit Warning and Error Messages User Level Filter Color Palette Apply Naming Standards Additional UI Features Drop-down Filter Single Drop-down Filter Multiple Drop-down Filters Create a Drop-down List from Data Sorting the Gallery Search Bar Dynamic Calculation Basic Calculations Calculation in a Gallery Aggregation Calculations Calculation in Collection User Experience Have a Clear Purpose Add Interaction Feedback Hover Effect in Gallery Pop-up Window Input Validation Conclusion Mini-Hackathon Objective Time Limit Chapter 10: Power Automate Business Scenario Power Automate Licensing Power Automate Flows Cloud Flow Overview Email Notification Create a Backup Prepare a Backup Excel File Create the Backup Process Test the Backup Process Fix Issues OData Filter Streamline Approval Processes Prepare the Approver Email Build the Approval Flow Integrate with Power Apps Approvals Customization Options Custom Responses in Approval Flow Add Conditional Actions After the Approval Flow Conclusion Mini-Hackathon Objectives Objective 1 Objective 2 Time Limit Chapter 11: Integrated Solutions Business Scenario Power Apps Visual in Power BI Limitation of Power Apps Visual Integrated Architecture Application Design Implement the Same Page Layout Underlying Data Use Scatter Plot for Strategic Categorization Create an Embedded App Include Decisions Back into the App Include Decisions Back into the Report Improving the Integrated Application Add Email Notification Add Action Status Tracker Access Management Conclusion Mini-Hackathon Objective Time Limit Final Thoughts Index 0.png 0.png