Chants of Power BI, Excel, Power Query and M

Clinton vs Trump – Whose campaign is more negative? Opponent-Mentions and Sentiment Analysis in Excel

Are You Ready for the Debate?

Today is the US Presidential debate. It is also a perfect opportunity to hone our analytical skills and answer some of the most intriguing questions of this unprecedented race.  According to CNN (here) we should “expect Clinton vs. Trump to be an Olympic battle of wits”. Well, whether we will get wits or just plain good mudslinging, one thing is certain, we will all get the “Bread and Circuses”.

During this race, both campaigns used negative messaging against their opponent. Did you ever wonder who strikes harder, and how it affects the audience? For the sake of learning new techniques in Excel and Power BI, today we will try to answer the following questions by looking into the candidates’ Facebook posts:

  1. Who mention the opponent the most? What is the rate of opponent mentions?
  2. Does the opponent mention rate change over time?
  3. Do people share more posts when the opponent is mentioned?
  4. Do people share more posts that have negative sentiment?
  5. Does the sentiment is more negative in posts in which the opponent is mentioned?

The method

We will use Excel 2016 Get & Transform (aka Power Query in Excel 2013 and 2010), and import Donald Trump’s and Hillary Clinton’s Facebook posts during 2016 (a total of ~4000 posts), including the number of shares each post received. Then we will split the posts of each candidate into two groups: Posts with mentions of the opponent, and posts without such mentions. Finally, we will apply sentiment analysis on the posts using Microsoft Cognitive Services, and answer the questions above. To read previous articles on Sentiment Analysis in Power BI (and Excel) click here.

Continue reading “Clinton vs Trump – Whose campaign is more negative? Opponent-Mentions and Sentiment Analysis in Excel”

Warming Up to the US Presidential Debate – Opponent Mentions Effect on Sentiment and Shares on Facebook

US Presidential Race - Opponent Mentions Effect on FacebookA busy weekend… The US Presidential debate is on Monday. I am using Excel, Power Query & Power Pivot to load all of Trump’s and Clinton’s Facebook posts, analyze their sentiment and check how sentiment scores and number of shares are affected when they mention their opponent.

Subscribe below to get the workbook 🙂 The detailed blog post will be published this Monday…


Subscribe To DataChant

Join our mailing list to receive exclusive content that was used in the preparation of this article.

Thank you for subscribing to DataChant. An email will be sent to you shortly with more details.


Presenting at Power BI Global User Group

It is a busy week at DataChant (Working on a well-customized on-site training for an advanced Power Query team in an amazing company; Meeting with a Finance Director from another company to boost their Power Pivot potential; and working on some collateral to share with potential new customers in the Pharmaceutical industry).

Yesterday, I published my blog post on PowerPivotPro, and DataChant broke a new record, reaching the Best-View-Ever of 929 views/day and 333 visitors/day.  I am honored to be part of the PowerPivotPro glorious team (Thank you Rob for this opportunity), and will do my best to continue sharing my experiences here and there.

If you are a new reader, welcome to DataChant!!! I am looking forward to helping you to combine your Super Power Pivot powers with the new Super Power Query ones. The two tools really shine together.

And now for the main status update of the day:

Tomorrow I am presenting on the Global Power BI User Group (Thank you Vishal for the invite). We will use Power BI Desktop to analyze the group Facebook page, find the most active users, and run Sentiment Analysis on the Group’s comments.

It will be a real fun, you are welcome to join. The session will start here at 11:30am PT.

You can leave some positive or negative comments on any post in the group, and we will run Sentiment Analysis on those comments tomorrow. Let’s find in Power BI who is most negative 🙂

Oh, and I almost forgot, three of the participants will get free Facebook Analytics of their pages, and their competitors, so you should really consider joining 🙂

More details:


Use DAX and Slicers to Define Thresholds for Sentiment Analysis

Use DAX and Slicers to define thresholds for Negative & Positive Sentiment

Sentiment Analysis in Power BI – Part 3

This is the third part of the Sentiment Analysis series. In the first part we learnt how apply Sentiment Analysis in Power BI without any server side code, or help from IT or Data Scientists. In the second part we refined the solution to support large content beyond 1000 messages.

In today’s post, I would like to focus on a different important aspect of our Sentiment Analysis implementation. According to Microsoft here, the “Text Analytics service returns a score between 0 and 1 denoting overall sentiment in the input text. Scores close to 1 indicate positive sentiment, while scores close to 0 indicate negative sentiment.”

So how should we treat a score of 0.65, or a score of 0.35? Is a score of 0.65 close enough to 1 to be considered as a positive sentiment?  How should we translate the numerical values into textual values: Negative, Neutral and Positive?

This is our main topic for today. We will use DAX and Slicers to give the analyst control in deciding the questions above. Moreover, the techniques we’ll describe today can be generalized and applied on any report where you need to translate numerical values into textual buckets (e.g. Low, Medium, High).

Our tutorial will start with static thresholds that are defined on the query level, and at the last part we will focus on dynamic thresholds that can be changed from slicers, as seen here:

Continue reading “Use DAX and Slicers to Define Thresholds for Sentiment Analysis”

I am working on a cheat sheet for Power Query and M, and was wondering if you would like to share your best tips, and take part in this project. The best tips will be included in the final cheat sheet with your credit of course (Blog, Twitter, etc.), and will be shared for free.

Contact me by email ([email protected]) if you are interested.

Subscribe to DataChant to get the Cheat Sheet when it is ready:


Subscribe To DataChant

Join our mailing list to receive exclusive content that was used in the preparation of this article.

Thank you for subscribing to DataChant. An email will be sent to you shortly with more details.


Power BI Force Graph and Facebook Page Affinity – Tips & Tricks

In today’s post, I’ll share with you few cool tips and tricks that helped me in the creation of the Force Graph below. If you are a DataChant subscriber, you will get the “source code” emailed to you.

The report connects to Facebook using the Graph API. It starts with three “root” Facebook Pages: Power BI, Tableau and Qlik, and collects general information such as page picture, and number of fans. Then it finds which Facebook pages are liked by the “root” pages, collects information on these pages, and extracts all the pages that they like.

The result is a graph that starts with three root nodes (Power BI, Tableau & Qlik), and traverses through their 1st level pages till it reaches the 2nd level ones.

The report itself is relatively advanced, and it was a significant effort to create it, so I am not sure I will write a full step-by-step tutorial (There are so many other things to share with you, and the time is a bit challenging).

Continue reading “Power BI Force Graph and Facebook Page Affinity – Tips & Tricks”

Facebook Page Affinity in Power BI Force Graph

Force Graph to visualize Pages that like other Pages

I love graphs, and was eager to explore Power BI Force Graph. Today I found a cool scenario to try:

Facebook pages can like other Facebook pages. Using Facebook Graph API, using a single API call, we can extract 3 levels of pages from a specific page, through its pages, to its pages’ pages. So I started with 3 Facebook pages: Power BI, Tableau and Qlik, and extracted the pages that they like. Then I extracted from the 2nd level pages, the 3rd level pages.

Here is the result:

If I get enough interest, I will write a detailed blog post with the steps to achieve this dashboard.

Sentiment Analysis in Power BI – Part 2

Beyond 1000 Messages

Today’s blog post will take you one step further with Sentiment Analysis in Power BI and will show you how easy it is to extend the solution beyond 1000 messages. Read and follow the steps of Part 1 before we proceed, or subscribe below to DataChant to receive the Power BI Template file that can be used as a starting point.

My recent article on Power BI Community blog (referred here as Part 1) demonstrated how easy it is to apply Microsoft Cognitive Services on messages, and perform Sentiment Analysis inside Power BI Desktop, and without any help from Data Scientists, or big data developers.

As Microsoft Cognitive service allows 1000 messages per API call, I decided to keep the last article short and simple to follow, and narrowed down our analysis to a single API call with the first 1000 messages.

So today we will learn how to handle thousands of messages and feed them to the Sentiment Analysis API in groups of 1000 messages. The great thing here is that we still do everything for free, as we are allowed to make 5000 API calls (5 million messages) per month.

Subscribe to DataChant and take the shortcut


Subscribe To DataChant

Join our mailing list to receive exclusive content that was used in the preparation of this article.

Thank you for subscribing to DataChant. An email will be sent to you shortly with more details.

If you didn’t follow all the steps in Part 1, don’t worry, you can take a shortcut and subscribe to my blog in the form above, and you’ll get the Power BI Template file of Part 1. When you get the template and open it, you will be asked to provide the Sentiment Analysis API key (You can learn in Part 1 how to get the API Key).

If you already followed Part 1, you should consider changing the sample data. Last time, we used an Excel workbook that had 1000 messages. This time you can use a larger data set. You can download it here, and change your query sources to this file.

Continue reading “Sentiment Analysis in Power BI – Part 2”

Power BI and MailChimp – Part 1

As some of you know, I recently started offering a free subscription service that includes sneak peaks to my new work and exclusive access to Power Bi Desktop files and Excel workbooks. To communicate with my subscribers I use the free MailChimp web service, which is a really awesome tool.

So today, while I am still waiting for Power BI team to publish my Sentiment Analysis blog post on their community blog (as promised here), I decided to try MailChimp content pack on Power BI. Here is a quick tutorial (with some bugs at the end, and some screenshots about my actual subscriber stats, so keep reading, if you want to “spy” on my MailChimp numbers).

Continue reading “Power BI and MailChimp – Part 1”

Proudly powered by WordPress | Theme: Baskerville by Anders Noren.

Up ↑

Sentiment Analysis in Power BI - Part 2

Receive Exclusive Sample Reports

Get the best out of DataChant blog:

As a DataChant subscriber you will receieve the actual Power BI Report files (.pbix, .pbit) and Excel workbooks (.xlsx) that were created in preparation of the tutorials.


Thank you for subscribing to Data Chant. You will receieve a confirmation email shortly.

Gil Raviv is the author of

Need Help?

Hire a TOP consultant on Power BI, Excel BI, Power Pivot, DAX, Power Query, M & Visualization.

Boost up your team with an amazing on-site Training.

Secure the success of your project with ad-hoc or ongoing help.

You have Successfully Subscribed!

Powered byRapidology