Chants of Power BI, Power Query and M

Open for Business

Blogging is fun, but sometimes one has to make a living. Therefore, I am happy to announce that DataChant is now open for business, and will offer the following premium services:

  • Power Query SOS: Contact us for any challenge you have with Power Query, and we will deliver. It can start with an ad-hoc challenge or an ongoing support.
  • DataChant Facebook Dashboards: Did you like our dashboards (see examples on our Gallery page). You can now order any of these dashboards, or request a customized version.
  • Onsite Training: Power BI & Power Query 2 day on-site training that is tailored to your specific needs.
  • Transform and Unpivot ANY data to meet your analytical needs. We may even do it for free, if you let us publish our work (without revealing any confidential information, of course).
  • Sub-contracting your projects. I know you are the best of the best, but next time you have a flood of new projects, you can contact me.
  • Be your Power BI Desktop specialist on your Power BI Embedded projects.


Featured post

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.


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 to take the shortcut

If you didn’t follow all the steps in Part 1, don’t worry, you can take a shortcut and subscribe to my blog, and I will send you the Power BI Template file with the queries 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”

Sneak Peek: Sentiment Analysis in Power BI

Mark the date: Aug 1st. This is the date when the Power Bi team at Microsoft will publish my first blog post on Sentiment Analysis, and you will be able to learn the steps to create this dashboard (click here to open it in a separate window).

Subscribe below to DataChant and I will send you the actual .pbix file once the blog post gets published.

Till then, check out which post got today high rate of “Haha” in reaction to Donald J. Trump’s post, and see its Sentiment score.

Sentiment Analysis in Excel & Power BI

Today I would like to kickoff a special series on DataChant blog – For the first time ever,  we will incorporate Sentiment Analysis to our Excel workbooks and Power BI dashboards.

This series of blog posts will walk you through the use of Sentiment Analysis in Excel and Power BI. We will apply the Microsoft Cognitive Services Text Analytics API which is available here (Free of charge for 5000 API calls/month).

Would you like to get an Excel workbook sample that performs the Sentiment Analysis on Trump’s and Clinton’s Facebook pages, before I publish its walkthrough? Subscribe in the form below.

Subscribe to DataChant to get this workbook
Subscribe to DataChant to get this workbook

Use DAX for dynamic events in Power BI Pulse Chart

Update (Aug 16, 2016): The latest Power BI Desktop doesn’t allow us to assign measures as Event Description. As a result, you cannot create the dynamic events, and this tutorial will break. As a subscriber, you can still download the .pbix file and see the dynamic events in action. The bug doesn’t affect existing Pulse Charts. I contacted Microsoft and they confirmed that this is a known issue “We’re going to fix it in the following versions of the PulseChart visual“. Will keep you updated.

Continue reading “Use DAX for dynamic events in Power BI Pulse Chart”

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

Up ↑