PowerQueryConnectors - A collection of data connectors for Power BI

Power Query (or PQ for short) is one of the cornerstones of the Microsoft BI Suite. It’s a tool built into Excel 2016 and Power BI Desktop that allows us to extract our data...

By Matt Allington One of the many things I love about Power Pivot and Power Query is that these tools have put BI into the hands of users and there is no l

In a query developed with Excel Power Query I would like to replace the table column name with a variable, when I filter rows of a list. Unfortunately, I either get an error message (e.g. Expression

Many Excel users can relate to the problem of data wrangling. You start with an input data set that you wish to transform into a new format so that you can perform further analysis using the transformed data. Not surprisingly, these problems arise for common types of data such as date and time, addresses, phone numbers, URLs. For example, consider....

I have dynamic data in a few columns in Table2 (see image). In Table 1 (created manually - enter data), each row represents one of the columns in Table 2. I need to replace the placeholders in the 3rd column of Table 1 with the average of the values in the indicated columns from Table 2 (the two tab...

As a follow up of the Web Scraping post, here, In today’s post, we will use the Add Column by Example feature to extract links from web pages. (Note: You may have already read this part here, but I am sure that many readers have missed it), Stay tuned for Part 3, where we will go step by step …

I'm trying to invoke a function that sorts a column in the calling query's table. I'm basically trying to turn this... ...into this... I made this simple function: = () = let Source = ...

I have the following data in PowerQuery: | ParentX | A | | ParentY | A | | ParentZ | A | | ParentY | B | | ParentZ | B | | ParentX | C | | ParentY | C | | ParentZ | C | I want to add an index col...

Recently I shared a Matrix Multiplication Power Query technique here, and explained how to use List.Generate as the core step of the multiplication. It was fun to experiment with List.Generate and use it to implement a kind of a For-Loop. So I thought it would be cool to use List.Generate to iterate over a nested loop. Below …

There are plenty examples on how to unpivot nested tables. But what if a table we need to make flat has no nested headers for repeating groups of columns?

Learn about the mathematical formulas available in the Power BI Desktop and Power Query as well as how to load and convert data for your reporting needs.

Power-BI-Desktop-Query-Extensions - Various M Queries that you can use to make life easy-peasy

Whenever I’m faced with a data mashup problem in Power BI, I try to check if it can be resolved with a standard M functions exposed by the intrinsic variable #shared. Navigating this data str…

For this post, I’m show how we can Keep Only Numbers from a string of text (removing all other characters), as well as keep only text (removing all numbers)

The Add Column By Example functionality that appeared in the April 2017 release of Power BI Desktop is, I have to say, very cool: I’ve used it quite a bit in the last few weeks and it really does w…

Below is where I had a situation where I wanted to put in a parameter within a piece of text. This was so that I could then dynamically change the Month Version for my budget and when I refreshed m…

Developing queries for Power BI and Power Query I had to look into documentation or check my previous solutions from time to time in order to get answers to same questions again and again. So, I en…

There are abundant public data sources on the Web, and it is so tempting to import and mash them up with your existing data sets in Power BI and Excel Power Query to gain new correlations and insights. While it’s easy to import static tables from many websites in Power BI and Excel Power Query, you’ve probably …

Very often I have thought about trying M instead of R for machine learning problems in PowerBI. Not only because I’m such a big fan of M, but also because (…) Read more

The R ways in #PowerQuery. How R.Execute() can be used for other non-statistical related tasks #PowerBI

Get your own Power Query editor with intellisense, snythax highlighting, parameter hints and help texts. Custom M code has never been easier to write.

I saw a blog post recently where Jay Killeen was talking about how he handles his Source Control for the Query Editor or Power Query. And this got me thinking of how I could automate this in a quic…

Sometimes (dare I say often, or even usually) the data you have available is not in the ideal shape for analysis in Power BI. These problems can manifest i