These are our TEN COMMANDMENTS for “M” language-based Power Query Models. These help us clean and harmonize dirtiest and most unstructured data.

1. Comment. Comment. Comment: We add comments to explain complex expressions or steps. We Document our thought process and reasoning.

2. We always create a “sort by” column for all major dimensions.

3. We ensure that 99% of relationships in our data models are one-to-many relationships only.

4. We build a tightly constructed model that’s easier to work with and uses less memory .

5. Data Models have in-memory analytics engine which implements powerful compression techniques to reduce storage requirements.

6. We use only native DAX functions instead of Excel formulas.

7. We avoid the use of ALL in calculated columns, nested IFs and SWITCH, unnecessary CALCULATE calls, EARLIER, SUMMARIZE, DISTINCT expressions. This helps improve speed of model.

8. We use calculated columns instead of measures for static values.

9. We use variables or parameters to minimize, if not eliminate, hard coding. This makes our code more flexible and easier to maintain. Variables Improve our formulas and enhance performance, readability, and debugging. We assign intermediate results to variables to avoid redundant calculations. Example: VAR SalesPriorYear = CALCULATE([Sales], PARALLELPERIOD('Date'[Date], -12, MONTH)) RETURN DIVIDE(([Sales] - SalesPriorYear), SalesPriorYear)

10. We format Your DAX Codes. Properly formatted DAX is essential for readability.