Certification goals for 2023

MichaelAdrianJohnsonLeave a comment

This year (2023) my role at Cobalt Analytics will evolve a bit more as we grow. I will become more focused on the sales, architecture and training parts of the business and as such need to grow my knowledge of not only our core technologies but also the environments in which these technologies operate.

I’m setting myself the goal of obtaining 12 new certifications this year which breaks down to a very convenient 1 per month although I do not expect to complete them at that cadence. Many of these certifications will be in our core focus area of data engineering and analytics on the Microsoft platform but I would also like to take at least one competing product certification. Below I have listed the 12 certifications\exams that I plan to take although I expect this to evolve over the year. I’m also including my reasoning for taking the exam so if you see any flaw in my reasoning feel free to leave a comment.

1) AZ 305 : Designing Microsoft Azure Infrastructure Solutions 

My first exam for the year needs to be Microsoft AZ-305 Designing Microsoft Azure Infrastructure Solutions. Last year Microsoft changed its partner system such that partners needed to obtain more than a handful of technology-focused exams. and one of those changes was the introduction of two prerequisite exams, AZ-104 and AZ-305. I wrote the AZ-104 exam last year and need to rewrite the AZ-305 this year.   

Completed on 10 Jan 2023

2) Databricks : Data Engineer Associate

At cobalt Analytics we want to grow our supported tech stack to include Databricks. There is great demand both locally for Databricks implementations both locally and abroad and we need to make sure that we are up to the challenge of embracing this growing technology. I completed the fundamentals exam and the data analytics exam last year and would like to grow and affirm my skills in the data engineering side of the tool.

Completed on 20 Jan 2023

3) Databricks : Apache Spark Developer Associate

The Apache Spark exam focuses on your understanding of the Spark DataFrame API which is the foundation of both Spark and the Databricks offering. I believe that this exam will fit in nicely between the associate and professional data engineer exam

4) Databricks : Data Engineer Professional

Following on from the associate exam I would also like to write the Professional exam. 

5) Databricks : Machine Learning Associate

Machine learning is not an area that we focus on but keeping our eye on the horizon I will also be taking on the Machine learning features in Databricks. As this is an associate exam I plan to take this in the first quarter of 2023 to start the journey. 

6) Databricks : Machine learning professional

The final Databricks exam I plan to take is the Databricks machine learning professional exam. this will no doubt be the most difficult exam I plan to take this year. while I am familiar with many machine learning concepts it is not my strong suit. as such I expect this exam to be the last one I write this year and if I fall behind will probably be the first to fall off the list. That’s not to say it is not important but I am willing to give myself more time to become familiar with the topic if need be.  

7) DP-500 : Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI

The DP-500 exam is one of the newest exams, as it focuses on Power BI, a technology I have a lot of experience I do not see this being an overly challenging exam. That’s not to say I will not need to do some preparation but I’m assuming (famous last words) that this one will be one I could write

8) AI 102 : Designing and Implementing a Microsoft Azure AI Solution

One of the focuses of the year will be to ensure that I am up to date with all the data & AI exams and the AI-102 is another exam that I need to complete, the AI 102 exam looks at testing your knowledge of all Microsoft’s prebuild AI tools collectively known as the Azure cognitive services. This exam is less about understanding the implementation of these tools but rather their use as most of them use APIs.

9) DP 100 : Designing and Implementing a Data Science Solution on Azure

The DP 100 looks at your ability to apply your machine learning solutions on Azure infrastructure. A large part of this exam looks to be machine learning in Databricks so will complement my certifications in Databricks machine learning. 

10) DP 420 : Designing and Implementing Cloud-Native Applications Using Microsoft Azure Cosmos DB

The DP 420 exam focuses on Azure Cosmos DB which is a collection of data stores including key-value, column, document, graph and now relational databases. While this exam does not address analytics and is more of a developer exam, Cosmos DB is a growing source of data and understanding the platform will no doubt be crucial. 

11) DP 300 : Administering Microsoft Azure SQL Solutions

The DP 300 exam looks at building database solutions on Azure SQL databases, SQL is still a major source of data for analytics and is very capable of supporting analytics workloads itself.

12) Snowflake : SnowPro Core Certification

Finally, I want to add a none Microsoft exam to my list of skills, even though I do not do any work on Snowflake, it is easily the largest competitor in the analytics space to Microsoft so having a better knowledge of its strengths and weaknesses can only be a good thing.

This is certainly a lot of exams and I’m being very optimistic in believing that I can complete them all. but as they say, if you aim high and miss you have still done well. Completing less than 8 of these exams would be disappointing. I plan to update this page every time I complete one of these exams and also hope to do a small writeup on each one sharing things that worked out well for me.

T-SQL Tuesday #134 – Give Me A Break

MichaelAdrianJohnsonLeave a comment

This month’s T-SQL Tuesday is being hosted by James McGillivray (blog|twitter). The topic is “Give Me A Break“.

I’ve never been very good at getting away and find my self in front of the PC even when I’m not working but there are a few breaks that I usually take.

The annual holiday that did not happen

Usually my family would go down to the coast for the x-mas holidays. South Africa, being in the southern hemisphere has x-mas during our very hot summers making the coast the kind of place that you want to be. Unfortunately going to the coast this year was really not an option with a high number of Covid-19 cases being experienced in the Eastern Cape which is where we usually go. This meant that

The Office rebuild

For years I have had a small uncomfortable table to work at from home. Although eliciting a lot of complaining on my part it was never a huge problem as most of my work happened in the offices of my clients. 2020 and Covid-19 changed that, in addition to working from home full time I had also committed to writing a book. This meant that I spent far too much time behind a small desk with an uncomfortable chair. I’ve always liked the idea of working at a standing desk and I decided to build a solid wooden bench at standing height across the length of the room. This gives me a great 4 meter open surface to work on. I also took the opportunity to rope in the help of my best mate who I hardly get to see now days.

First layer of oil goes on
Getting there

This turned into a really nice distraction, so while I may have not actually gotten away from from the office, and actually spent blood, sweat, and tears at my desk than I would usually it was actually a great experience and while not technically a break I felt refreshed and now love being at my desk (Although my legs are still getting used to standing for the majority of the day)

Goals for 2021

MichaelAdrianJohnsonLeave a comment

Many people question the usefulness behind setting goals for the year. Of the 39 years I’ve been on the planet I’ve only done this once in 2019 where I set several goals for myself and completed them with various degrees of success. I did not set my self any goals for 2020, which may have been a good thing seeing how that year turned out.

What happened in 2020

Apart from the state of the world that we all know about, I managed to get done in 2020. The biggest thing would be writing my first real book. Together with Ásgeir Gunnarsson (T|L|B), we completed a book aimed at Power BI administration. Writing a full book was a lot more work than I’d thought and was quite a struggle to finish towards the end. I was also able to speak at my first and ultimately last PASS summit event. While the event was virtual, it was still lovely to see everyone, usually during the chats at the end of the talks.

A new Analytics Virtual Group

As you are probably aware, PASS will suspend all operations on the 15th of January. For the last three years, I have run the Business Analytics Virtual Group so it would be sad to see this resource being lost to the community. Therefore, together with Ásgeir Gunnarsson (T|L|B), we will be starting a new group with a similar focus. There is still much to do, and not much time to do it as all the resources that PASS has provided in the past need to be recreated.  

With no real way to gauge real community engagement, in 2021 our aim is to provide 5 000 hours of viewed content across both the live events and the YouTube channel where we will post recordings.

For more information on the group head on over to https://analyticsvirtualgroup.com/ , Ásgeir and I will be working on the site over the next few days (the 3rd of January 2021), in the meantime, we would love to hear your thought and get you signed up to our mailing list by filling in this form.

Write 18 Exams

I have not been very good at writing exams and generally only write them for the Microsoft partnership. One of my focuses professionally will be growing the training side of our business, so it is important that I get up to speed with the Microsoft curriculum. I’ll be relying heavily on Microsoft learn for this. While 18 does sound like a lot, and it is, I’m hoping that I should get many of the fundamental exams without too much time spent studying (famous last words). The goal is to get the six fundamental exams out of the way by the end of February then go along at a bit over an exam per month. With such a high goal, even if I only make it two-thirds of the way through this goal, 12 exams would still be a great accomplishment.

Run 1600 km

In 2019 I set my self the goal of running 1000km. Running started well, but I got lazy mid-year which resulted in a mad dash at the end resulting in a few injuries. In 2020 I set my self un unofficial goal of running 5km at less than 5 min\km or 25 min in total. Again I achieved this only by pushing it in December. This year I’m setting the goal of running 1200 km with a stretch goal of 1600 km. Using Strava has helped from motivation point as there are a lot of runners in the #SQLFamily and it nice to get that positive reinforcement.

Blogging

I’m hoping to doa better job on blogging this year than I have in the past. I’m going to set my target at 24 posts broken down into two per month, like running long term goals like this need smaller attainable goals.

Summary

That about covers my personal goals for the year. I’m hoping like we all are that this will be a better year than the last (Even though the first few days of 2021 suggest it won’t be).

2019 goals in review

MichaelAdrianJohnsonLeave a comment

At the beginning of 2019 I did something I have not formally done before which is to set myself a number of formalized goals for the year(I wrote a blog post about it here). Like many I have always told myself next year I would be better but without anything to track it by how can you know. Below is a summary of how I did against each goal and all in all I am pretty happy even though one of the goals was a non starter. I will definitely be setting my self new goals for the year.

Write 25 blog posts (0 or 25)

Writing is one of those things I have lacked confidence in for most of my life so to help get me started I set myself a goal of writing 25 posts. Sadly I did not see this through at all as I can barely count the two posts I did write as legitimate posts towards this goal. I did however contribute a chapter to in book (you can find it here) co-written by several authors, this was an interesting experience and something I hope to do again. I will be setting my self a similar target again this year but making the goal more granular as 2-4 posts per month (Still to be decided).

Read 25 books (26 of 25)

As a kid I used to read a lot but have not done so for many years. 25 book works out to a book every two weeks which at times was a push but I managed to achieve the goal and even managed to sneak in an additional book during the December down time taking me to 26 books read. Many of the books were in the business category but I also read some great fiction books. I did learned that my reading speed is not what it should be or at least what kindle thinks it should be but I feel it had gotten better as we went through the year. The books I read were:

Title Author
Making Work Visible Dominica Degrandis
The DevOps Handbook Gene Kim
Fast Focus Damon Zahariades
Zero to One Pieter theil
Good Omens Terry Pretchet & Niel Gaiman
The Truthful Art Alberto Cairo
American Gods Niel Gaiman
Real world Kanban: Do less accomplish more with lean thinking Matthias skatin
Agile Project Management with Kanban Eric Brechner
Measure What Matters: OKRs: The Simple Idea that Drives 10x Growth John Doerr
80/20 Running: Run Stronger and Race Faster By Training Slower Matt Fitzgerald
Shoe Dog: A Memoir by the Creator of Nike Phil Knight
Weapons of Math Destruction: How Big Data Increases Inequality and Threatens Democracy Cathy O’neil
Exam Ref 70-767 Implementing a SQL Data Warehouse Jose Chinchilla
Exam Ref 70-768 Developing SQL Data Models Stacia Varga
Bad Blood: Secrets and Lies in a Silicon Valley Startup John Carreyrou
Never Split the Difference: Negotiating as if Your Life Depended on It Chris Voss
Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence Philip Seamark
Atomic Habits: An Easy & Proven Way to Build Good Habits & Break Bad Ones James Clear
I Am Legend Richard Matheson
Data Strategy: How to Profit from a World of Big Data, Analytics and the Internet of Things Bernard Marr
Leaders Eat Last: Why Some Teams Pull Together and Others Don’t Simon Sinek
Deep Work: Rules for Focused Success in a Distracted World Cal Newport
The Unicorn Project: A Novel about Developers, Digital Disruption, and Thriving in the Age of Data Gene Kim
The Calculating Stars (Lady Astronaut Book 1) Mary Robinette Kowal

Run 1000 km (1000 of 1000)

This was the second time that I set myself this target after failing to reach it in 2018, I’m happy to say that I was able to do this although with some difficulty. I picked up and injury in September that kept me off the road for two months resulting in needing to run 170 km in the final month. All was on track with two weeks and 54 km to then I picked shin splints (no doubt from pushing too hard), I tried resting it for a week but this did not help so in the end I ended up “Power walking” this last bit which ironically felt harder an than actual jogging but I was glad to have finished this challenge and look forward to doing something similar in 2020.

Hire two employees (2 of 2)

I set my self a professional goal for 2019 to hire two employees for my company (A big leap when it has only been me for the last few years). I hired my first employee in March and in December hire my second although he will only start in February 2020 (I’m still counting it). In addition to the two employees I also made the decision to merge\create a new company, the company is called Cobalt Analytics and was founder by James McGillivray (L) ,Neville Marle (L) and myself where we will help our clients with their Business Intelligence and Analytics needs. this take us to a total of 5 people all together which I thing is a really good result.

I created a simple power BI report to track these goals, for some reason this does not render in WordPress, if you would like to see the report please follow this link

In the next few days I will be documenting my goals for the year ahead.

I’m speaking at SQL Saturday Victoria

MichaelAdrianJohnsonLeave a comment

On Saturday, 16 March 2019 I will be speaking at SQL Saturday Victoria. This will be the second time that I am able to attend this great event and I’m looking forward to again being in this beautiful city.

This year, instead of a technical presentation I will be presenting a professional development talk ‘Taking back control of your work using Kanban‘. This talk will look at what Kanban is and how it can help you and your organisation retake control of work delivery.

I hope to see you there.

 

 

 

The Year Ahead

MichaelAdrianJohnsonLeave a comment

An age-old adage used within the Business Intelligence field is that you can manage (or improve) that which you can’t measure. With this in mind, I have decided to join the many people who set goals for themselves in the year ahead. I refer specifically to goals as I feel resolutions do not work as well.

I want to begin with 4 goals, this seems like a number large enough to stretch my self but not so large that it pushes me too far. Burn out is something that happens to many of us in the IT field and something to be avoided. The goals that I am setting myself are the following.

Write 25 Blog posts

Blogging is one of those things that I always plan to do yet never seem to get done, this is evident from the 14 posts that I have written in the three years that I have had this blog. My goal is to write 1 new blog post every fortnight.

Read 25 Books

As a teen, I loved reading, but have found myself reading fewer books as time went by. This year I would like to read 25 books, like the blog posts this would require 1 every fortnight.

Run 1 000 KM

I set my self this target last year as well but failed to get there although I am not unhappy about this is I contracted Hepatitis A midway through the year which put a stop to any running for many months.

Hire 2 Employees

On a professional front, work has been going rather well, I have a strong client base and leads on many new and exciting projects. I plan to start with an intermediate developer which will allow me to focus on the tasks where I can add greater value while still ensuring that the works that need to be done gets done.

To track my progress, I will also create a Power BI dashboard which I will embed into this page to share my progress.

 

 

 

 

Planning my PASS Summit 2018 schedule

MichaelAdrianJohnsonLeave a comment

Summit is just around the corner and its time to start thinking about some of the sessions you will be attending. In the past, I have winged-it but this often resulted in me missing session that I really wanted to attend or attending too many of the same type of session. As I will also be attending  SQL Saturday Portland and some of the speakers will also be speaking I also have the opportunity to see some of the sessions there. Below are some of the sessions that I plan to attend this year.

Day One

Day 1 Keynote

PASS Summit always begins with a keynote, in addition to providing a good roadmap of the features to come over the last few years, we regularly get to hear the first announcements of new and upcoming features in SQL Server. With SQL Server 2019 on the horizon maybe there will be more exciting news (Holding thumbs).

Data types do matter – Angela Henry

I am very excited about this talk as it will be the first PASS Summit presentation for my friend Angela Henry. Not only is Sumit a great time to learn but also to support #SQLFamily and I look forward to seeing her do her thing.

I’ve been working with SQL Server for a long time now and kind of just assume that I know the basics, it will be great to get a bit of a refresher and hopefully fill in some of the missing gaps that I have.

My Power BI Report is Slow: What Should I Do? – Marco Russo

Performance tuning in DAX is something that I have been doing more recently of so have some experience with the topic but who can turn down the opportunity to learn from the master. What I’m most looking to take away from this talk is the Approach that Marco takes to identifying the biggest bottlenecks and patterns that he uses to address them.

Storytelling for Machine Learning and Advanced Analytics – Jen Underwood

Jen has worked for Microsoft and many other industry leaders. She has a fantastic grasp not only on the Microsofts BI tools but industry trends in general, I look forward to her thoughts on this.

Speaker Idol: Round One

As I am taking part in this competition this year it is only right to spy on support my fellow contestants. I look forward to watching although I’m not sure if watching the others present will make me more or less nervous.

Day Two

Day 2 will be a little different for me, taking part in this year’s Speaker Idol competition and speaking during the last session of day 2 I will not be attending any afternoon sessions as I will need to prepare for my talk.

Inside Columnstore Indexes – Bob Ward & Ryan Stonecipher

This will be my 4th PASS Summit and I have never been to a Bob Ward session, they are legendary for melting your brain. Unlike some of his other talks, columnstores are something that I am familiar with and share some common implementation patterns that underpin vertipaq which is the engine behind Power BI. Hopefully, I will be able to keep up.

Test Driven Development in SQL Server (How to Deploy Code Safer) – Hamish Watson

Devops is something that I am trying to understand more while integrating it more into my daily activities. On top of that, I understand the speaker like to hand out candy so who could say no to that.

Speaker Idol: Round Two

I will be presenting during this session so if you don’t find me here that means that I have messed up somewhere.

Day Three

Day three I’ll be a bit of a mixed bag, If I make it through the first round then I will again be skipping sessions to ensure that I have enough time to prepare.

Using Azure Databricks to Develop Scalable Data Solutions – Ginger Grant

I got to meet Ginger earlier this year and enjoyed talking to her. She will be presenting on a topic that will play a greater role in the data platform ecosystem in years to come so need to pay attention to more and I look forward to learning.

Building Enterprise-Grade BI Models with Microsoft Power BI – Christian Wade

For a long time, I have been an analysis services fan and most of my work revolves around it in some way, it looks like Microsoft plan to make Power BI the centre of the analytics experience so understanding this may be very important

Deep Dive in DAX Evaluation Context – Alberto Ferrari

Although I understand DAX context evaluation reasonably well this is a must attend talk for me, just like Marco Russo’s session, this is a must-attend talk for me.

Speaker Idol: Final Round

This will e the final session of the summit, I hope to be speaking during this session as well but even if I don’t make it through I will make sure I will be the crowd supporting the other contestants.

 

 

 

 

 

 

 

PASS Summit 2018

MichaelAdrianJohnsonLeave a comment

I’m excited to be able to go to my 4th PASS Summit having attended in 2012, 2014 and 2016 (a little bit of a trend there). PASS summit is always the highlight of the Microsoft Data Platform community (more than just SQL nowadays), and I look forward to reconnecting with old friends and meeting new ones. Flying from South Africa to Seattle is always a bit of an effort from both a time and cost perspective, so I like to bring as many people as possible with me especially people who have not had the opportunity before.  This year I joined by two first-timers, James McGillivray (T) and Charl Heinemann (T) along with some repeat attendees like as JP Voogt (T) and Jody Roberts (T). While I have always enjoyed my trips to PASS Summit, this year is going to be a little more interesting.

Microsoft Data platform MVP

This will be the first time I attend PASS Summit as a Microsoft MVP, while there are no extra perc’s to being an MVP at the event it’s always nice to add one more ribbon to my attendee badge and reconnect with some of the many people I met at the MVP Summit.

Speaker Idol

This year I have been selected to take part in the Speaker Idols competition. The Speaker Idols competition consists of several 5-minute lightning talks over four rounds, the first three rounds are made up of four contestants (12 in total) who each present to a panel of judges, a winner is chosen from each group who then go onto the final round held on Friday afternoon. I will be presenting in the second group also made up of Leslie Andrews (T), Janusz Rokicki (T) and Paresh Motiwala(T) all of whom I have never met before so I look forward to getting to know each of them as well as the judges.

Power BI World Tour

I have also been selected to present a session at the Power BI World tour taking place in Bellevue from 29 to 30 October 2018. I will be speaking about Data flows in power BI which at the moment is still in private preview but will likely be made public by then. This is a two-day event dedicated to Power BI and will also take place in the building where most of the Power BI team work out of so it is sure to be a great event.

SQL Saturday Oregon

I will also be attending SQL Saturday Oregon for the second time. I will also be attending with James and JP and we look forward to getting in some hiking while we are there and SQL Saturday Oregon would not be the same without #SQLTrain, without a doubt the best way to travel to PASS Summit that starts the next day.

October and November are going to be great, and I Hope that I will see you all there.

 

 

Creating a custom date table using PowerQuery

MichaelAdrianJohnsonLeave a comment

A common requirement in many BI environments including PowerBI is a date table. Date tables are a great way to add extra depth to you period\time sensitive reporting and are essential when using the time intelligence features of DAX.

There are a couple of options you have when creating a date-table such as importing the table from another data source such as a data warehouse or you can create the date-table using the CALENDAR function in the data model. In this post, we will look at how a date-table can be created using PowerQuery (also known as M).

At the heart of any date table, we require a column of distinct, continuous dates for a given date range.  Fortunately, PowerQuery has a function List.Dates that can create such a table with a few quirks\features. Firstly this function returns a list and not a table, to solve this we can use the Table.FromList function to convert the list to a table. Secondly unlike the CALENDAR function in DAX which takes a start and end date as parameters the List.Dates function instead takes a start date, count of periods that need to be created and finally a duration. this duration can be in days, hours, minutes or seconds, in our case, we would want to use days. Unless you know the exact number of days that you need in your date table it may be easier to calculate the number of days using the Duration.Days function which will return the number of days between two dates. The code sample below can be used to create a list of dates between the StartDate and EndDate.

    StartDate = #date(2017,1,1),
    EndDate = #date(2017,12,31),
    NumberOfDays = Duration.Days(Duration.From(EndDate-StartDate)),
    Source = List.Dates(StartDate,NumberOfDays ,#duration(1,0,0,0))

The next step is to convert this list into a table using the Table.FromList function, which also allows us to provide a better column name for our date column, this column also need to be set to a date data type.

    TableFromList = Table.FromList(Source,Splitter.SplitByNothing(),{"Date"}),
    ChangeType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}})

This simple table with the correct relationships in your data model will allow you to use the powerful time intelligence functions that DAX supports, however, we want to create our own custom date attributes that allow for more natural slicing, we will do this by adding additional columns using the Table.AddColumn function along with the Date family of functions.

    Year = Table.AddColumn(ChangeType ,"Year",each Date.Year([Date]),type number),
    MonthOfYearNumber = Table.AddColumn(Year ,"Month of Year Number",each Date.Month([Date]),type number),
    MonthOfYearName = Table.AddColumn(MonthOfYearNumber ,"Month of year",each Date.ToText([Date],"MMMM"),type text),
    DayOfMonth = Table.AddColumn(MonthOfYearName ,"Day of Month",each Date.Day([Date]),type number),
    DayOfWeekNumber = Table.AddColumn(DayOfMonth,"Day of Week number",each Date.DayOfWeek([Date]),type text),
    DayOfWeekName = Table.AddColumn(DayOfWeekNumber ,"Day of week",each Date.ToText([Date],"dddd")),
    PeriodNumber = Table.AddColumn(DayOfWeekName,"Period Number",each Date.ToText([Date],"yyyyMM"), type number),
    Period = Table.AddColumn(PeriodNumber ,"Period",each Date.ToText([Date],"MMM") & "-" & Date.ToText([Date],"yy"))
  • Feel free to add and remove any columns that you would find useful.

The last thing that we may consider doing to make this code more useful is to convert the code block into a function, this will allow us to create multiple date-tables if required but more importantly, it will make using a dynamic start and end date much easier. The final code block would look like this.


let CreateDateTable = (StartDate as date,EndDate as date) as table =>
    let
        NumberOfDays = Duration.Days(Duration.From(EndDate-StartDate)),
        Source = List.Dates(StartDate,NumberOfDays ,#duration(1,0,0,0)),
        TableFromList = Table.FromList(Source,Splitter.SplitByNothing(),{"Date"}),
        ChangeType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
        Year = Table.AddColumn(ChangeType ,"Year",each Date.Year([Date]),type number),
        MonthOfYearNumber = Table.AddColumn(Year ,"Month of Year Number",each Date.Month([Date]),type number),
        MonthOfYearName = Table.AddColumn(MonthOfYearNumber ,"Month of year",each Date.ToText([Date],"MMMM"),type text),
        DayOfMonth = Table.AddColumn(MonthOfYearName ,"Day of Month",each Date.Day([Date]),type number),
        DayOfWeekNumber = Table.AddColumn(DayOfMonth,"Day of Week number",each Date.DayOfWeek([Date]),type text),
        DayOfWeekName = Table.AddColumn(DayOfWeekNumber ,"Day of week",each Date.ToText([Date],"dddd")),
        PeriodNumber = Table.AddColumn(DayOfWeekName,"Period Number",each Date.ToText([Date],"yyyyMM"), type number),
        Period = Table.AddColumn(PeriodNumber ,"Period",each Date.ToText([Date],"MMM") & "-" & Date.ToText([Date],"yy"))
    in
        Period
in
    CreateDateTable

 

If you have never created a blank query before then you can use the following steps to implement this code.

From the Query Editor select Get data –> Other –> Blank Query

BlankQuery.PNG

This creates  a new query, select the advanced editor from the Home ribbon

BlankQuery2.png

Using the Advanced editor replace the default code with final code sample from above

BlankQuery3.png

Invoke the function by selecting it from the list of queries (I have renamed the Query to make it more usable), capture the start and end dates using the calendar controls then click on the [invoke] button.

BlankQuery4.png

You will now have a custom date table that you are able to use in your data model.

BlankQuery5.png