Recently I have gained the Microsoft MCSA BI Reporting certification, which consists of two exams: Analyzing and Visualizing Data with Power BI (70-778) and Analyzing and Visualizing Data with Microsoft Excel (70-779).
I was asked by a few people in my team on how and what I did to prepare for this certification, so I thought I would share some useful tips from my experience in completing the exam.
Firstly, you need to understand that both Excel and Power BI are quite similar in terms of the engine, querying language and principles they use, therefore this blog will cover both exams.
However, there are notable differences between the tools so you will need be understand and be prepared for them. This brings us nicely to the first point…
Understanding where you have gaps with Power BI and Excel will help you plan and utilise your time wisely.
The Skills Measured section for each exam on the Microsoft learning site, provides a breakdown of the technical areas the exam will covers.
Both the exams are broken down into three sections: Consume and Transform Data, Model Data and Visualize Data/Configure Dashboards. Each section is broken down into topics, and each topic includes a subtopic with descriptions.
Dustin Ryan’s blog breaks the Power BI exam content down further and has links to the resources that will help you increase your knowledge.
Back to Basic
Power BI and Excel are simple and easy to use tools, so most people would not have had any formal training and just gain knowledge over the years. I believe it is a good way to understand how the tools can really solve business problems, however this is not great when you need to sit a formal exam.
So, I recommend that going back to basics and doing some free EDX courses in Power BI and Excel. These courses will give you a good overview of the tools with hands-on examples, they are aimed at beginners so if you are a seasoned user then you will breeze through them.
Data Modelling and Cleansing
The visualisations in Power BI and Excel are pretty, but don’t focus all your time on that. The data modelling and data cleansing elements in these tools are what makes them so powerful and useful.
DAX and Power Query (M) languages are fundamental for data modelling and cleansing, so make sure you are familiar with DAX and M functions.
The Definitive Guide to DAX book by Marco Russo & Alberto Ferrari is a great resource that will help you go from basic to the advance DAX user. They also have some great blogs on their website sqlbi.com that breakdown some of the DAX functions.
YouTube has some good resources on DAX, the DAX Fridays series by Curbal channel which goes through some of the key functions with good examples.
The Guy in Cube YouTube channel run by Adam Saxton and Patrick LeBlanc (Microsoft Employees) gives you great overview of new features and has some good training videos.
Resources for Power Query (M) is a little bit harder to come by, however the MSDN Library categories all the M functions and provides a good description.
Practice Makes Perfect
Finally, make sure you practice by developing models and use the different features in Power BI and Excel. This will help you develop your navigation knowledge as well understanding of any limitations.
Bonus: The Community
Power BI is very community focused, which is rapidly growing and very helpful. There are several Power BI Users Groups (PUG) ran across the country where actually users share tips and knowledge. Consider joining a Power BI Users Group, you may learn something new or just give back to the community.
I hope this gives you some useful tips and somewhere to start in preparation for your certification. I wish you luck for your certification and enjoy Power BI & Excel.