Home > BI, Excel, Power Query > Use Power Query to Generate Date Table with Australian Financial Year and Holiday Flag

Use Power Query to Generate Date Table with Australian Financial Year and Holiday Flag

This is not a new technic as Chris Webb and Matt Mason have both blogged about formulas for generating  a date table using Power Query. Darren Gosbell has another blog for ISO weeks and 4-4-5 period. They are all excellent articles that you can reference as use straight away.

In my recent project, I need to filter out the data with Australian Financial Year (FY) and holidays. So I wrote my own version of the Power Query to get it done. I’ll share the Excel 2013 workbook below for you to download.


I’ll briefly talk about how I do it. You can view the actual Power Query code in my shared Excel 2013 workbook.

Step 1 – Take Matt Masson’s Power Query as the start point and add FY calculation.

Step 2 – Find Australian Holiday information. I could find any useful one by just use Power Query online search. Jump to Google search and find http://australia.gov.au/topics/australian-facts-and-figures/public-holidays. I then use the Power Query to load it and perform all sorts of data manipulation to make it ready for merging with my base query. Having said that, for demo purpose, it is good. In reality, I would prefer to load it once and save it as a static table somewhere for reference. As you can imagine, the Power Query will break if the web page changes and for such small volume, automation does not add much value to manual maintenance.

Step 3 – Merging the Power Query produced from Step 1 and 2 and do the final cleansing.

Enjoy!

Advertisements
Categories: BI, Excel, Power Query Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: