08 6210 8500
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 [...]
by Wyn Hopkins Excel Hash 2021 A bit of fun to be had and a challenge set by fellow Excel MVP Oz du Soleil. Finding a solution to the spare seat assigner. Our solution incorporates the following items: 1. Cutout person 2. Pick at least 2 from the following list: LET, LAMBDA, Dynamic Arrays, Power [...]
by Eric Marshall Assigning Data Types Assigning data types is an essential step when working with Power Query in Excel or Transform Data in Power BI Desktop. Here are some useful tips on how to change data types while maximising the efficiency of your models: Tip #1: Don’t Change Types Until the End of a [...]
by Wyn Hopkins How to use Power Query to connect to a file on OneDrive or SharePoint A growing number of organisations are starting to store their files on OneDrive for Business and SharePoint. Users will begin to discover that connecting to these files with Power Query is not as straight forward as clicking the button for [...]
By Wyn Hopkins Power Query and a Key Word search (Episode 1) This demonstration came about due to a real life scenario where I needed to categorise a transaction listing (like a bank statement for example) so that I could generate a summary report of categories of spend. All sorts of tips can [...]
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 [...]
by Wyn Hopkins Did you know about these hidden Power BI features? It seems unfortunate that for such an awesome product there are a few user interface features that are very difficult to discover. I have trained hundreds and hundreds of users in Power BI, and I get to experience people hitting these issues over [...]
by Wyn Hopkins Use HYPERLINK and XLOOKUP to jump to a result XLOOKUP will return a range so we can use it inside other formulas such as HYPERLINK to be able to jump to our result. Want to learn more? Jump on to one of our courses We've trained over 1,000 people from all [...]
By Wyn Hopkins If you find the default Icon settings confusing in both Excel and Power BI, you're not alone! In July 2019 conditional formatting Icon Sets were added to Power BI and it caused us to revisit how they work in Excel. In Excel you would highlight a range of numbers and choose Conditional [...]
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
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
