power query

In Excel Power Query, I have a table. Column A has single numbers. I want to mark those records where the Column A value matches a list. A cutdown version of the problem is: let TableA = Ta...

Automatically transform all columns to the types that have been detected in the cells of its first row: Table.TransformColumnTypesToFirstRowsTypes

Over the last couple of years, I have been actively involved in doing Power BI trainings for my clients. Because of that, I am always looking for new and easier ways to explain Power BI concepts to…

Power Query is a very powerful tool. It has a rich interface from which users can easily clean and reshape their data.   Table.Unpivot is one of my favourite feature. It IS a real game changer for cleaning data because business users tend to "build their data in the format that a PivotTable produces...

Learn how to use Power Query like a pro and organize your queries by using this very simple technique. Take your queries and organize them in folders.

How we reduce development lag when building queries in Power Query is a question that came up in my blog post at PowerPivotPro last week, even though that wasn't the main issue.  I thought it might be a good idea … Continue reading →

Do you often find yourself in dire need to compare two tables in Excel? In today’s blog post, we will create an Excel workbook that compares two tables, and highlights the changes in a third table. You can load new versions of the tables, click Refresh All in Excel, and immediately see the changes in …

Bill of materials (BOM) implosion analysis shows where each component is used. See how easily this can be done using M in PowerBI or Power Query in Excel:

Unpivot is one of the most frequent tasks when working with dirty data. Get this new "swiss-army knife-function" for all things unpivot:

Announcement Update: For those who missed out on the webinar Watch the Session Recording for my (Avi Singh) webinar. Click here to watch the session record

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

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…

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

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…

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

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

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 …