Creating OpenAI and Azure OpenAI functions in Power BI dataflows

Dive deeper into the world of AI innovation and stay ahead of the AI curve! Subscribe to our AI_Distilled newsletter for the latest insights. Don't miss out – sign up today!

This article is an excerpt from the book, Power BI Machine Learning and OpenAI, by Greg Beaumont. Master core data architecture design concepts and Azure Data & AI services to gain a cloud data and AI architect’s perspective to developing end-to-end solutions 

Introduction

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 link: https://platform.openai.com/ docs/introduction/overview.

It is worth noting that optimizing and tuning the OpenAI API will likely be a popular topic in the coming year. Various concepts, including prompt engineering, optimal token usage, fine-tuning, embeddings, plugins, and parameters that modify response creativity (such as temperature and top p), can all be tested and fine-tuned for optimal results.

While these topics are complex and may be explored in greater detail in future works, this book will focus primarily on establishing connectivity between OpenAI and Power BI. Specifically, we will explore prompt engineering and token limits, which are key considerations that will be incorporated into the API call to ensure optimal performance:

• Prompts: Prompt engineering, in basic terms, is the English-language text that will be used to preface every API call. For example, instead of sending [Operator] and [Airplane] as values without context, text was added to the request in the previous chapter such that the API will receive Tell me about the airplane model [Aircraft] operated by [Operator] in three sentences:. The prompt adds context to the values passed to the OpenAI model.
• Tokens: Words sent to the OpenAI model get broken into chunks called tokens. Per the OpenAI website, a token contains about four English language characters. Reviewing the Remarks column in the Power BI dataset reveals that most entries have up to 2,000 characters. (2000 / 4) = 500, so you will specify 500 as the token limit. Is that the right number? You’d need to do extensive testing to answer that question, which goes beyond the scope of this book.

Let’s get started with building your OpenAI and Azure OpenAI API calls for Power BI dataflows!

Creating OpenAI and Azure OpenAI functions for Power BI dataflows

You will create two functions for OpenAI in your dataflow named OpenAI. The only difference between the two will be the token limits. The purpose of having different token limits is primarily cost savings, since larger token limits could potentially run up a bigger bill. Follow these steps to create a new function named OpenAIshort:

1.      Select Get data | Blank query.

2.      Paste in the following M code and select Next. Be sure to replace abc123xyz with your OpenAI API key.

Here is the code for the function. The code can also be found as 01 OpenAIshortFunction.M in the Packt GitHub repository at https://github.com/PacktPublishing/ Unleashing-Your-Data-with-Power-BI-Machine-Learning-and-OpenAI/ tree/main/Chapter-13:

let
   callOpenAI = (prompt as text) as text =>
       let
           jsonPayload = "{""prompt"": """ & prompt & """,             ""max_tokens"": " & Text.From(120) & "}",
           url = "https://api.openai.com/v1/engines/ 
           text-davinci-003/completions",
           headers = [#"Content-Type"="application/json",              #"Authorization"="Bearer abc123xyz"],
           response = Web.Contents(url, [Headers=headers,              Content=Text.ToBinary(jsonPayload)]),
           jsonResponse = Json.Document(response),
           choices = jsonResponse[choices],
           text = choices{0}[text]
       in
           text
in
   callOpenAI

3.      Now, you can rename the function OpenAIshort. Right-click on the function in the Queries panel and duplicate it. The new function will have a larger token limit.

4.      Rename this new function OpenAIlong.

5.      Right-click on OpenAIlong and select Advanced editor.

6.      Change the section of code reading Text.From(120) to Text.From(500).

7.      Click OK.

Your screen should now look like this:

Figure 13.1 – OpenAI functions added to a Power BI dataflow

These two functions can be used to complete the workshop for the remainder of this chapter. If you’d prefer to use Azure OpenAI, the M code for OpenAIshort would be as follows. Remember to replace PBI_OpenAI_project with your Azure resource name, davinci-PBIML with your deployment name, and abc123xyz with your API key:

let
   callAzureOpenAI = (prompt as text) as text =>
       let
           jsonPayload = "{""prompt"": """ & prompt & """,              ""max_tokens"": " & Text.From(120) & "}"
           url = "https://" & "PBI_OpenAI_project" & ".openai.azure. 
           com" & "/openai/deployments/" & "davinci-PBIML" & "/ 
           completions?api-version=2022-12-01",
           headers = [#"Content-Type"="application/json",              #"api-key"="abc123xyz"],
           response = Web.Contents(url, [Headers=headers,              Content=Text.ToBinary(jsonPayload)]),
           jsonResponse = Json.Document(response),
           choices = jsonResponse[choices],
           text = choices{0}[text]
       in
           text
in
   callAzureOpenAI

As with the previous example, changing the token limit for Text.From(120) to Text.From(500) is all you need to do to create an Azure OpenAI function for 500 tokens instead of 120. The M code to create the dataflows for your OpenAI functions can also be found on the Packt GitHub site at this link: https://github.com/PacktPublishing/Unleashing-Your-Data-withPower-BI-Machine-Learning-and-OpenAI/tree/main/Chapter-13.

Now that you have your OpenAI and Azure OpenAI functions ready to go in a Power BI dataflow, you can test them out on the FAA Wildlife Strike data!

Conclusion

In conclusion, this article has provided valuable insights into integrating OpenAI and Azure OpenAI with Power BI dataflows using custom M code. By offering M code for both OpenAI and Azure OpenAI, it allows users to seamlessly incorporate these powerful AI technologies into their Power BI solutions. The article emphasizes the significance of prompt engineering and token limits in optimizing the OpenAI API. It also provides step-by-step instructions for creating functions with different token limits, enabling cost-effective customization.

With these functions in place, users can harness the capabilities of OpenAI and Azure OpenAI within Power BI, enhancing data analysis and content generation. For further details and code references, you can explore the provided GitHub repository. Now, armed with these tools, you are ready to explore the potential of OpenAI and Azure OpenAI in your Power BI data projects.

Author Bio

Greg Beaumont is a Data Architect at Microsoft; Greg is an expert in solving complex problems and creating value for customers. With a focus on the healthcare industry, Greg works closely with customers to plan enterprise analytics strategies, evaluate new tools and products, conduct training sessions and hackathons, and architect solutions that improve the quality of care and reduce costs. With years of experience in data architecture and a passion for innovation, Greg has a unique ability to identify and solve complex challenges. He is a trusted advisor to his customers and is always seeking new ways to drive progress and help organizations thrive. For more than 15 years, Greg has worked with healthcare customers who strive to improve patient outcomes and find opportunities for efficiencies. He is a veteran of the Microsoft data speaker network and has worked with hundreds of customers on their data management and analytics strategies.