Power Query

Being a fan of PowerBI, I recently looked for a way to read zip files directly into the Data Model, and found this blog which showed a usab...

Get Data from the Twitter API with Power Query. Power Query can perform HTTP GET & POST, making it very useful for working with REST APIs.

Thoughts on data analysis, programming, and statistics, with an emphasis on R, JavaScript, Power Query, Azure, and Power BI

Introduction to PowerTweet M functions

Let M write its own code: Automatically create a function record for Expression.Evaluate to replace #shared so it will work in the service as well.

Recently I needed to do the very simple thing in Power Query. I have the column of numbers and need to check if the values in this column are less than N and then put a corresponding text value in the new column. The function for the new column is something like this: Actually some values are not a numbers but nulls: Data contains nulls and comparison return an error And this simple calculation returns an error for these values! Why? There is the catch, which is hidden in the depth of documentation (actually on the page 67 of the "Power Query Formula Language Specification (October 2016)" P...

Sometimes your power queries can get quite heavy and you might need to optimize the steps in your query but how can you calculate the time it takes for your query to load. Wouldn't it be nice if you c…

I was working on a dataset where I wanted to change multiple column names using one step and not having to change them manually. Since there were over 30 columns this would be really time consuming…

Working with column types can be challenging in Power Query. The default conversion of types may not always work well for you, and may create future refresh errors, when the column names change. I have blogged about it in details here, and usually recommend to use explicit type conversion, and remove the automatic Changed Type detection …

The latest version of Power BI Desktop came out with a new Web Connector specifically designed for Web Scraping Scenarios. in this blog post I’ll try to go deep into how this new experience works and how you can take advantage of it. Before we move forward, you’re gonna need the latest version of Power …

The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead: The Challenge Stretching or compressing a forecast so that the proportion of (…) Read more

See how cool it is to build a permutations table with Power Query. Being able to see the results of each of your steps immediately makes a real difference h

What an amazing year 2018 has started out to be! Joining the PowerPivotPro team was an aspiration of mine that started in the Fall of 2013… I wanted to BE a part of this team and this community – to be able to share what I have personally experienced and what I have learned about using PowerPivot In Excel and Power

Just wanted to share a method in Power Query that might also help you. Today I had a table where the column contained information about both the parent element and child element In the example the …

This article is the starting point of a series of articles that demonstrates how to navigate through tables inside a Tabular Model, from Microsoft Analysis Services Tabular, Power Pivot or Power BI using DAX

From time to time Huang Caiguang sends me some geeky M-code that turns out to be very useful (like this for example). Today it’s utilizing java script code for regular (…) Read more

The Challenge I love the idea behind Power Query (M)agic. These posts let Power Query users learn and share best practices (tips & tricks) that solve real problems. Query management comes up a lot. Wh…

A quick post more for my future reference. Hoping it helps some one out there! While I was working on a Power BI report with Commonwealth Games dataset, I came…

In the comments to my last blog post on how the order that you select columns can affect the output of certain calculations, both Bradley Sawler and John B. Thomas pointed out something very useful…

The draft of my book is almost ready (I even have few Excel formulas that are showing me, that I have already wrote 93% of the books, and a total of 359 draft pages so far). This weekend, after finishing 44 pages on M, I wanted to share with you, in this post, a 3-page …

Maybe this is obvious to more experienced Power Query users, but something I always point out when I’m training people up on Power Query is that the order that you select columns in the Power Query…

What is a “Navigation” step in Power Query? When using Power Query, you might’ve come across a step that reads “Navigation” and is usually automatically created for you by Power Query, but…what does it do? Well, if we look at the code that it was created for the Navigation step, it reads: = Source{[Item=”SalesTable”,Kind=”Table”]}[Data] It …

When you work with Power BI, you'll find that there are two languages to work with: DAX and M. Since most of the M query that you will regul...

Power Query and the M language is a bit of a pain, not a lot of people do it (Or they do, and they don’t write about it), and the knowledge base on MSDN is not real world example friendly. So…

My custom M function GetTableOfDateAndDateTime() makes it really easy to create tables of date or datetime values for your work with Power Query in Excel or Power BI Desktop.