Transforming and Cleansing Data

The transformations applied within Power BI’s M queries serve to protect the integrity of the data model and to support enhanced analysis and visualization. The specific transformations vary based on data quality, integration needs, and the goals of the overall solution. At a minimum, developers should seek to protect the integrity of the model’s relationships and simplify the user experience via denormalization and standardization. Additionally, developers should converse with the owners of data sources to determine if required transformations can be implemented in the source or made available via SQL view objects.

This recipe demonstrates how to protect a model from duplicate values within the source data that can prevent the formation of proper relationships within the data model, which may even result in query failures. While a simple scenario is used, this recipe demonstrates scenarios you may run into while attempting to merge...