Welcome to the second post in my series concerning Microsoft Exam 70-778, where I hope to provide a detailed revision tool for those preparing to take this exam. Last week’s post introduced the fundamental notions behind connecting to data sources in Power BI Desktop, and we will lead on from this to look at the Perform transformations topic, which covers the following skills:

Design and implement basic and advanced transformations; apply business rules; change data format to support visualization

Let’s jump straight in by welcoming the concept that is most relevant to all of this…

Power Query

First introduced in Excel some years back, the Power Query M formula language is very much the engine underneath the hood of Power BI. It deals with everything from the retrieval of data through to the removal of rows, creation of new columns, filtering – basically, if it concerns data manipulation, Power Query can more than likely handle it. By right-clicking any Query within the Power Query Editor window and selecting Advanced Editor, you can view the syntax of the language in detail and edit it to your hearts contents. In the screenshot below, Power Query is connecting to the WideWorldImporters database and returning the table Application.PaymentMethods:

Now, if you are coming into Power BI for the first time, the thought of having to learn a whole new programming language can be a little daunting. That is why the Power Query Editor is the ideal tool for beginners to carry out most (if not all) data transformation from within the Power BI interface. We will take a look at this topic in more detail shortly, but when it comes to working with Queries, it is worth mentioning the following pieces of functionality that we have at our disposal:

  • The Advanced Editor has some minimal syntax error detection built in, but nothing on the same par as IntelliSense. Triple checking your Queries is always recommended to avoid any errors when loading your data.
  • Queries can be renamed at any time and be given detailed descriptions if required. This step is generally recommended to help users better understand the data they are interfacing with.
  • Queries will remain actively loaded within the Power Query Editor, unless and until they are disabled explicitly by right-clicking on them and de-selecting the Enable load option. Queries with names in italics are disabled.
  • It is possible to both Duplicate & Reference queries at any time. The Reference option is particularly useful if you need to create variants of a particular source query that filters on different values, for example. Regardless of whether the Query is loaded directly into the model or not, it can be duplicated or referenced without issue.
  • It is possible also to create Parameter Queries, and even Custom Function Queries to, for example, perform highly specific transformation actions for each column value provided. Parameters will be discussed in greater detail later on, whereas Custom Functions are beyond the scope of the exam.

Transforming Data: A Brief Overview

The Transform and Add Column tabs within the Power Query Editor are your go-to destinations when it comes to finding out what you can do from a transformation perspective:

With the toolbox provided here, you can do things such as:

  • Pivot/Unpivot your data.
  • Replace column values.
  • Split data based on delimiters.
  • Perform advanced mathematical calculations.
  • Create new columns derived from date/time values, such as Month Name or time durations.
  • Define new columns based on examples, specific calculations/conditions or from another column value.

The example at the end of this post will cover some of these specific transformation steps in detail, showing you how to apply them straightforwardly in the Power Query Editor.

Merging & Appending Queries

In some cases, you are likely to be bringing in data that is similar or related in some way, and your principle requirement will be to bring this together into a more simplistic view. In this regard, the following two features are especially useful:

  • Merging: This involves combining two queries horizontally. If you are familiar with SQL JOINs, then this is the same thing. You define your base table and table to merge, the key values to pair on and then, finally, your join type. You have the following options at your disposal here:
    • Left Outer
    • Right Outer
    • Full Outer
    • Inner
    • Left Anti
    • Right Anti
  • Appending: Best used when you have queries with the same overall structure, this step involves combining Queries vertically. The number of Queries can be as many as you want and you have the option of either a) appending onto the existing Query or b) onto an entirely new one. It is also possible, but not recommended, to Append Queries with entirely different structures.

Using Parameters

Parameters are a great way to give you the ability to quickly and easily modify filters in one or more Queries. They are created from the Home tab by going to the Manage Parameters -> New Parameter option, as indicated below:

Then, you will be able to define your Parameter. For example, to create a Parameter that will filter the PaymentMethodName field on the Application PaymentMethods Query, specify the settings as indicated below:

A new Query for the Parameter will appear on the left-hand pane, like so:

Then, go to the Application PaymentMethods, click on the button with the arrow and select Text Filters -> Equals… to open the Filter Rows window. Make sure that equals is selected and, on the second dropdown box, select Parameter and then the newly created Parameter:

Pressing OK will then apply the appropriate filter. Any changes made to the selected Parameter value will update automatically to the filters you have defined. When it comes to working with many filters across multiple Queries, Parameters can take away a lot of the pressure involved.

Query Folding

Where possible, when performing transformation steps within the Power Query Editor, Power BI will attempt to figure out the most optimal natural language to use when querying the data source and apply this accordingly. In most cases, this will only occur for SQL based data sources. In the example below, after right-clicking on the Applied Steps for the Sales.Invoice query and selecting View Native Query, we can view the underlying T-SQL query used:

You should, therefore, pay careful attention to the order in which you apply your steps to ensure that Query Folding takes place wherever possible. There is an excellent article on the MSSQLTips website that goes into greater detail on this whole subject.

Example: Transforming Table Data Using Power Query

Picking up from where we left off last time, we will now perform a range of different transformation actions against the Sales.CustomerTransactions table from the WideWorldImporters sample database. The steps that follow are designed to give you a flavour of the types of transformation activities that you can perform against your data:

  1. Within Power BI Desktop, click on the Edit Queries button. The Power Query Editor window will open, listing all of the tables imported from the WideWorldImporters database. The Query Sales CustomerTransactions should already be selected but, if not, double-click on it to load the data from this data source into the main window:
  2. We can see in the main window that Power BI has automatically decided the best data types to use, based on the underlying SQL table schema. However, for all columns that relate to financial data (AmountExcludingTax, TaxAmount, TransactionAmount & OutstandingBalance), it will be more prudent to convert these into the most appropriate data type for currency values – Fixed decimal number. While holding down the CTRL key, left-click to select each of the columns mentioned above, right-click and select Change Type -> Fixed decimal number:
  3. Notice now that a $ symbol appears next to each of the chosen fields. You can also see, on the right-hand pane, underneath Applied Steps, a new Applied Step called Changed Type:
  4. As the interface is used to modify the data, the appropriate Power Query M code transformation occurs behind the scenes. All Applied Steps are reversible, and this can be done by highlighting it and pressing the X icon. It can also be renamed by selecting it and pressing the F2 button.
  5. Several columns have been brought over from the Sales.CustomerTransactions table that will not be particularly useful to end users, specifically:
    • CustomerID
    • TransactionTypeID
    • InvoiceID
    • PaymentMethodID
  6. These can be removed by using the CTRL and left-click method, right-clicking any of the selected columns and selecting the Remove Columns option:
  7. Because we have imported data alongside other related tables, there will be some special relationship column types at the end of our table. An example of this is the Application.People field. For this example, we need to extract the FullName value from this table and include it as part of our current query, by clicking on the two arrows icon on the top left of the field, ticking the FullName field and pressing OK. You can (optionally) tick/untick the box at the bottom that says Use original column as prefix, which does exactly what it says on the tin:
  1. At this point, you can also remove all other relationship columns pictured below using the same method outlined in step 3:
  2. The TransactionAmount field provides us with a total of each orders total value, by adding together the AmountExcludingTax and TaxAmount fields. Let’s assume for a second that this field does not exist in our data; in this scenario, it is possible to create a Custom Column that performs the appropriate calculation and adds this onto our table as a new field. On the Add Column tab, the Custom Column button is one way of doing this. Then, define the appropriate formula to add together both field values, using familiar, Excel-like syntax:
  3. A common requirement for reporting is the ability to report sales based on the quarter of the year. To meet this requirement, Power Query can extract information like this from a Date field with relative ease. With the TransactionDate field selected, go to the Add Column tab and select Date -> Quarter -> Quarter of Year:
  4. A new column called TransactionQuarter will be created, which can be dragged and dropped next to the TransactionDate field to keep things tidy:
  5. Another common sales reporting requirement is being able to rank a particular sale by category. Again, Power Query can come to the rescue with the Conditional Column feature:
  6. If you are familiar with if/else conditional logic flow, then the next part will be pretty straightforward 🙂 Within the Add Conditional Column window, populate each field with the field values indicated below and then press OK. You can use the Add rule button to include the additional Else if rows required for this field:
  7. Once added, we can then view the field at the end of our table, working as expected:

At this point, our model is ready, but you could experiment further if you wish. Some additional transformation steps could include:

  • Extracting the Month Name value from the TransactionDate field.
  • Use the Split Column feature to extract the Forename and Surname from the FullName field, using Space as a delimiter.
  • Filter the OutstandingBalance column to only include data where the value does not equal 0.
  • Rename all columns and the Query itself to more human-readable names.

Key Takeaways

  • The Power Query M formula language is used to perform transformations to data once loaded into Power BI. Although it is possible to do this via code, Power BI allows us to define all of our required data changes from within the interface, without the need to write a single line of code.
  • Each data source connected to represents itself as a Query within Power BI. There are many options at your disposal when working with Queries, such as renaming, merging, duplication and the ability to disable or reference as part of other Queries.
  • There are wide-range of column transformations that can be applied, which are too numerous to mention. The Transform tab provides the best means of seeing what is available, with options ranging from formatting through to grouping and pivoting/unpivoting.
  • New columns are addable via the Add Column tab. You can choose to base new columns on calculations, conditional logic, other column values or as a defined list of ascending numbers, which may be useful for indexing purposes.
  • It is possible to merge or append queries together to suit your specific requirements. Merging involves the horizontal combination of Queries, whereas appending represents a vertical combination.
  • Parameters can be used to help optimise any complex filtering requirements.
  • Where possible, Power Query will attempt to use the most optimal query for your data source, based on the transformation steps you define. This action is known as Query Folding and, in most cases, SQL-derived data sources will support this option by default.

In the next post, we will take a look at the options available to us from a data cleansing perspective and how it is possible to apply optimisation to a messy example dataset.

As discussed recently on the blog, I have been on a journey to try and attain the Microsoft Certified Solutions Associate Certification in BI Reporting. I was very fortunate to overcome the final hurdle of this task by passing Exam 70-778: Analyzing and Visualizing Data with Microsoft Power BI the other day. I enjoyed the opportunity to dive deeper into the world of Business Intelligence, particularly given the enhanced role Power BI has within the Business Applications space today. With this in mind, and in the hopes of encouraging others, today’s post is the first in a new series of revision notes for Exam 70-778. I hope that you find this, and all future posts, useful as either a revision tool or as an introduction into the world of Power BI.

The first skill area of the exam is all around how to Import from data sources, as described on the exam specification:

Connect to and import from databases, files, and folders; connect to Microsoft SQL Azure, Big Data, SQL Server Analysis Services (SSAS), and Power Query; import supported file types; import from other Excel workbooks; link to data from other sources

To begin with, I will provide a detailed overview of the topic areas covered above, before jumping into an example of how to import data into Power BI.

Supported Data Sources

The great benefit of Power BI is its huge list of supported connectors, which are integrated neatly within the application itself. The list of all possible data sources changes on a monthly basis, and it is impossible to go into detail on each one. Suffice to say; you should at least be familiar with the following data sources:

  • SQL Server (on-premise & Azure)
  • SQL Server Analysis Services
  • A wide range of vendor-specific Relational Database Management Systems (RDBMS’s), such as Oracle, MySQL, PostgreSQL, SAP Hana
  • Any data source that supports Open Database Connectivity (ODBC) or Object Linking and Embedding, Database (OLEDB).
  • The following flat file types:
    • Excel (.xlsx)
    • Text (.txt)
    • Comma Separated Value documents (.csv)
    • Extensible Markup Language (.xml)
    • JavaScript Object Notation (.json)
  • Web sources, such as Web pages or OData Feeds

Some RDBMS vendor solutions have a requirement to install additional software, which will enable you to interact with that particular data source. You should check the relevant documentation for each vendor to verify any specific requirements.

Power BI also supports a wide range of Microsoft proprietary and non-proprietary applications, such as Dynamics 365 Customer Engagement, SharePoint, Google Analytics & SalesForce. If you are feeling particularly technical, then you can also use the Blank Query option to, in theory, connect to any data source of your choosing or even go as far as building custom connectors yourself to interact with a specific application.

Bulk File Loading

As well as supporting connections to single flat files, it is also possible to interact with multiple files existing in the same location. This feature can be useful if, for example, there is a requirement to process hundreds of .csv files with different data, but the same overall structure. The supported list of bulk file locations are:

  • Windows file system folder
  • SharePoint document folder
  • Azure Blob Storage
  • Azure Data Lake Storage

When loading multiple files into Power BI, you not only can read the contents of each file but can also access file-level metadata, as indicated below:

Import vs DirectQuery

An important design decision when working with data sources concerns the data connectivity mode to be used. Your final choice will generally fall into one of two options:

  • Import: When connecting to your data source, Power BI takes a copy of all data and stores it within your report. By implication, this places additional pressure on your local machines disk space and memory consumption. Import is the default option for most data sources and, to ensure that your data remains consistently up to date when deployed to the Power BI service, you have the opportunity of defining your data refresh frequency – 8 times a day for Power BI Professional and 48 times a day for Power BI Premium subscriptions. Import is the most sensible option to choose when there is no requirement for regular refreshing of your data sources or if performance concerns arise when using…
  • DirectQuery: Instead of taking a snapshot of the data, Power BI will read the data at source and store only the schema of the data within the model. At the time of writing this post, only a select number of mostly SQL based data sources are compatible with this feature. DirectQuery is your best choice when there is a need to keep reports continually up to date, and when your target data source is sufficiently beefed up to handle frequent requests. It’s also worth bearing in mind the following points when evaluating DirectQuery:
    • DirectQuery only supports a single data source connection for the entire model, with no option of defining additional sources. While traditionally true, the release of composite models for DirectQuery removes this much-loathed limitation.
    • There are limitations when it comes to data transformation options, especially for non-Microsoft data sources.
    • Some query types will be unsupported.
    • For data modelling using DAX, there are some crucial limitations. For example, Measures that use the SUMX & PATH functions (or their related counterparts) are not allowed.

You should also be aware of a third option – Live Connection – which behaves similar to DirectQuery but is for SQL Server Analysis Services only. This option has the following limitations:

  • Not possible to define relationships
  • No possibility to transform data from within Power BI.
  • Data modelling options, except for Measure creation, are almost non-existent.

Importing Excel Workbooks

There are some aspects of working with Excel documents in Power BI that are worth further consideration. You mostly have two options at your disposal to consume Excel workbooks:

  1. Import Data: Similar to working with any other flat file source, data within each of the following Excel objects is importable into Power BI:
    • Tables
    • Sheets
    • Ranges
  2. You can see below how this looks for a file containing four worksheets:
  3. Import workbook contents: If you have built out a complex spreadsheet that utilises the full range of features available in the Excel Data Model, then it is also possible to import these into Power BI “as-is”. The following Excel Data Model features are exportable in this manner:
    • Power Query queries
    • Power Pivot Data Models
    • Power View Worksheets
    • (Most) Power View visuals; where a visual is unsupported in Power BI, an error appears on the appropriate visual.

Example: Importing SQL Server Database Data

What follows now is a typical data connection exercise in Power BI Desktop, which involves connecting to a SQL Server database. The experience described here is mostly similar for other data sources and, therefore, represents an optimal example to familiarise yourself with connecting to data sources in the application:

  1. Launch Power Bi Desktop and, on the splash screen, select the Get data link on the left-hand pane:
  2. On the Get Data window, choose Database on the left-hand list, select SQL Server database and then press the Connect button:
  3. You will now be prompted to provide the following details:
    • Server: This will be either the Fully Qualified Domain Name (FQDN) of the computer with a default SQL Server instance or the computer name and named instance name (e.g. MYSQLSERVER/MYSQLSERVERINSTANCE). In the example below, we are connecting to a default SQL Server instance on the computer JG-LAB-SQL
    • Database: If you already know the name of the database you want to access, you can type this here; otherwise, leave blank. In this example, we are connecting to the WideWorldImporters sample database.
    • Data Connectivity mode: See the section Import vs DirectQuery above for further details. For this example, select the Import setting:
  4. There are also several additional options that are definable in the Advanced options area:
    • Command timeout in minutes: Tells Power BI how long to wait before throwing an error due to connection issues.
    • SQL statement: Specify here a pre-compiled SQL statement that will return the objects/datasets that you require. This option can be useful if you wish to reduce the complexity of your model within Power BI or if there is a requirement to return data from a stored procedure.
    • Include relationship columns: Enabling this setting will return a single column for each defined relationship which, when expanded, gives you the ability to add related column fields onto your table object.
    • Navigate using full hierarchy: Enabling this will allow you to navigate through the database hierarchy using schema object names. In most cases, this should remain disabled, unless there a specified schema names in your dataset (like Application, Sales, Purchasing etc. in the WideWorldImporters database).
    • Enable SQL Server Failover support: If enabled, then Power BI will take advantage of any failover capability setup on your SQL Server instance, re-routing requests to the appropriate location where necessary.
  5. Illustrated below are some example settings for all of the above. For this walkthrough, leave all of these fields blank and then press OK to continue.
  6. The Navigator window will appear, which will enable you to select the Tables or Views that you wish to work within the model. Selecting any of the objects listed will load a preview in the right-hand window, allowing you to see a “sneak peek” of the schema and the underlying data. Tick the object Sales.CustomerTransactions and then press the Select Related Tables button; all other Tables that have a relationship with the Sales.CustomerTransactions are then automatically included. Press Load when you are ready to import all selected table objects into Power BI.
  7. After a few moments, the Load window will appear and update accordingly as each table object gets processed by Power BI (exact times may vary, depending on the remote server/local machines capabilities). Eventually, when the window closes, you will see on the right-hand pane that all table objects have been loaded into Power BI and are ready to use for building out visualizations:
  8. At this stage, you would then look at loading up your imported objects into Power Query for fine-tuning. But that’s a topic for the next post 🙂

Key Takeaways

  • Power BI supports a broad range of database systems, flat file, folder, application and custom data sources. While it is impossible to memorise each data source, you should at least broadly familiarise yourself with the different types at our disposal.
  • A crucial decision for many data sources relates to the choice of either Importing a data source in its entirety or in taking advantage of DirectQuery functionality instead (if available). Both routes have their own defined set of benefits and disadvantages. DirectQuery is worth consideration if there is a need to keep data regularly refreshed and you have no requirement to work with multiple data sources as part of your solution.
  • Live Connection is a specific data connectivity option available for SQL Server Analysis Services. It behaves similarly to DirectQuery.
  • It is possible to import an existing Excel BI solution into Power BI with minimal effort, alongside the ability to import standard worksheet data in the same manner as other flat file types.

Look out for my next post in this series, where I will take a look at the range of transformation options available to us in Power BI, and work through some examples applied against the tables listed above.

I don’t typically stray too far from Microsoft technology areas as part of this blog, but having experienced this particular issue at the coalface and, being acutely aware of the popularity of the WordPress platform for many bloggers, I thought I’d do a specific post to help spread awareness. For those who are in a hurry…

TL;DR VERSION: IF YOU ARE USING THE WP GDPR COMPLIANCE PLUGIN ON YOUR WORDPRESS WEBSITE, UPDATE IT ASAP AND CHECK YOUR WORDPRESS INSTANCE FOR ANY NEW/SUSPICIOUS USER ACCOUNTS; IF EXISTING, THEN YOUR WEBSITE HAS BEEN HACKED. IMMEDIATELY TAKE YOUR SITE OFFLINE AND RESTORE FROM BACKUP, REMOVING AND THEN REINSTALLING THE ABOVE PLUGIN MANUALLY.

When it comes to using WordPress as your blogging platform of choice, the journey from conception to fully working blog can be relatively smooth. The ease of this journey is due, in no small part, to the vast variety of custom extensions – Plugins – available to end-users. These can help to overcome common requirements, such as adding Header/Footer scripts to your website, integrating your website with tools such as Google reCAPTCHA and even to allow you to transform WordPress into a fully-featured e-commerce site. The high degree of personal autonomy this can place in your hands when building out your web presence is truly staggering, and there is no fault on the part of the WordPress project for its regular performance, feature and security release cycles. All of this has meant that the product has grown in popularity and adoption over time.

Regrettably, the applications greatest strength is also its critical weakness point. WordPress is by far the most highly targeted application on the web today by hackers or malicious users. The latest CVE database result for the Content Management System (CMS) proves this point rather definitively but does not explain one of the most common reasons why WordPress is such a major target – namely, that most WordPress deployments are not subject to regular patching cycles. Plugins are by and large more susceptible to this, and any organisation which does not implement a monthly patching cycle for their WordPress site is significantly heightening their risk of being attacked. Even with all of this in place, you are not immune, as what follows demonstrates rather clearly:

On the 6th of November, a plugin designed to assist administrators in meeting their requirements under GDPR vanished from the WordPress Plugin store due to a “security flaw”. The developers deserve full credit and recognition here – within a small space of time, they had released a new version of the plugin with the flaw addressed – but hackers were quick on the ball with this particular vulnerability. On the afternoon of Thursday 8th November, I was alerted to the following actions which were carried out on numerous WordPress websites that I have responsibility for looking after:

  • The WordPress site setting Anyone can register setting was forcibly enabled, having been disabled previously.
  • Administrator became the default role for all new user accounts, having been set to Subscriber previously.
  • Next, a new user account – with a name matching or similar to “trollherten” – was created, containing full administrator privileges. Depending on your WordPress site configuration, an email was then sent to an email address, exposing the full details of your website URL and giving the attacker the ability to login into your site.

From this point forward, the attacker has the keys to the kingdom and can do anything they want on your WordPress website – including, but not limited to, blocking access for other users, installing malicious codes/themes or accessing/downloading the entire contents of the site. The success of the attack lies in its rapid targeting, given the very brief window between the disclosure of the plugin flaw and the timing of the attack, and the relative straightforwardness of automating all of the required steps outlined above. For those who are interested in finding out more about the technical details of the attack, then WordFence has published a great article that goes into further detail on this subject.

So what should I do if my WordPress site is using this plugin or there is evidence of a hacking attempt?

Here is my suggested action list, in priority order, for immediate action:

  • Take your website offline, either by switching off your web server or, if you are using Azure App Service, you have some nifty options at your disposal to restrict access to your site to trusted hosts only.
  • Restore your website from the last, good backup.
  • Update the WP GDPR Compliance plugin to the latest version.
  • As a precaution, change the credentials for all of the following on the website:
    • User Accounts
    • Web Server FTP
    • Any linked/related service to your site that stores privileged information, such as a password, authorisation key etc.
  • Review the following points and put in the appropriate controls, where necessary, to mitigate the risk of a future attack:
    • Patching Cycle for WordPress, Plugins & Themes: You should ideally be carrying out regular patching of all of these components, at least once per month. There are also plugins available that can email you when a new update is available which, in this particular scenario, would have helped to more speedily identify the faulty plugin.
    • Document your Plugins/Themes: You should have a full list of all plugins deployed on your WordPress website(s) stored somewhere, which then forms the basis of regular reviews. Any plugin that has a published vulnerability that has not been addressed by the developer should be removed from your website immediately.
    • Restrict access to the WordPress Admin Centre: .htaccess rules for Apache or web.config changes for IIS can restrict specific URLs on a site to an approved list of hosts. This way, you can ensure that even if an exploit like the one described in this post takes place, the attacker will be restricted when trying to login into your WordPress backend.
    • Review Backup Schedule: Typically, I’ve found that incidents like this can immediately demonstrate flaws in any backup procedure that is in place for a website – either in not being regular enough or, in the worst case, not taking place at all. You should ideally be performing daily backups of your WordPress website(s). Again, Azure makes this particularly easy to implement, but you can also take advantage of services such as VaultPress, which take all the hassle out of this for a small monthly price.

Conclusions or Wot I Think

Attacks of the nature described in this post are an unfortunate byproduct of the internet age and, regrettably, some of the evidence relating to this particular attack does, unfortunately, show that individuals and small businesses are the unfortunate casualties in today’s virtual conflicts on the world stage. Constant vigilance is the only best defence that you can have, more so given the speedy exploitation of this particular flaw. And, there has to be a frank admission that attacks like this are not 100% preventable; all necessary attention, therefore, should be drawn towards risk reduction, with the ultimate aim being to put in place as many steps possible to avoid an obvious target from being painted on your back. I hope that this post has been useful in making you are aware of this issue (if you weren’t already) and in offering some practical tips on how to resolve.

The life of a Dynamics CRM/Dynamics 365 for Customer Engagement (CRM/D365CE) professional is one of continual learning across many different technology areas within the core “stack” of the Business Applications platform. Microsoft has clarified this in no uncertain terms recently via the launch of the Power Platform offering, making it clear that cross-skilling across the various services associated with the Common Data Service is no longer an optional requirement, should you wish to build out a comprehensive business solution. I would not be surprised in the slightest if we find ourselves in a situation where the standard SSRS, Chart and Dashboarding options available within CRM/D365CE become deprecated soon, and Power BI becomes the preferred option for any reporting requirements involving the application. With this in mind, knowledge of Power BI becomes a critical requirement when developing and managing these applications, even more so when you consider how it is undoubtedly a core part of Microsoft’s product lineup; epitomised most clearly by the release of the Microsoft Certified Solutions Architect certification in BI Reporting earlier this year.

I have been doing a lot of hands-on and strategic work with Power BI this past year, a product for which I have a lot of affection and which has numerous business uses. As a consequence, I am in the process of going through the motions to attain the BI Reporting MCSA, having recently passed Exam 70-779: Analyzing and Visualizing Data with Microsoft Excel. As part of this week’s post, I wanted to share some general, non-NDA breaching advice for those who are contemplating going for the exam. I hope you find it useful 🙂

Power BI Experience is Relevant

For an exam focused purely on the Excel sides of things, there are a lot of areas tested that have a significant amount of crossover with Power BI, such as:

  • Connecting to data sources via Power Query in Excel, an experience which is an almost carbon copy of working with Power Query within Power BI.
  • Although working with the Excel Data Model, for me at least, represented a significant learning curve when revising, it does have a lot of cross-functionality with Power BI, specifically when it comes to how DAX fits into the whole equation.
  • Power BI is even a tested component for this exam. You should, therefore, expect to know how to upload Excel workbooks/Data Models into Power BI and be thoroughly familiar with the Power BI Publisher for Excel.

Any previous knowledge around working with Power BI is going to give you a jet boost when it comes to tackling this exam, but do not take this for granted. There are some significant differences between both sets of products (epitomised by the fact that Excel and Power BI, in theory, address two distinctly different business requirements), and this is something that you will need to understand and potentially identify during the exam. But specific, detailed knowledge of some of the inner workings of Power BI is not going to be a disadvantage to you.

Learn *a lot* of DAX

DAX, or Data Analysis Expressions, are so important for this exam, and also for 70-778 as well. While it will not necessarily be required for you to memorise every single DAX expression available to pass the exam (although you are welcome to try!), you should be in a position to recognise the structure of the more common DAX functions available. You ideal DAX study areas before the exam may include, but is not limited to:

A focus, in particular, should be driven towards the syntax of these functions, to the extent that you can memorise example usage scenarios involving them.

Get the exam book

As with all exams, Microsoft has released an accompanying book that is a handy revision guide and reference point for self-study. On balance, I feel this is one of the better exam reference books that I have come across, but beware of the occasional errata and, given the frequency of changes these days thanks to the regular Office 365 release cycle, be sure to supplement your learning with any proper online cross-checking.

Setup a dedicated lab environment

This task can be accomplished alongside working through the exercises in the exam book referenced above but, as with any exam, hands-on experience using the product is the best way of getting a passing grade. Download a copy of SQL Server Developer edition, restore one of the sample databases made available by Microsoft, get a copy of Excel 2016 and – hey presto! – you now have a working lab environment & dataset that you can interact with to your heart’s content.

Pivot yourself toward greater Excel knowledge

Almost a quarter of the exam tests candidates on the broad range of PivotTable/PivotChart visualisations made available within Excel. With this in mind, some very detailed, specific knowledge is required in each of the following areas to stand a good chance of a passing grade:

  • PivotTables: How they are structured, how to modify the displaying of Totals/Subtotals, changing their layout configuration, filtering (Slicers, Timelines etc.), auto-refresh options, aggregations/summarising data and the difference between Implicit and Explicit Measures.
  • PivotCharts: What chart types are available in previous and newer versions of Excel (and which aren’t), understanding the ideal usage scenario for each chart type, understanding the different variants available for each chart types, understanding the structure of a chart (Legend, Axis etc.), chart filtering and formatting options available for each chart.

Check out the relevant edX course

As a revision tool, I found the following edX course of great assistance and free of charge to work through:

Analyzing and Visualizing Data with Excel

The course syllable mirrors itself firmly to the skills measured for the exam and represents a useful visual tool for self-study or as a means of quickly filling any knowledge gaps.

Conclusions or Wot I Think

It is always essential, within the IT space, to keep one eye over the garden fence to see what is happening in related technology areas. This simple action of “keeping up with the Joneses” is to ensure no surprises down the road and to ensure that you can keep your skills relevant for the here and now. In case you didn’t realise already, Power BI is very much one of those things that traditional reporting analysts and CRM/D365CE professionals should be contemplating working with, now or in the immediate future. As well as being a dream to work with, it affords you the best opportunity to implement a reporting solution that will both excite and innovate end users. For me, it has allowed me to develop client relationships further once putting the solution in place, as users increasingly ask us to bring in other data sources into the solution. Whereas typically, this may have resulted in a protracted and costly development cycle to implement, Power BI takes all the hassle out of this and lets us instead focus on creating the most engaging range of visualisations possible for the data in question. I would strongly urge any CRM/D365CE professional to start learning about Power BI when they can and, as the next logical step, look to go for the BI Reporting MCSA.