Build Your Own CRM in Excel Using Power Query Custom Data Types (No Code)

Transforming Data into a CRM

• 💡 This video demonstrates how to convert three separate Excel customer datasets (customer details, interactions, opportunities) into a searchable Customer Relationship Management (CRM) system.
• 🚀 The core technique involves using the custom data types feature within Power Query, requiring no coding, add-ins, or third-party applications, only Excel for Microsoft 365.

Power Query Data Type Creation

• 📥 Data is imported into Power Query from Excel tables, even if it's in separate files or a database.
• 🛠️ Within Power Query, each table is transformed into a custom data type (e.g., DT customers, DT opportunity, DT interactions) by selecting all columns and creating a data type, with a chosen display column like Customer ID.
• 🔗 Queries are renamed (e.g., Q customers) to differentiate from the data types, and then loaded as connections only to avoid cluttering the workbook.

Building the CRM Report Interface

• 📊 A new sheet is prepared to load the custom data types as tables, allowing for viewing underlying data by clicking on the data type icon.
• 📝 A report sheet is set up with labels for customer details, including a dropdown list for Customer ID created using data validation linked to the defined customer name list.
• 🔗 Formulas, using dot notation, are employed to extract specific fields (like first name, last name, industry, country, status, email) from the customer data type based on the selected Customer ID.
• ⚠️ Error handling with IFERROR is implemented to provide user prompts or blanks when no customer ID is selected or data is missing.

Integrating Interaction and Opportunity Data

• 💬 The FILTER function is used to pull interaction data, cross-referencing the Customer ID from the interactions data type with the selected customer on the report.
• ⚡ Formulas are then used to extract specific interaction details (date, channel, topic, outcome), with spilled arrays and error handling to automatically populate all relevant rows.
• 📈 For active leads, the FILTER function extracts customers with a status of 'active', and XLOOKUP is used to pull associated opportunities from the opportunities data type, referencing the Customer ID.
• 💰 Formulas extract opportunity details (name, stage, value), and formatting is applied for clarity, enabling filtering and analysis of active leads.

Updating and Expanding the CRM

• 🔄 Reports are automatically updated by clicking 'Refresh All' on the Data tab after changes are made to the source data file.
• 💡 The technique of using custom data types is versatile and can be applied to create other databases like product catalogs, employee directories, or inventory systems.