What this book covers
Chapter 1, Getting Started with Power Query, focuses on what Power Query is, how the tool has evolved, and where you can find/use it across Microsoft platforms. Then, we share when to use Power Query within each Microsoft service (Power BI, Excel, Analysis Services, Power Apps, and Azure Data Factory), giving you an idea of how different types of users can leverage the same tool for different purposes.
Chapter 2, Connecting to Fetch Data, shows an overview of connectors. Some best practices will be shared on how to connect to some of the most common connector types. The main ones identified are connections to files, folders, databases, and websites.
Chapter 3, Data Exploration in Power Query, focuses on data exploration features in Power Query. You will learn how to choose a subset of data and explore data profiling tools and query dependencies in order to see at a glance what data you will be dealing with. You will see how to smartly use query and step panes with some shortcuts and examples. Moreover, the schema and diagram views will be explained.
Chapter 4, Reshaping Your Data, focuses on how users can reshape their data. Most common transformation tasks will be shown, sharing best practices that you can apply to a wide range of dataset types. Other than data manipulation and wrangling, some artificial intelligence features such as Cognitive Services will be shown.
Chapter 5, Combining Queries for Efficiency, describes how users can combine different queries. Merge and append possibilities will be explained. Best practices for multiple file combinations are also shown.
Chapter 6, Optimizing Power Query Performance, aims to clarify what features you can leverage to optimize Power Query queries. The setup of parameters and their use will be explained and we will take a deep dive into how to best approach query folding. It is important for you to understand how query folding works and how to apply it in an incremental refresh scenario.
Chapter 7, Leveraging the M Language, gives an outline of M coding. The differences from the DAX language will be clarified and knowledge about how to deal with existing and new queries using M code will be shared. The chapter will focus both on simpler and more advanced scenarios involving M code.
Chapter 8, Adding Value to Your Data, aims to teach you how you can enrich your data by using add column features that range from simpler ones such as columns from examples to more advanced ones such as custom columns. Custom functions will be explored and examples will be shared. The cluster values feature, one of the most recent, will be given as an example.
Chapter 9, Performance Tuning with Power BI Dataflows, explains Power BI dataflows. It will focus on how users can leverage the Power Query engine to create dataflows, how to schedule a refresh, and how to allow other users to build data models by using dataflows as central data sources. The aim is to clarify what the best practices are, such as to prefer dataflows over Power Query in other Microsoft tools, and which are the most common scenarios for their use.
Chapter 10, Implementing Query Diagnostics, focuses on Power Query diagnostics. There is a specific tool for that and it is useful to describe how to use it and interpret its output.