whopkins@accessanalytic.com.au

by Wyn Hopkins The Challenge: Survey Response Remove any current AND FUTURE columns that begin with Sys, but allow for other future additional (non Sys) columns to be automatically included Replace all but 1 space between the names in the first column Add a TRUE / FALSE flag for the 3 response options in 3 [...]

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 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 [...]