Power Query

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…

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…

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…

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)

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…

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

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.

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?

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 …

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...

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 = ...

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 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...

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....

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

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

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...

PowerQueryConnectors - A collection of data connectors for Power BI

In this webinar, Greg Low will describe one of the most important processes for creating analytics: obtaining and transforming data that the analytics will be based upon. He’ll show how to use Power Query in Excel, and how to use the same functionality in Power BI Desktop to import data and get it i...

I have a requirement like., One Source and 10 entites I need each entites 10 runs data . example like., Source1 Entity1 ( top 10 ) Source1 Entity2 ( top 10) .... ...

Earlier, I published a blog about A case insensitive approach to sort a table with Power Query (M language) and I received a few pertinent comments from “Nick”. I agree with most of his…

A really useful new feature was added to Get & Transform (the functionality previously known as Power Query) in the latest updates for the Office 365 click-to-run version of Excel 2016: the abi…

This post is part of the series: “Professional SSRS 2016 Book Preview Posts” which are excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports.  Eac…

A few weeks ago, Microsoft published the Power Query SDK, an extension of Visual Studio, on the Visual Studio Marketplace. It is available for free for VS2015 and VS2017. This extension is a really…

How exactly List.Generate works in Power Query? Unraveling the mystery of an algorithm for the better use