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 use meaningful variable names when we create intermediate calculations.

3. We use variables or parameters to minimize, if not eliminate, hard coding. This makes our code more flexible and easier to maintain.

4. Power Query is case sensitive. We follow a consistent naming convention (e.g., InventoryAfterPlannedShipments).

5. We give Meaningful Column Names. We rename columns to reflect their content. Clear column names improve readability and understanding.

6. We create reusable custom functions to encapsulate common logic. Such functions are stored in separate queries for better organization.

7. We split complex transformations into smaller steps. We use intermediate variables (let-in blocks) to make our code more readable.

8. Power Query Models normally work with large datasets. We minimize unnecessary steps and transformations to optimize performance.

9. We use error handling techniques (e.g., try, otherwise) to handle unexpected situations. This helps avoid breaking our entire query due to a single error.

10. We test our transformations step by step to verify that each transformation produces the expected results.