Project Information

Objective

Power Automate is a highly versatile tool that enables seamless interaction across various software within the Microsoft 365 ecosystem. Its diverse set of features becomes even more powerful with the integration of AI, offering advanced automation capabilities. However, the elevated costs remain a challenge, particularly when deploying AI-driven tools in production, highlighting the need for cost-effective solutions.

To address this, I have designed a system that integrates Power Automate to simplify query generation in Power BI. By utilizing prompts, the system searches datasets, identifies relevant tables, and leverages the Deepseek API—an affordable AI tool—to generate DAX queries. This approach empowers users to perform queries independently, reducing dependency on the BI team and allowing us to focus on more strategic initiatives.

Workflow Steps:

  1. User Prompt: The user provides a query prompt to the system, such as: "I want the top 10 users in terms of revenue."
  2. AI Query Trigger: Power Automate sends the prompt via an HTTP request to the AI, which analyzes it, scans the dataset’s table descriptions, and identifies the necessary table names and columns.
  3. Generate DAX Query: The system triggers a second HTTP request to create the corresponding DAX query based on the identified tables and columns.
  4. Data Transformation: The results are processed using Power Automate’s "Compose" action to extract only the essential information for the DAX query.
  5. Run DAX Query: The generated DAX query is executed against the dataset, and the output is returned in JSON format.
  6. Add Data to Excel: The resulting data is formatted and added as a new row in an Excel report.
  7. Send Report: The final report is sent to the user via email using the Outlook connector.