Data Modeling with Microsoft Power BI

Chapter 13. Understanding a Data Model from the Power Query Point of View

Power BI’s tool to create the tables for and maintain the shape of the data model is Power Query. Power Query is directly built into the product and can be accessed via Home → “Transform data.” It opens in a separate window, which is convenient if you have more than one screen. Then you can make changes to the transformations in Power Query, refresh the data model, and test in the reports without closing the Power Query window.

All changes you make in the UI in the Power Query window are “recorded” as steps, which are applied to the source data. You can re-access every step (which is convenient when you need to debug the transformations). For many of the steps, you can also click on the gear icon to change a step through a dialog box (which, in most cases, matches the dialog box you use when you create a step in the first place). Throughout Part IV, I also show how to directly change the steps or edit a script in the advanced editor. The language of the script is the Power Query mashup language, or M for short. Unfortunately, it doesn’t have much in common with the DAX language.

Power BI dataflows are sometimes called “Power Query online.” They share most of the functionality of Power Query. Unfortunately, they aren’t completely identical in terms of features, but so close that you can copy most M scripts between the two tools. Azure Data Factory offers a similar experience via Data Wrangling in Azure Data ...