Jon Acampora
2017-04-27
2017-04-27
Monthly Archives: April 2017
Time to upgrade my Spreadsheet Mug
The dishwasher has taken a heavy toll on my old mug.
I think the new version has a slightly lower capacity.
The NETWORKDAYS.INTL Function Has a Unique Trick
Its customisable
The NETWORKDAYS.INTL function was added in Excel 2010. It allows to calculate how may work days between two dates using non-standard weekends. Some countries don’t have Saturday/Sunday weekends.
The Happiest Refugee
Highly recommend.
This book has it all, humour, drama, intrigue, family secrets and running jokes throughout.
Plus its all true. Anh is a nice guy and a great comedian and he writes well. I didn’t realise he had done so some many things, and done them successfully.
It shows the impact of a new country on refugees. A great read!
I heard it might be made into a movie – look forward to it.
David Iseminger - Microsoft
2017-03-29
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 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.
Increase Decimal
I found a good shortcut today to increase the decimal places.
Alt H 0
For example you might use these two shortcuts one after the other to format a range as a percentage with one decimal place.
Ctrl + Shift + %
Alt H 0
Trailing Minus For Negatives in CSV vs TXT Files
You can control TXT files much better
Some Accounting systems (I think SAP is one) downloads negative values with a trailing minus sign. Excel doesn’t recognise this as a number. When you import TXT files, negatives are handled correctly. CSV files don’t.
Sapiens
A must read.
It will get you thinking about how we have progressed and whether it has been progress or regress.
Have the lives of sapiens improved over the thousands of years?
What about the planet and the other animals on the planet, (the ones we haven’t killed off any way) are they better off?
Chris Webb
2017-04-11
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.
Making Subtotals Bold
When you use the SUBTOTAL feature in the Data ribbon tab it automatically inserts subtotals in your list – see blog post on it here.
One problem with this is that is only makes the cell with the word Total bold – it doesn’t make the whole row bold.
If you want the whole row to be bold it isn’t hard to fix.
- Select the whole range involved.
- Use the grouping button 2 top left corner. See image below.
- Then hold the Alt key down and press the ; (semicolon key) – this selects just the visible cells.
- Then press Ctrl + b to bold it.
- Click another cell to reset the range and you are done.
Gilbert Quevauvilliers
2017-04-11
2017-04-11
Compare the offerings of the Free vs Pro versions of Power BI.
The Pro version costs about AUD10 – 15 a month.
Thanks to Gilbert Quevauvilliers for creating the infographic + sharing.
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.
Alt Key Shortcuts
Two more
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones.
Matt Allington
2017-04-04
2017-04-04
A free Excel template that can
- create a PivotTable linked to your data model
- show you the memory usage of your model
- plus a listing and a definition of the measures you are using
“Without any announcement, latest #powerbi desktop fixed time intelligence for non-contiguous selection of dates in #dax”
@marcorus
2017-04-07
2017-04-07
2017-04-03
Some great tips on language usage with DAX and the M language in Power Query with examples and explanations.
You can download pdfs.
Printing Separate Print Areas
If you need to print a number of separate print areas from the one worksheet in one step there is an option that allows you to create a print area that includes additional ranges.
Create your first print area as per normal.
Select another print range and click the Page Layout ribbon and use the Print Area drop down and choose Add to Print Area. Keep adding ranges as you require.
You can also use the Ctrl key and mouse to select multiple separate ranges and then use Set Print Area.
The above technique allows you to add to that Print Area if you miss a range.
Downside
The only problem with this option is that each separate range is printed on a separate page(s).
There are lots of Excel blogs out there.
This page lists a lot of the best ones (unfortunately my site isn’t listed yet – but you never know).
2017-04-03
Ability to connect to the Power BI dataset and the ability to create a Query based on an example column. Interesting times ahead.
Quick measures also added.
Export a sheet as a PDF
It takes a few clicks but it is possible
I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)
Close All Icon
If you have a lot of files open and you want to close them all and still leave Excel open you can add this icon to the Quick Access toolbar.
See this post if you are not sure about using the Quick Access Toolbar.
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.