08 6210 8500
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 [...]
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:
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 [...]
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 [...]
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 [...]
by Jeff Robson Challenge of the month - Flex your Power Query Skills From time to time, we post fun, technical challenges in Excel & Power BI. In this one, you start with a dataset where every second row is data and the rows in between are column headings. Can you turn this into two [...]
by Wyn Hopkins Power Query and a file selector Macro If you have an Excel file containing Power Query and need to send it to colleagues, read on. If they need to be able to change where the source data for the Query is being pulled from then read on. If you [...]
5 Great Excel tips from 5 Excel MVPs Want to learn from the best? Jump on to one of our courses
By Yury Tokarev Dynamically consolidate multiple ranges when loading Excel files from Folder in Power Query We've come across a task where we need to consolidate a client's budget data from Excel files, each representing a division, where any of the files may have multiple similarly structured tabs with sub-divisional data. In our [...]
How to create a dynamic drop down list in Excel - with WARNINGS! Want to learn more? Join one of our courses
Power Pivot basics plus a Power Query technique for handling awkward data Something for everyone in this video, showing how to take awkward source data, transform it using Power Query and produce an interactive variance report in Power Pivot. The pre-built template we used is available for free from our website along other cool downloads [...]
Power Query is an amazing tool for cleaning and transforming your data to make it ready for analysis in Power BI or Excel. Here are 5 useful hints to help you use Power Query more effectively: Hint #1 – Use the Formula Bar The Formula Bar helps you quickly understand which step you have previously [...]
Pay my invoice
