What this book covers
Chapter 1, Installation and Setup. Power Query is now integrated into all the data analysis or business intelligence tools from Microsoft such as Excel, Analysis Services, and Power BI. It allows users to discover, combine, and refine their data from various sources. In this chapter, you will learn how to install and access these tools across multiple office versions.
Chapter 2, Power Pivot Basics, Inadequacies, and Data Management, introduces you to the shortcomings of Power Pivot in handling complex data and offers Power Query as a solution to retrieve, extract, and reshape data. You will be taken through an example to demonstrate the differences between Power Query and Power Pivot and to learn how to convert worksheet data into a table.
Chapter 3, Introduction to the Power Query Interface, introduces you to the Power Query interface and takes you on a journey through its tabs, creating a basic Power Query query and visiting the View tab in Power BI to set data profiling options, as well as discovering how to send data back to an Excel workbook.
Chapter 4, Connecting to Various Data Sources Using Get & Transform, explains how to connect to numerous data sources using the Get & Transform tool, known as Power Query, and investigate data source settings.
Chapter 5, Transforming Power Query Data, covers how to reshape tabular data, including altering rows, columns, and tables using a multitude of Power Query tools.
Chapter 6, Advanced Power Queries and Functions, concentrates on the more advanced queries and functions in Power Query, such as the IF, Index, and Modulo functions. You will learn to create parameters to alter query paths and append multiple files and sheet tabs.
Chapter 7, Automating Reports in Power Query, goes through the options provided by Power Query to streamline and automate reports from multiple sources. In this chapter, we will look at creating a report from multiple files in a folder to a single dataset, which will update when new data is added to the Power Query data folder.
Chapter 8, Creating Dashboards with Power Query, looks at dashboards, which are a business-intelligent, single-canvas page that allows the user to tell a story through various visualizations created from table data to highlight important data points for an organization. In this chapter, you will learn how to create a dashboard from connected data, select a visualization type, and publish and customize the dashboard. We will also cover multi-dimensional reporting.
Chapter 9, Working with M introduces the Power Query M language and explains how to use and write the syntax, including steps to reveal a list of functions and definitions. This chapter will start by explaining how M got its name and how Microsoft tried to change it. We will also look at the structure and syntax of M. All programming languages have a specific structure and once you master the structure of M, it becomes much easier to understand and use. We will look at the main data types and functions and provide a walkthrough demonstration of how to use each of these data types, before looking at how to import a CSV file using M.
Chapter 10, Examples of M Usage, concentrates on a few examples of using M, including the concatenate function, which first compares the difference between formulas in Excel and Power BI before looking at the ampersand operator (&) and how it can be used. This chapter examines how Text.From and Text.Combine can be used to join and concatenate different strings, dates, and columns. There is an extensive section on how to set up your own free and legal SQL server for you to use for non-commercial purposes. It has full functionality, and we will also cover how to import the AdventureWorks databases into SQL for us to be able to use them as a resource. Lastly, this chapter concentrates on parameters and how they can be used effectively in filtering data sources, adding parameters to control statements that allow us to filter according to different dates. We will continue by adding parameters to order objects and columns in ascending and descending order, before looking at how we can make these changes in Power BI's Data view.
Chapter 11, Creating a Basic Custom Function, will take you through the steps to create functions manually using M in Power Query, as well as how create a date and time column using functions.
Chapter 12, Differences Between DAX and M, looks at the differences between M, which is the mashup functional language of Power Query and is used to query numerous data sources, and DAX, which allows functions to work on data stored in tables, much like Excel. In this chapter, you will learn about the differences between the two languages by working through examples and learning how to create calculated measures.