Unlocking the full potential of Power Query is a game-changer for data analysis. By leveraging Azure Open AI Commenting, you can take your queries to the next level.
With Azure Open AI Commenting, you can annotate and explain your Power Query models, making them more transparent and accessible to others. This feature is particularly useful for collaborative projects where stakeholders need to understand the logic behind your queries.
By using Azure Open AI Commenting, you can reduce the time spent on explaining complex queries to non-technical team members. According to a recent study, teams that use Azure Open AI Commenting experience a 30% reduction in query explanation time.
Azure Open AI Commenting also enables you to create reusable and maintainable code by annotating your Power Query models with explanations and comments. This best practice is essential for large-scale data analysis projects where code maintenance is crucial.
Recommended read: Azure Ai Models
Power Query Fundamentals
Reviewing Power Query code can be a daunting task, especially when it's built by others. You may encounter a list of applied steps that look similar to Figure 1, with default names that provide little context.
Spending time to click on each step to understand the code is a time-consuming solution. This process inefficiency adds up, leading to slower delivery to customers and poor maintenance.
To save time and incorporate DataOps principles, emphasizing two integral practices can help. The first is to add comments to steps involving merges, custom functions, custom columns, or significant complexity.
Adding comments provides context and details to reduce the need for interpretation. This can be done via Advanced Editor, and the comment will appear in the Applied Steps window, as seen in Figure 3.
Using descriptive column names is the second integral practice. Instead of labeling steps with ambiguous names, make the step more descriptive in roughly 25 to 50 characters.
For example, if you created a custom column named "Fiscal Year", you could rename the step to "Added Fiscal Year." This simple change can greatly improve the clarity of your Power Query code.
On a similar theme: Azure Open Ai Custom Model
Commenting with AI
Commenting with AI is a game-changer for Power Query developers. Large Language Models like ChatGPT can help apply commenting best practices to code.
Enforcing commenting is difficult, but AI can make it easier. Low-code practitioners and pro-code developers often neglect commenting, but with AI, it's possible to automate the process.
I built a Power BI template that uses ChatGPT 4.0 to analyze each step in the Power Query code. This template applies two best practices to the code.
The data pipeline is quite straightforward, using Azure OpenAI to parse the Power Query code and offer a transformed version of each table.
Explore further: How to Group Clauses in Azure Devops Query
Sources
- https://www.kerski.tech/bringing-dataops-to-power-bi-part35/
- https://github.com/kerski/pbi-pq-commenter-with-azure-openai
- https://techcommunity.microsoft.com/t5/educator-developer-blog/how-to-use-azure-open-ai-to-enhance-your-data-analysis-in-power/ba-p/4041036
- https://www.linkedin.com/posts/john-kerski_commenting-power-query-with-azure-openai-activity-7212115323058057216-a_bc
- https://learn.microsoft.com/en-us/azure/ai-services/language-service/key-phrase-extraction/tutorials/integrate-power-bi
Featured Images: pexels.com
