Data Modeling with Microsoft Power BI

Chapter 14. Building a Data Model with Power Query and M

Once you understand the moving parts of Power Query and the M language (see Chapter 13), it’s time to see what this tool has in store for you to bring a data model into shape. You can achieve many tasks by finding your way around in the UI. No matter what kind of transformation you are applying, every step is “recorded” as a step in a script in language M. Think of this feature as something similar to Excel’s macro recorder. Some steps will show a gear icon, which will open a dialog box and guide you through options to change a step’s logic. You can also directly edit the scripts, if you want.

In this chapter, you’ll learn how and why to touch some of the scripts directly. Editing the script is sometimes just faster, compared to navigating through the UI with the mouse. In other cases, it might be that a feature of the powerful language M is just not available via the UI. Especially when it comes to making the transformation either flexible or resilient against changes in the data source, M can do some magic.

You’ll also learn how to normalize fact tables and denormalize dimension tables (to form a star schema) using Power Query. I’ll introduce you to calculations in M and show you how you can transform flags and indicators into user-friendly text. A whole section is dedicated to creating a date table, which is mandatory for time-intelligence calculations in DAX and increases the usability of a data model by offering a variety ...