08 6210 8500
by Wyn Hopkins Challenge: Change columns to a single row Inspired by a real-life scenario where nicely structure data needed to be "Pivoted", but in a specific way, and as always with Power Query it needs to work when new data is added. The challenge is to: present each person as a single row with [...]
by Wyn Hopkins This month's challenge is about dates and times There's a surprising issue hidden in here too! The goals are as follows: extract the time without the seconds extract the year (watch out for the trap) extract the number of days between the last Friday of the Year and the last day of [...]
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 Insert Blank Rows with Power Query Power Query in Excel can be used for so many purposes and converting data ready for upload into another system is just one. In this scenario we show you a couple of techniques to insert a row for each change in a group of items. Did [...]
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 Wyn Hopkins SquareWars - A Fun Way to Learn Excel 365 Here’s a fun way to learn a few Excel 365 functions and challenge a colleague to a friendly game at the same time.. Aim Leave your opponent with the last square and you win! Rules Take turns to enter your sequence of numbers [...]
By Jeff Robson Use this Excel Skills Assessment spreadsheet to check how much you know about Excel. Simply go through each step in our self-assessment ExceleRater tool and tick the areas you are familiar with. At the end, you'll see your score and can tell what level you're at.
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 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 [...]
Reach out to the Access Analytic team Monday to Friday on (08) 6210 8500 or submit your contact information and we will be in touch.
Get Ahead of the Game – Book Your Training Now!
Wyn has been awarded MVP status by Microsoft every year since 2017 and is the author of the book “Power BI for the Excel Analyst”. He is known worldwide for sharing his knowledge via the Access Analytic YouTube channel with over 5 million views and 90,000+ subscribers.
He gained his Chartered Accountant qualification at PwC in 2000 and has extensive Business Analyst experience with HBOS and Barclays in the UK plus multinational clients in Perth.
Wyn is skilled in Excel and Power BI development, Power Query automation, management reporting and is an experienced Excel and Power Bl trainer.
Pay my invoice
