We often have our data spread out in different Excel workbooks and sheets. How do we combine all of them into one table? Let’s walk through an example. We have our data saved in a folder with three files: 2019.xlsx, 2020.xlsx, and 2021.xlsx. You can download the files ...

One task data analysts often have to deal with is matching data from one table to data in another table. Let’s look at how we get our tables matched up in a new and powerful way: using Power Query Join and a custom filter Text.Contains function. First, let’s look ...

Dynamically retaining only columns that contain Actual values from a Table This article is part 3 of the Using Power Query lists series. In this series, we will look at how we can use lists to do the following: Dynamic Filtering on a Column Using Lists Changing a Table of ...

Utilizing Power Query Lists 2/3 This article is part 2 of the Using Power Query lists series. In this series, we will look at how we can use lists to do the following: Dynamic Filtering on a Column Using Lists Changing a Table of Attributes and Values to retain only Top 10 ...

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

Last year, Microsoft announced the introduction of a new group of functions in Excel, known as dynamic array functions. One of these – the FILTER function – is possibly the best of the lot. The FILTER function will filter a list and return the results that meet the ...

Today is most definitely one of the most exciting days of this year for all Excel users out there. Today the endless discussions about whether people should use VLOOKUP or INDEX-MATCH are finally OVER! I will admit that I thought it should have been over back when Power ...

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 Power Query. Now whereas Power Query is brilliant and can do amazing things, I like to point out the little ...

In Slovenia (where I come from) there is a significant shortage of Excel and Power BI conferences. Basically, there are none (I’m planning to change that in November). Furthermore, there is a shortage of Excel, and Power BI themed conferences in Europe in general. ...

I wrote a blog post on the New Excel Data Types in May and stated that “the Sky is the limit” in the way the New Excel Data Types can affect the way we work with Excel… And one month later, here is a first Excel feature that is greatly enhanced by The New ...

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 runs a famous Excel blog called How To Excel where you can find tons of useful tips and tricks. In this post, ...

Jan Karel Pieterse and Tony de Jonker will give Excel enthusiasts reason for joy on the 7th and 8th of June 2018. This longest running Excel Conference in Europe is returning for the fifth time in a row! I have very fond memories of the Amsterdam Excel Summit and ...

New Data Types in Excel will undoubtedly be THE talking point of the near future Excel evolution. That being said, they are also the reality in Office 365 Fast Insiders. That’s where they were recently introduced by Microsoft. This post will first explain what the new ...

The Unlock Excel Conference  took place in April in three cities across Australia. I joined Excel MVP’s Liam Bastick (SumProduct LLC), Jon Peltier (Peltier Tech), Ken Puls (Excel Guru), Mynda Treacy (My Online Training Hub) and Tim Heng (SumProduct LLC), and we all ...

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 one. You can download it here (or just continue working in the one from last week). And before we begin, the ...

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. Index-Match combination once and for all. Needless to say, the goal was not achieved and to be fair, I think no ...

Readers of Excel Unplugged know that I am a big believer in using keyboard shortcuts to be fast and more efficient in Excel. There are a lot of good resources for learning shortcuts but the Excel Add-in from David Hoppe called XL Shortcut is just what the doctor ordered. ...

This post will feature VBA code that will recolour the Worksheet tab based on a set of predefined conditions. Let’s imagine we have a Workbook where each Sheet represents one active project in a company. Cell A1 gives a short status of the project from the task ...

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. It’s still one of most popular posts on Excel Unplugged and has been commented on 57 times and counting. ...

This weeks post will be a short testament to a development venture by a few of my countrymen. Like most of Excel fans, I’m also a big fan of Power BI and it’s reporting capabilities. But having said that, the built-in visuals have often left me wishing I was in ...

I guess every data professional that has ever taken on the challenge of creating data models either in Power Pivot or in Power BI knows the importance of the Calendar table. It gives you the ability to use Time Intelligence functions in DAX like DATESYTD, DATEADD, ...

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 the new Sheet. This code was written and tested in Excel 2016. And if you want to follow along, […]

This is a follow up post on the final result of last week’s post Table.Join function in Power Query. So basically, we want to get from this To this Only by using Conditional Formatting. First, we need to make sure, that the column in which we will be simulating blank ...

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 use Power Query to get a list of duplicate files that could be interesting for System Administrators and the ...