Power on with this Power Query Challenge

by Wyn Hopkins Flexible Consolidation of Tables These 2 tables exist on their own tabs in this file, goal is to combine them in a flexible way to produce the output required :   Must replace nulls with 0s Must be able to handle a completely new table for a new Region being added Must [...]

Build Wordle using Dynamic Array Functions

by Wyn Hopkins Learning Dynamic Array formulas in Excel by building Wordle This video shows the use of Dynamic Array formulas # references, XLOOKUP, SEQUENCE, MID, TODAY and some conditional formatting. Click on the file below to play along!     Download a copy of the file we used below:

New Year Power Query Challenge

by Wyn Hopkins Select Range and Convert To kick off 2022 we thought we would put this Power Query challenge to you: Select Range and Convert to required result Solution should work when dates, codes, colours wording or numbers in Value column change Note the colours in the Required Result are for guidance purposes only [...]

November Power Query Challenge

by Wyn Hopkins Columns to Rows and Rows to Columns Challenge In this month’s challenge we have a couple of key challenges to face: We have a double row heading, the top row of which is merged These merged “Department” headings need to become row items and we need to filter out variance We have [...]

October Power Query Challenge

by Wyn Hopkins Challenge of the month - Flex your Power Query Skills From time to time, we post fun, technical challenges in Excel & Power BI. For this one, take the source data in the blue table and turn it into the format shown in the green table. Your solution needs to automatically handle [...]

Power Query: Unpivot using Column Numbers rather than Names

by Jeff Robson Say my name, say my name When you're working with data from Excel in Power Query, column names can often change, new columns can be added and columns can be deleted. Excel users don't think twice about doing any of these things as they all seem quite normal and logical however they [...]

2021-06-25T10:46:17+08:00Power Query|

Using Power Query to Consolidate Multiple Files from a Folder

by Wyn Hopkins Combining Multiple Files from a Folder How to use Power Query for Excel and Power BI to consolidate multiple files into a single table of data, whether you're using OneDrive , SharePoint or a traditional network folder. As well as showing the basic steps, this video explains the inner workings of the [...]

Consolidate Excel worksheets with Power Query

by Wyn Hopkins Consolidate multiple Excel worksheets using Power Query A common request on forums and in the training classes we run is "how do I consolidated multiple sheets into a summary?" Power Query can do it, I step you through how I would approach it. Techniques covered include: Creating a custom function (via the [...]