Power Bi Step-By-Step Part 3: Power Query, Parameters, Templates & Custom Functions: Power Bi Mastery Through Hands-On Tutorials - Paperback

by Grant Gamble (Author)

Self-Service Business Intelligence

Power BI enables business users to interact with corporate information and to extract the reports and intel they need. Power BI allows users to access reports and dashboards using familiar tools and devices. Self-service BI has never been so user-friendly

The Microsoft Power BI Step-by-Step series will show you how to create compelling visualizations and reports which provide deep insights into your data.

G Com Solutions Limited provide Power BI training in Peterborough, London and throughout the UK. Details of their Power BI courses can be found at the following URL:

https: //gcomsolutions.co.uk/microsoft-power-bi-training-courses/

In this third book in the series, we focus on data cleansing and transformation. The techniques covered in this book apply both to using the Query Editor in Power BI Desktop and using Power Query to create dataflows in the Power BI service.

After having plenty of hands-on practice on cleaning and transforming data, you will learn how to leverage templates, parameters and custom functions to create reusable Power BI reporting solutions.

Introduction

Chapter 1: Power BI Dataflows vs Power BI Desktop
Data Preparation
Using a Dataflow as a Data Source
Conclusion

Chapter 2: Trim, Clean and Case
Isolating the Problem
Using the Trim Function
Using the Clean Function
Using the Replace Values Command
Using the Capitalize Each Word Command
Conclusion

Chapter 3: House Keeping and Meta Data
Renaming Items
Renaming Columns
Renaming Query Steps
Adding Descriptions to your Steps
Conclusion

Chapter 4: The Split Columns Command
Tidying up the Data
Using Split by Number of Characters
Creating Relationships
Creating a Treemap Visual
Creating a Filled Map Visual
Conclusion

Chapter 5: Removing Unwanted Rows
Removing Header Rows
Removing an Excel Table Total Row
Conclusion

Chapter 6: Replace Value and Fill Down
The Replace Values Command
Remove Errors and Replace Errors
The Fill Down Command
Conclusion

Chapter 7: The Unpivot Columns Command
What is Pivoted Data?
Importing the Data
Using UnPivot Other Columns
Creating a Bar Chart Visual
Conclusion

Chapter 8: Reordering Columns
Benefits of Changing Column Order
Moving Columns by Dragging
Moving Columns Relative to Other Columns
Conclusion

Chapter 9: Creating Custom Columns
Using Split by Delimiter
Using the Add Custom Columns Command
Conclusion

Chapter 10: Append Queries
Importing the Data
Removing the differences between columns
Using Append Queries as New
Conclusion

Chapter 11: Merge Queries
Using Merge Queries as New
Specifying the Join Kind
Conclusion

Chapter 12: Grouping Data
Benefits of Grouping Data
Adding Groupings
Adding Aggregations
Conclusion

Chapter 13: Power Query Parameters and Templates
Benefits of parameters and templates
Overview of Our Example
Creating Dynamic Titles
Creating parameters
Name and Description
Optional or Required
Parameter Type
Suggested Values
Default Value vs Current Value
Creating Parameters on the Fly
Updating a Data Source via a Parameter
Parameterizing Filter Operations
Creating a Template
Using a Template
Conclusion

Chapter 14. Power Query Custom Functions
Converting a Query to a Function
Connecting to our Example file
Creating a parameter
Invoking a Function
Using the Invoke Custom Function Command
Understanding Function Syntax
Creating a Date of Birth function
Conclusion