Excel Unplugged

Tag Archives: Power Query

AI in Excel – Part 2 – Column From Examples

Welcome back to Artificial Intelligence in Excel! In Part 1, we’ll looked at a feature called Flash Fill. It works by recognizing patterns of data and filling ...

Turning Product Descriptions into a list of attributes with Power Query

You can also watch the video version of this content here: https://youtu.be/5QDERKmMvZQ or watch the embedded video at the end of this post. It’s true Power ...

Extract Columns that Contain Actual Values

We often work with large datasets containing multiple columns, but only need a few. In this tutorial we’ll show how to extract the columns that contain Actual ...

Transforming a Table to Top 10 (by values) and Others with Power Query

How to get the top 10 values in a table, and an aggregate for all the other data? Our goal today is to turn a table of countries with attributes into a table of ten ...

Filtering a Table to a List of Values in Power Query or Power BI

Knowing how to use Lists in Power Query or Power BI is like a superpower. This Post will show you how to filter a Table to a Dynamic List of Values by using Lists in ...

Start And End Date Of The Week With Power Query

One of the most popular posts on Excel Unplugged is Formula to get the Start and End Date of the Week. For the last two years I’ve been a strong advocate for ...

Importing CSV Files with Changing Start Row

In this post, we’re going to talk about importing CSV files with power query! This is a guest post written by John MacDougall, an Excel MVP from Canada. John ...

Ultimate Vlookup Alternative? (Part 2)

This is part 2 of the Ultimate Vlookup Alternative post and a continuance from last week’s post. We will be using the same Excel workbook as we did in part ...

Ultimate Vlookup alternative? (Part 1)

Almost a year ago, more precisely in May of 2017 a site called defeat excel invited 27 Excel Experts (including myself) to end the endless debate of Vlookup vs. ...

Making Importing from Folder in Power Query (or Power BI) Bulletproof

I’ve always been a big fan of the Import from Folder feature in Power Query. So much so, that I wrote my first post on the topic way back in February of 2015. ...

Dynamic Calendar with Power Query or Power BI – Take 2

I guess every data professional that has ever taken on the challenge of creating a data model either in Power Pivot or in Power BI knows the importance of the ...

Modifying Power Query M code with VBA
  • January 23, 2018

    The core of this blog post is a VBA code that will create a copy of a Power Query M code, modify it, create a new sheet and load the result of the modified Query to ...

    Table.Join – The Power Tool of Power Query

    The basic explanation of Table.Join function in Power Query is to use Merge Queries on two steps of one single query.This will be a step by step tutorial on how to ...

    Get a random subset of data with Power Query

    How to get a random subset of data with Power Query? The other day I made a Dashboard with a classic by time and relevant dimensions analysis. The data was pulled ...

    Dynamic Power Query

    Another example how to utilize dynamic capabilities of Power Query comes from our reader. The following post was written as the reply to a great question Dane asked ...

    USE POWER QUERY’S GET DATA FROM FOLDER TO GET DATA FROM MULTIPLE EXCEL FILES

    I recently posted on how to make a process of getting data from a folder bulletproof. You can read it here. Whereas that post was about getting data from multiple ...

    Creating a Dynamic Calendar for Your Data Model

    This article will take care of the most fundamental need when it comes to Power Pivot or even Power BI and Time Intelligence. Having a Date Table (Calendar Table) is ...

    Get data from Folder in Power Query

    It seems that lately Excel users can be separated into two groups. One group that gets the new Excel (2010 or 2013) but still uses the application as if it’s ...

    Creating a dynamic Dropdown List with Data Validation from another workbook with a little help from Power Query

    Probably the longest title of all times, but it leads to something great. First let’s explain Power Query. Power Query is an AddInn for Excel and part of ...

    The New Pivot Column option in Power Query

    If some pictures are hard to view, you can get the PDF of the article The Pivot option in Power Query. With the latest update of Power Query AddInn for Excel quite a ...

    Unpivot with Power Query

    One might think that analyzing data with a Pivot table is hard, but Unpivot a Pivot table proves to be even harder. But Power Query or better yet a Query editor ...