# Tags **Software** : [[02 - Tags/Software/Power Query]] [[ChatGPT]] [[Python]] [[Power Bi]] **Functions** used : [[csv.Document]] [[Table.PromoteHeaders]] [[Table.TransformColumnTypes]] [[Table.ExpandTableColumn]] [[Table.AddColumn]] [[Python.Execute]] [[Table.RemoveColumns]] **Date** : 23-March-2026 # Description In this video i show you how to make a API Call using python in power query to perform sentiment analysis based on customer feedback. # Video Link <iframe width="560" height="315" src="https://www.youtube.com/embed/Ba_YNw2LNfM?si=XyFgr8yZ5OQi9h_N" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe> [GITHUB Source File Link](https://github.com/jbotes/powerbiTutorials/blob/main/customer_feedback.csv) [GITHUB Python Code](https://github.com/jbotes/powerbiTutorials/blob/main/666-SentimentAnalysis.ipynb) [GITHUB PowerBi File](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_SentimentAnalysis_Video.pbix) # M-Query ```mquery let Source = Csv.Document(File.Contents("C:\Users\User\OneDrive - basensei.com\Camtasia Videos\PQ - Sentiment Analysis\customer_feedback.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Customer message", type text}}), #"Added Custom" = Table.ExpandTableColumn(Table.AddColumn(#"Changed Type", "Custom", each sentimentfunction([Customer message])), "Custom", {"Score", "Explaination"}) in #"Added Custom" ``` ```mquery (Inputtext as text)=> let Source = #table({"CustomerMessage"}, {{Inputtext}}), #"Run Python script" = Python.Execute("import openai#(lf)import pandas as pd#(lf)import re#(lf)#(lf)# Set your OpenAI API key#(lf)openai.api_key = ""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"" # Replace with your actual key#(lf)#(lf)#(lf)def rate_call_sentiment_df(transcript, model=""gpt-4""):#(lf) prompt = (#(lf) ""You are a sentiment analysis expert.\n""#(lf) ""Given a customer support call transcript, rate the overall sentiment of the customer's experience on a scale from 1 to 10, where:\n""#(lf) ""- 1 = Extremely negative experience\n""#(lf) ""- 5 = Neutral or mixed experience\n""#(lf) ""- 10 = Extremely positive experience\n\n""#(lf) ""Consider the customer’s tone, language, satisfaction, and resolution of the issue.\n""#(lf) ""After giving the score, briefly explain in 2–3 bullet points why you chose that score.\n\n""#(lf) f""Here is the transcript:\n{transcript}""#(lf) )#(lf)#(lf) response = openai.chat.completions.create(#(lf) model=model,#(lf) messages=[#(lf) {""role"": ""system"", ""content"": ""You are a helpful assistant for sentiment scoring.""},#(lf) {""role"": ""user"", ""content"": prompt}#(lf) ],#(lf) temperature=0.2#(lf) )#(lf)#(lf) output = response.choices[0].message.content.strip()#(lf)#(lf) # Extract score#(lf) score = None#(lf) for match in re.findall(r'\b\d+\b', output):#(lf) num = int(match)#(lf) if 1 <= num <= 10:#(lf) score = num#(lf) break#(lf)#(lf) # Clean explanation#(lf) explanation = output#(lf) if score is not None:#(lf) explanation = re.sub(rf"".*?\b{score}\b[.:]?\s*"", """", output, count=1, flags=re.IGNORECASE | re.DOTALL).strip()#(lf)#(lf) return score, explanation#(lf)#(lf)transcript = dataset.CustomerMessage#(lf)transcript_a = transcript.iloc[0]#(lf)score, explaination = rate_call_sentiment_df(transcript_a)#(lf)#(lf)dataset[""Score""] = score#(lf)dataset[""Explaination""] = explaination",[dataset=Source]), Custom1 = Table.ExpandTableColumn(#"Run Python script", "Value", {"Score", "Explaination"}), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Name"}) in #"Removed Columns" ``` ## Optional Tags **Tags** : **Technique** : [[AP]]