Revolutionize Power BI Queries with OpenAI

This article is an excerpt from the book,

Figure 15.4 – Screenshot of the chat playground from within Azure OpenAI

Now that you have set up an instance of OpenAI, here’s an example conversation taken from OpenAI, where the chat functionality has been used to assist with writing M queries in Power BI:

User: Hi, I’m working on a Power BI project, and I need to transform some data using M queries. I have a table with sales data, and I want to calculate the total sales for each product category. Can you help me with the M query for that?

ChatGPT: Of course! To calculate the total sales for each product category, you can use the Group By transformation in Power BI’s Power Query Editor. Here’s a sample M snippet for your scenario:

let
   Source = YourDataSource,
   GroupedTable = Table.Group(Source, {"ProductCategory"}, 
{{"TotalSales", each List.Sum([Sales]), type number}})
in
   GroupedTable

Replace YourDataSource with the actual name of your data source. This code groups the data by the ProductCategory column and calculates the total sales for each group.

User: Thanks! That’s a great starting point. What if I want to filter out products with sales less than $1,000  before calculating the total sales?

ChatGPT: You can add a filtering step before the Group By transformation. Here’s an updated version of the M:

let
   Source = YourDataSource,
   FilteredTable = Table.SelectRows(Source, each [Sales] >= 1000),
   GroupedTable = Table.Group(FilteredTable, {"ProductCategory"}, 
{{"TotalSales", each List.Sum([Sales]), type number}})
in
   GroupedTable

This code filters out rows where sales are less than $1,000 before grouping the data by product category and calculating the total sales.

User: Perfect! This is really helpful. One more thing – what if I want to sort the results in descending order based on total sales?

ChatGPT: You can add a sorting step using the Table.Sort function. Here’s the modified M:

let
   Source = YourDataSource,
   FilteredTable = Table.SelectRows(Source, each [Sales] >= 1000),
   GroupedTable = Table.Group(FilteredTable, {"ProductCategory"}, 
{{"TotalSales", each List.Sum([Sales]), type number}}),
   SortedTable = Table.Sort(GroupedTable,{{"TotalSales", Order.
Descending}})
in
   SortedTable

This code sorts the grouped table in descending order based on the “TotalSales” column.

This interaction represents a simple example of how those who can leverage the power of tools such as OpenAI and C hatGPT will be able to quickly upskill in areas such as coding. It has to be said, though, that while this is still in its infancy, it’s important to always test and validate the answers provided before implementing them in production. Also, ensure that you take precautions when using the publicly available ChatGPT model to avoid sharing sensitive data publicly. If you would like to use sensitive data or you want to ensure that requests are given within a secured governed environment, make sure to use the ChatGPT model within your own Azure OpenAI instance.

In more complex examples, optimizing Power Query transformations could involve efficient interaction with Azure OpenAI. This includes streamlining API calls, managing large datasets, and incorporating caching mechanisms for repetitive queries, ensuring a seamless and performant data cleaning process.

As we begin to explore the use cases where this technology can be most effective, there are a number of clear early winners:

• Optimizing query plans: ChatGPT’s natural language understanding can assist in formulating more efficient Power Query plans. By describing the desired transformations in natural language, users can interact with ChatGPT to generate optimized query plans. This involves selecting the most suitable Power Query functions and structuring transformations for performance gains.
• Caching strategies for repetitive queries: ChatGPT can guide users in devising effective caching strategies. By understanding the context of data transformations, it can recommend where to implement caching mechanisms to store and reuse intermediate results, minimizing redundant API calls and computations. The following is an example of just this, where I have asked Azure OpenAI to verify and optimize my query from the Power Query Advanced Editor. The model suggested I use the Table.Buffer function to help cache the table in memory and optimize the query.

Figure – An example response from OpenAI to help describe my query

• Error handling strategies: Optimizing Power Query also entails robust error handling. ChatGPT can provide recommendations for anticipating and handling errors gracefully within a query. This includes strategies for logging errors, implementing fallback mechanisms, and ensuring the stability of the overall data preparation process.

In this section, you learned how to optimize Power Query transformations with Azure OpenAI efficiently. Key takeaways include using ChatGPT for natural-language-based query planning and effective caching strategies. Insights include handling large datasets through parallel processing, early filtering, and aggregations. This knowledge equips you to streamline and enhance your Power Query processes effectively.

In the next section, you will learn about Microsoft  Copilot, how to set up a Power BI instance with Copilot activated, and also how you can use this new AI technology to help clean and prepare your data.

Conclusion

In conclusion, Azure OpenAI with ChatGPT presents a game-changing solution for maximizing Power BI's potential. From query optimization to error-handling strategies, this integration streamlines processes and enhances productivity. As users navigate complex data transformations, the guidance provided fosters efficient decision-making and empowers users to tackle challenges with confidence. With Azure OpenAI and ChatGPT, the possibilities for revolutionizing Power BI workflows are endless, offering a glimpse into the future of data transformation and analytics.

Author Bio

Gus Frazer is a seasoned Analytics Consultant focused on Business Intelligence solutions. With over 7 years of experience working for the two market-leading platforms, Power BI & Tableau, has amassed a wealth of knowledge and expertise. Gus has helped hundreds of customers to drive their digital and data transformations, scope data requirements, drive actionable insights, and most important of all, cleanse data ready for analysis. Most recently helping to set up, organize and run the Power BI UK community at Microsoft. He holds 6 Azure and Power BI certifications, including the PL-300 and DP-500 certifications. In this book, Gus offers readers invaluable guidance on ingesting, preparing, and cleansing data for analysis in Power BI. --This text refers to an out of print or unavailable edition of this title.