whopkins@accessanalytic.com.au

Home/Wyn Hopkins

About Wyn Hopkins

This author has not yet filled in any details.
So far Wyn Hopkins has created 15 blog entries.

Revealing Power BI’s Hidden Gems

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

2019-12-09T16:04:26+08:00Power BI, Power Query|

HYPERLINK and XLOOKUP to jump to a result

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

Power Query (Get & Transform) – Video Introduction Part 2

Power Query (Get & Transform) - Part 2 In part 1 I demonstrated how Power Query can quickly consolidate all of the files in a folder and bring that data into Excel. In this 6 minute video I follow on from that introduction and add an extra transformation step to get the dates I need. Once [...]

Power Query (Get & Transform) – Video Introduction Part 1

Power Query (Get & Transform) Part 1 Power Query is amazing at linking to data sources and cleansing / transforming that data as it's loaded into Excel (or Power BI Desktop). Watch this 4 minute video and see how you can consolidate all of the files from a folder instantly using Power Query. Build the transformation steps [...]

Excel Dependent Drop Downs

  Dependent Drop Down Box How to do one of those trickier tasks in Excel: set up a drop down box that is dependent on the result of another drop down box. The scenario If coffee is selected from drop down A then I want a list of coffee types to appear in drop [...]

Easy Cumulative totals in Tables

Easy Cumulative totals in Tables The single greatest advancement in Excel in the last 10 years was the introduction of Tables.  Yet although Tables were introduced in Excel 2007 there is still a huge number (I'd even say the majority) of Excel users that don't know how to create tables or understand what they do. This [...]

Custom Visuals – Power BI – 2 of the best

One of the great things about Power BI visualisations is the fact that they are open source and clever generous folk are developing fantastic tools for free that can help us all deliver a more enjoyable experience to the end users. Custom Visuals Gallery Two of my current favourites are both by OKVIZ and are [...]

2018-04-05T11:16:36+08:00Power BI|

Master INDEX MATCH in 60 seconds

Great news! XLOOKUP is going to replace INDEX MATCH eventually but in the meantime….. We've written a number of articles in the past around how INDEX MATCH is a technically better option compared to VLOOKUP. However, it is a trickier formula and therefore not as widely used. In May 2016 Wyn, added a new [...]

Power Query Parameters – How to use Named Cells as Flexible Inputs

Power Query is the best thing to happen to Excel since Tables were introduced in Excel 2007 **Updated May 2018** When developing Power Query solutions for others to use, or when needing parameters that can be changed easily (e.g. source file or date) you can store these variables in named cells and reference them [...]

2018-05-15T13:00:18+08:00Power BI, Power Query|

VBA & DropBox causing Excel to crash on close

Excel Warning Box By Wyn Hopkins In a nutshell: Save yourself a lot of wasted time and turn off DropBox badges.... If you ever experience a VBA Password box appearing after you close Excel and then the dreaded "Microsoft Excel has stopped working" message then there may be a simple solution...... After much debugging [...]

2017-03-08T03:53:54+08:00VBA – Macros|

See Celsius in your cells (try saying that quickly 3 times!)

I recently was asked if there's a way of adding the Celsius symbol to a value in Excel. The answer is yes, and the method can be applied to all sorts of useful symbols / notation.  e.g. bullet points, m2, km/hr, bbl, currency symbols etc. This is how.... Pick a blank cell then go to [...]

2019-02-05T11:58:40+08:00Formatting, Functions|