Wellington, NZ – Urgent Call For Help

As you’ve hopefully heard by now, we’re coming back for our annual Excel conference in Australia and New Zealand, with dates planned for Wellington on Apr 19 & 20. It’s hard to believe that it’s only a month away, but it’s time to make the difficult call about go/no-go decisions.  And while all the Australia dates are all locked in and going ahead, the reality is that Wellington is on the brink…

Unlock Excel Logo

We hope to be in Wellington on April 19 & 20, 2018

We need to hear from you

I want to be clear here, we REALLY want to come to Wellington.  The challenge we have is that we need to know people are going to show up to support the event.  It costs a lot to book venues, flights and the like, and right now we don’t have enough people to make this work.

And we have until 2PM New Zealand time TODAY (March 16, 2018) to decide if we have enough interest to make this viable.

Call to Action

We don’t need your completed registration just yet.  What we do need is enough people that INTEND to register for the Wellington edition.  If you can give us that, then we’ll take it on faith that you will register.  We get it – convincing the boss takes time.  We want to give you that time, so here’s how you can help us…

Fill out this survey if you want to attend the Wellington edition of the Unlock Excel conference.  All we need is a couple of pieces of information:

  • Do you intend to come?
  • What is the % chance you’ll be able to register?
  • Your name and email

We promise that we won’t add you to any email distribution lists, we just need to assure that you’re real.  The reality here is that we need to cover our costs.

Our Target?

Right now, we need an additional 30 people at a minimum to express their interest in attending the Wellington edition.  If we can get that, we’ll go ahead.  If we can’t…

What can you do?

There are two things you can do:

  1. Fill out the survey to indicate your interest
  2. Share this with anyone in the Wellington area who may be interested

We appreciate anything you can do to help us spread the word and make this conference a go.

Wait – Tell me more!

Sorry, you HAVEN’T heard of the Unlock Excel conference?

The conference is going to be awesome!  From charting and dashboarding, to VBA, to Power Query, Power Pivot and more, we will be exposing you to the fact that Excel isn’t just Excel any more.  With revolutionary changes to the product starting in Excel 2010, there is a VERY good chance that you’re still working the hard way, and we want to fix that for you.  We even have 1 on 1 sessions and group Q&A sessions where you can pose your questions directly to us.

Unlock Excel Speakers

Don't miss out on the chance to learn from an international group of Microsoft MVPs.

And if it’s professional development you’re after, it counts for 17.75 hours.

You can find out more about the Wellington event on the CPA Australia website: https://www.cpaaustralia.com.au/training-and-events/conferences/unlock-excel/wellington

Just don’t forget to fill out our survey. Or even better, register if you’re currently in a position to do so!

Return a Specific Day of the Next Month

In a comment on a previous post, a reader asked how you return a specific day of the next month from any given date.  In other words, I've got a date of March 5 and I want to use Power Query to return April 10 in Excel (or Power BI).  How do you do it?

The Excel User's First Guess

So my first thought was to jump straight into the Power Query Formula reference guide to review the date functions.  Surely there must be something in there to manipulate dates and such, right?

Here's a quick list of the the functions I knew I'd need:

  • Date.Year()
  • Date.Month()
  • Date.AddMonths()

So those are awesome for ripping dates apart and shifting them, but what I really needed at the end was a way to put things back together.  I needed an equivalent of Excel's =DATE(year,month,day) function.  I couldn't find one.

Return a Specific Day of the Next Month

After poking around with this for a while, it suddenly occurred to me that I was doing this all wrong.  To return a specific day of the next month, I just needed to provide the "literal" #date() and I was good to go.

Let's take a simple table like this:

image

I pulled it into Power Query, went to Add Column --> Custom Column, and added the following formula:

=#date(
Date.Year(Date.AddMonths([Dates],1)),
Date.Month(Date.AddMonths([Dates],1)),
10
)

And at that point it works beautifully:

image

Basically, the #date() literal works just like Excel's DATE() function, you just case it differently and put a # tag in front of it:

#date(year,month,day)

It's a weird one, for sure

Returning a specific day of the next month is one of those odd cases where you have to use one of Power Query's literals to create the date you want, rather than employing a function to convert values as you're used to in Excel.   The good news though?  Miguel does an amazing deep dive into the M coding language in our Power Query Academy, including explaining what literals, tokens, keywords and more are all about.

If you want to understand this in depth, check out our course:

image

PS:  Sign up for our free trial first, to make sure you like our style!  And when you're convinced… you won't find better Power Query training anywhere.  Smile