Comparing Measures

Katherine S. Rowell, M.S., M.H.A.
2017-08-29

If you are ever tempted to start a column (bar) chart at a point other than zero, please read this post.

It also has suggestions for alternative chart types to handle comparisons.

Data Validation Search – Free Add-in

Jon Acampora
2017-04-27

A common Excel request is to be able to type characters and see the in-cell data validation drop down list reduce, based on what you have typed.

This free add-in from Jon Acampora (Excel MVP) does just that. He has recently added a few new features.

If you have long drop down lists this add-in is a great addition to Excel.

This link has a video of how it works and the new features like Auto Open when a data validation list cell is selected.

The R Language

David Iseminger - Microsoft
2017-03-29

If you are into statistics then you probably already know about the R language – but if you don’t it may be worth looking at.

It is open source code that is built to handle statistics and big data. It has some limitations when used with Power BI but it can be used on its own.

This article looks at using visuals created with R in Power BI.

DAX Studio

DAX is the function and query language of PowerPivot and PowerBI. It has been around for a while, but it didn’t really have many editing tools until DAX Studio came around.

DAX Studio has been created by the SQLBI.com guys – the legends in PowerBI.

I saw a demo today at the Perth Modern Excel User Group at Microsoft Perth – its great.

Whilst it is a powerful editor, it is not just a code editor.

There are lots of options to allow you to optimise your DAX code. Lots of output options as well.

You can test and see how long it takes to run your DAX commands.

If you regularly use DAX in Power BI or PowerPivot then DAX Studio is a must have.

 

 

Hacking Power BI

Chris Webb
2017-04-11

Chris Webb’s BI website is a great resource on Power BI, DAX and Power Query and Power Pivot

This post shows how to hack DAX to display characters. Opens up some new possibilities.

 

Finding Those Pesky Links

Bill Manville

Thanks to Bill Manville for sharing the add-in.

This is a free add-in that’s been around for a long time and it finds most of those frustrating links that may have been created by

  • deleted files or folders
  • moved files or folders
  • renamed files or folders
  • sheets copied between files
  • charts copied between files

The add-in has been updated over the years so it now handles Power Query.

Power Query M Language Editor

Power Query (Get & Transform) uses the M language to allow you to customise and build in flexibility to your queries.

Currently writing the M language is tricky to write. It is case sensitive and there is no built-in help to assist you using the language.

Apparently Microsoft is working on creating an M editor, but there is no time line on when it will be ready.

This article takes you through some steps to create your own editor using Notepad ++.

If you use Power Query a lot then this may be worthwhile.

Tim Ferriss Podcast

I have been listening to Tim Ferriss’s podcasts for a few years now.

Its brilliant!

As well as having many famous people on, he also has people who aren’t so famous but are amazing nonetheless.

Many episodes go over 2 hours.

Highly recommend – great for car travel.

There is occasional swearing, not much, but if you don’t like that, you have been warned.

The content is well worth ignoring the occasional swear word.

 

Ron DeBruin

Ron DeBruin is an Excel MVP and has been for a long time.

His site has lots of excellent macro code and free Add-ins.

He has tips and macro examples for the Mac as well as Windows.

I have used his pdf creator code to create pdfs of certain sheets in a file.

If you want to learn about modifying the ribbon he has some great tips and examples.

He also has code examples for amending Excel’s pop-up menus.