Chants of Power BI, Excel, Power Query and M

Power BI – Improved Query Function Authoring

Power BI Desktop November Update (2016) is out with amazing new features. In today’s blog post, I would like to focus on a new functionality which simplifies the authoring experience of query functions.


As you may know, each step in Query Editor is transformed to a line of code (as part of the Power Query Formula Language, informally known as M – Learn more about it here). While each line of transformation is available for tweaks in the Formula Bar, you can also manipulate the entire expression in the Advanced Editor.

Prior to this release, you could convert a query into a function, from the Advanced Editor, by modifying some static values into parameters, and adding a function declaration with the parameters as arguments. The following code changes illustrates the creation of a query function:

This query loads the first page from Star Wars API. After the modification it is turned into a function that gets a page by its number.

But once you converted a query into a function, you couldn’t  manipulate its steps through the UI. All the steps in APPLIED STEPS were reduced to a single step, as shown here:


The good news in today’s update, is that you can now relatively easy create a function from an existing query. More importantly, you can now edit the function steps, by manipulating the sample query in the UI. Thus, any changes in the sample query that is done in the UI will automatically affect the corresponding function, and all the queries that invoke it.

Continue reading “Power BI – Improved Query Function Authoring”

Use VBA to Automate Power Query scenarios in Excel 2016

In case you missed this important functionality in Excel 2016, today’s short blog post is written to raise some attention to a wonderful feature. You can write VBA code or PowerShell scripts to automate the creation of queries, change their M expression and copy queries from one workbook to another.

Here are few common scenarios you can achieve with automation:

  1. Manage queries, copy, delete or search for specific query name, description or M formula.
  2. Toggle queries’ data sources from staging to production environments.
  3. Automatically manipulate the M expression on massive number of queries.

Download my sample workbook here to learn how to manage queries, load them to the worksheet or Data Model. The sample was downloaded 3,976 times so far (11/22/2016).

Download my sample PowerShell scripts here to learn how you can copy and paste queries from one workbook the another.

If you are interested in Power Pivot automation, there are many new related interfaces in Excel 2016 that can be found here (anything that starts with Model.*).

Are you familiar with this functionality? Share the way you used it in the comments below.



Improve refresh time by batching Web API calls – Part 2

Following my last post here, Imke Feldmann, author of THE BICCOUNTANT, proposed a refined version for the solution. While my original solution is simpler for users who are reluctant of manipulating Power Query (M) expressions, Imke’s solution is simpler for advanced users. It contains less steps (It’s shorter by 8 UI step, and 10 transformation steps in M) and it’s more robust.

Continue reading “Improve refresh time by batching Web API calls – Part 2”

Improve refresh time by batching Web API calls – Yahoo Finance example

In today’s blog post we will learn a neat Power Query trick that is extremely useful in improving refresh time on certain Web API calls (Like Yahoo Finance, Quandl and Twitter).

Whenever you use Power Query to import data from a Web API that accepts certain object IDs, if you apply a single object ID in each API call, you will eventually experience a slow refresh, and reach the API limits, especially if the number of object IDs is high.

Some API calls allow you to better utilize their resources, by working with batches of object IDs. For example, on Yahoo Finance you can get the current Ask and Bid for AAPL, GOOG and MSFT, by calling these three API calls (learn more here):

But you can get the data faster by calling all quotes at once:

Imagine that you have thousands of symbols you wish to monitor. In the past you probably applied a single API call for each object ID using a function query and the Custom Column in Power Query. Today we’ll show you how to create batches of object IDs to reduce the number of API calls, as shown in this diagram. We will walk through the Yahoo Finance example.

Merging multiple IDs to improve Web API Refresh time

Continue reading “Improve refresh time by batching Web API calls – Yahoo Finance example”

Sneak Peek – Extracting Key Phrases from Text Messages in Power BI

With only three days until the US Presidential Election, here is a Power BI report that extracts Trump’s and Clinton’s key phrases from their Facebook status updates during the race.

In the upper WordCloud visual the most used phrases are bigger. In the lower WordCloud visual the phrases that got the higher number of shares are bigger.

Curious to learn how to create this report? Stay tuned for my next blog post on Power BI Community blog, where we will go through the steps to import Facebook posts, extract key phrases using Microsoft Cognitive Services, and build the main visuals. The blog post will be published next week, but only after Tuesday, so nobody will blame me for rigging this election 🙂

Feel free to share the report above, using this link.

Column Grouping in Power BI Desktop October Update

At the end of each month, the Power BI team releases a new update to Power BI Desktop. You can go here to learn what’s new in the latest version.

The October release is out – Version: 2.40.4554.361 64-bit (October 2016), and soon Microsoft will announce their new features. (Short update: The new features were announced four hours after I published this blog post. And there is a Snap-To-Grid preview. Check out the instructions below).

Here are my takes on one of the most coolest features you can find in this update: Grouping of Columns. It is fun to catch a feature before it gets announced.

Continue reading “Column Grouping in Power BI Desktop October Update”

Power BI Infographic Designer

Microsoft Power BI team released a new custom visual last week (Oct 26) – The Infographic Designer.
You can download the custom visual here.

Download Infographic Designer

The best resource to learn about the new custom visual is relatively hidden. You might have missed it. I had to download the sample file, and then go to the Hint page to find the link to this resource. You can find there useful tutorials, guides and videos that are crucial to master this tool.

The Infographic Designer custom visual was developed by a team at Microsoft Research (Extremely smart and talented people, but a different team, and as a result you get a new and different user experience inside the visual.

Our goal for today was supposed to be easy – Importing our own image into the Infographic Designer column chart. Dedicated for the coming Halloween, here is a scary and meaningless Power BI report with the Infographic Designer custom visual.

Continue reading “Power BI Infographic Designer”

PASS BI Virtual Chapter Demo on Sentiment Analysis in Power BI

Today I presented at PASS BI Virtual Chapter, and walked through the creation of a Power BI report which analyzes the engagement and sentiment of the US Election candidates on Facebook.

Here is the recording:

Continue reading “PASS BI Virtual Chapter Demo on Sentiment Analysis in Power BI”

Analyze Facebook Groups with Power BI

Today I presented at Power BI Global User Group, and demonstrated how to import Facebook group posts into Power BI, analyze the group engagement, and run sentiment analysis on the comments.

Here is the YouTube Recording:

Continue reading “Analyze Facebook Groups with Power BI”

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 for the blog's tutorials.

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

Join PASS BA in ChicagoLearn More
+ +