Using OpenAI and Azure OpenAI in Power BI Dataflows

Chapter 12 of this book provided an overview of the OpenAI and Azure OpenAI technologies. The chapter also explored methods to incorporate these cutting-edge AI technologies into your use case using FAA Wildlife Strike data. To enhance the quality of the content in Chapter 12, OpenAI ChatGPT was utilized to generate better text. Moving forward, ChatGPT will continue to be used on occasion as a tool to improve the writing quality of the rest of the book.

Furthermore, there are two use cases for OpenAI and Azure OpenAI technologies that are discussed in detail in this chapter. Specifically, you will focus on their applications in summarization and descriptive content generation, as discussed in Chapter 12. To illustrate these use cases, a project is included in this chapter.

In this chapter, you will see how to integrate OpenAI (or Azure OpenAI) into your Power BI solution for FAA Wildlife Strike data. Through this integration...

Technical requirements

For this chapter, you’ll need the following:

• An account with the original open source OpenAI: https://openai.com/.
• Optional – Azure OpenAI as part of your Azure subscription: https://azure.microsoft.com/en-us/products/cognitive-services/openai-service. The book is written so that this is optional since it is not available to everyone at the time of publication.
• FAA Wildlife Strike data files from either the FAA website or the Packt GitHub site.
• A Power BI Pro license.
• One of the following Power BI licensing options for access to Power BI dataflows:
  • Power BI Premium
  • Power BI Premium Per User
• One of the following options for getting data into the Power BI cloud service:
  • Microsoft OneDrive (with connectivity to the Power BI cloud service)
  • Microsoft Access + Power BI Gateway
  • Azure Data Lake (with connectivity to the Power BI cloud service)

Configuring OpenAI and Azure OpenAI for use in your Power BI solution

Prior to proceeding with the configuration of OpenAI and Azure OpenAI, it is important to note that OpenAI is still a nascent technology at the time of writing this book. In the future, the integration of OpenAI with Power BI may become less technical, as advancements in the technology continue to be made. However, the use cases that will be demonstrated in this chapter will remain applicable.

As such, the instructions provided in this chapter will showcase how this integration can be used to enhance your data analytics capabilities in the context of Power BI.

Configuring OpenAI

You can create an account in OpenAI (if you do not have one already) from this link: https://chat.openai.com/auth/login. At the time of writing, new accounts are granted trial credits to begin using OpenAI. If you run out of trial credits, or if the trial is no longer offered after this book has been written, you may need to pay...

Preparing a Power BI dataflow for OpenAI and Azure OpenAI

In Chapter 12, you decided to use OpenAI for two use cases with your FAA Wildlife Strike database project:

• Generating descriptions of airplane models and the operator of the aircraft, for each incident
• Summarizing the free text remarks provided in the report for each incident

Since OpenAI is still new at the time of writing this book, Power BI does not yet have connectors built into the product. But you can still call OpenAI and Azure OpenAI APIs from both Power Query and Power BI dataflows using custom M scripts. Let’s get started!

First, you will create a new dataflow for use with OpenAI and Cognitive Services in Power BI:

1. From your Power BI workspace, on the ribbon, select New | Dataflow.
2. Select Define new tables | Link tables from other dataflows.
3. Sign in and click Next.
4. Expand your workspace.
5. Expand the Strike Reports dataflow and check Strike Reports Curated New.
...

Creating OpenAI and Azure OpenAI functions in Power BI dataflows

As noted earlier, integrating OpenAI and Azure OpenAI with Power Query or dataflows currently requires custom M code. To facilitate this process, we have provided M code for both OpenAI and Azure OpenAI, giving you the flexibility to choose which version to use based on your specific needs and requirements.

By leveraging this provided M code, you can seamlessly integrate OpenAI or Azure OpenAI with your existing Power BI solutions. This will allow you to take advantage of the unique features and capabilities offered by these powerful AI technologies, while also gaining insights and generating new content from your data with ease.

OpenAI and Azure OpenAI functions

OpenAI offers a user-friendly API that can be easily accessed and utilized from within Power Query or dataflows in Power BI. For further information regarding the specifics of the API, we refer you to the official OpenAI documentation, available at this...

Using OpenAI and Azure OpenAI functions in Power BI dataflows

Your next step is to prep the FAA Wildlife Strike data for the OpenAI or Azure OpenAI functions to do content generation and summarization. As discussed previously, you’ll need to build effective prompts to provide context for the OpenAI models to process. The completed query for Reports Curated New OpenAI, which can be copied and pasted into Power BI, is also available from the Packt GitHub repository: https://github.com/PacktPublishing/Unleashing-Your-Data-with-Power-BI-Machine-Learning-and-OpenAI/tree/main/Chapter-13.

Looking back to Chapter 12, you decided upon the following combinations of new text with row-level content from the FAA Wildlife Strike database:

• Generating new information about airplanes and the carriers: Tell me about the airplane model [Aircraft] operated by [Operator] in three sentences:
• Summarizing the remarks from each report, including the operator, aircraft, and species: Summarize...

Adding a Cognitive Services function to the solution

Finally, you decide to add more value to the deliverables by showcasing the Cognitive Services capabilities in Power BI. In both Power BI Desktop and Power BI dataflows, Cognitive Services is built in as part of the SaaS tool. Details about using Cognitive Services in Power BI can be found at this link: https://learn.microsoft.com/en-us/power-bi/connect-data/service-tutorial-use-cognitive-services.

Cognitive Services refers to Azure services that can be called using APIs to score data with standard ML models. These tools can score the sentiment of text, identify images, extract key phrases, detect emotion in pictures, and more. The following Cognitive Services features are native to Power BI at the time of writing this book:

• Detect Language
• Tag Images
• Score Sentiment
• Extract Key Phrases

For this final addition to the project, you will use the Score Sentiment function on the Remarks column of the FAA...

Summary

This chapter covered the integration of OpenAI or Azure OpenAI with Power BI, along with a use case for Power BI Cognitive Services. By leveraging these advanced tools, you were able to generate new descriptions of airplanes using external data and GPT models, summarize remarks about wildlife strikes, and even score the remarks for sentiment analysis.

Moving forward, the next and final chapter will provide an opportunity to review and reflect on the work accomplished throughout this book and the accompanying workshop. We will also begin to explore new strategies for enhancing and expanding upon the existing project, as we recognize that projects are never truly complete if there is still potential for additional value to be achieved.