For the past 13 weeks on the blog, I have delivered a series of posts concerning Microsoft Exam 70-778, specifically focused towards providing a set of detailed revision notes that cover the broad array of Power BI features assessed as part of the exam. To round things off, today’s blog will bridge together everything I have discussed thus far in the series; with the hope being that this post can be a single reference point for those who have not been following the series to date.

Microsoft Exam 70-778 Overview

The exam, with its full title Analyzing and Visualizing Data with Microsoft Power BI, is targeted towards Business Intelligence (BI) and data professionals who are looking to validate their skills in working with Power BI. The exam is a necessary component, alongside Exam 70-779: Analyzing and Visualizing Data with Microsoft Excel, in attaining the Microsoft Certified Solutions Associate (MCSA) certification in BI Reporting. Successful candidates can then (optionally) pass an additional “elective” exam to gain the Microsoft Certified Solutions Expert (MCSE) certification in Data Management and Analytics.

Skills Measured in the Exam

The skills measured are outlined below, alongside links to the relevant posts from the series and the list of essential points to remember:

Consuming and Transforming Data By Using Power BI Desktop

Connect to data sources.
Skills Measured

May include: Connect to databases, files, folders; import from Excel; connect to SQL Azure, Big Data, SQL Server Analysis Services (SSAS)

Revision Notes

Exam 70-778 Revision Notes: Importing from Data Sources

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.
Perform transformations
Skills Measured

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

Revision Notes

Exam 70-778 Revision Notes: Performing Data Transformations

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.
Cleanse data
Skills Measured

May include: Manage incomplete data; meet data quality requirements

Revision Notes

Exam 70-778 Revision Notes: Cleansing Data

Key Takeaways
  • Data can be filtered directly within Power Query, using Excel-like functionality to assist you in only returning the most relevant data in your queries. The data type of each field plays a particularly important part of this, as only specific filter options will be at your disposal if, for example, you are working with numeric data.
  • From a data quality perspective, you typically will need to handle column values that contain one of two possible value types:
    • Errors: This will usually occur as a result of a calculated column field not working correctly. The best solution will always be to address any issues with your calculated column, such as by using a conditional statement to return a default value.
    • Blanks/NULLs: A common symptom when working with SQL derived data sources, your real problems with blank values start to appear when you attempt to implement DAX custom columns/Measures outside of the Power Query Editor. It is, therefore, recommended that these are dealt with via a Replace action, depending on your fields data types. For example, a number field with blank/NULL values should be replaced with 0.
  • The Remove Rows option(s) can act as a quick way of getting rid of any Error or Blank/NULL rows and can also be utilised further to remove duplicates or a range of rows. In most cases, you will have similar options available to you with Keep Rows instead.
  • There are a variety of formatting options available to us when working with text/string data types. These range from fixing capitalisation issues in data, through to removing whitespace/non-printable character sets and even the ability to prepend/append a new value.

Modeling and Visualizing Data

Create and optimize data models.
Skills Measured

May include: Manage relationships; optimize models for reporting; manually type in data; use Power Query

Revision Notes

Exam 70-778 Revision Notes: Create and Optimise Data Models

Key Takeaways
  • Relationships form the cornerstone of ensuring the long-term viability and scalability of a large data model. Assuming you are working with well-built out, existing data sources, Power BI will automatically detect and create Relationships for you. In situations where more granular control is required, these Relationships can be specified manually if needed. It is worth keeping in mind the following important features of Relationships:
    • They support one-to-one (1:N), one-to-many (1:N) and many-to-one (N:1) cardinality, with many-to-many (N:N) currently in preview.
    • Filter directions can be specified either one way or bi-directionally.
    • Only one relationship can be active on a table at any given time.
  • It is possible to sort columns using more highly tailored custom logic via the Sort By Column feature. The most common requirement for this generally involves the sorting of Month Names in date order but can be extended to cover other scenarios if required. To implement, you should ensure that your data has a numbered column to indicate the preferred sort order.
  • Moving outside of the Power Query Editor presents us with more flexibility when it comes to formatting data to suit particular styling or locale requirements. While the majority of this functionality provides date/time and currency formatting options, for the most part, it is also possible to categorise data based on Location, the type of URL it is or on whether or not it represents a Barcode value; these options can assist Power BI when rendering certain types of visualizations.
  • There may be ad-hoc requirements to add manually defined data into Power BI – for example, a list of values that need linking to a Slicer control. The Enter Data button is the “no-code” route to achieving this and supports the ability to copy & paste data from external sources. For more advanced scenarios, you also have at your disposal a range of M code functionality to create Lists, Records and Tables, which can be extended further as required.
Create calculated columns, calculated tables, and measures
Skills Measured

May include: Create DAX formulas for calculated columns, calculated tables, and measures; Use What If parameters

Revision Notes

Exam 70-778 Revision Notes: Using DAX for Calculated Columns

Key Takeaways
  • DAX is the primary formula language when working with datasets outside of Power Query. It includes, to date, more than 200 different types of functions that can assist in all sorts of data modelling.
  • An important concept to grasp within DAX is context and, specifically, row context (formulas that calculate a result for each row in a dataset) and filter context (formulas that automatically apply any filtering carried out at report level).
  • The sheer amount of DAX functions available makes it impossible to master and remember all of them, particularly when it comes to the exam. Your learning should, therefore, focus on learning the general syntax of DAX and the general types of functions available (aggregation, date/time etc.)
  • There are three principal means of utilising DAX with Power BI:
    • As Measures: These typically present a scalar value of some description, often an aggregation or a result of a complex formula. Using them in association with a Card visualization type is recommended, but this is not a strict requirement.
    • As Calculated Columns: Similar to the options available within Power Query, Calculated Columns provide a dynamic and versatile means of adding new columns onto your datasets. Compared with the options available within Power Query and the complexity of the M language, DAX Calculated Columns might represent a more straightforward means of adding custom columns onto your datasets.
    • As Calculated Tables: A powerful feature, mainly when used in conjunction with Calculated Columns, you have the ability here to create entirely new datasets within the model. These will typically derive from any existing datasets you have brought in from Power Query, but you also have functionality here to create Date tables, sequence numbers and manually defined datasets as well.
  • What-if Parameters provide of means of testing DAX formulas, as well as allowing report users to perform predictive adjustments that can affect multiple visualizations on a report.
Measure performance by using KPIs, gauges and cards.
Skills Measured

May include: calculate the actual; calculate the target; calculate actual to target; configure values for gauges; use the format settings to manually set values

Revision Notes

Exam 70-778 Revision Notes: Utilising KPIs with Gauge Visualisations

Key Takeaways
  • There are two principle visualization types available within Power BI to help track actual-to-target progress – KPIs and Gauges.
  • KPIs provide a more visually unique means of a binary success/fail determination when tracking towards a target. It is also possible to use KPI’s to track variance over time via the Trend axis. The Indicator will typically be the result of some form of aggregation or Measure.
  • Gauges provide a less visually distinctive, but non-binary, mechanism of viewing progress towards a target. Gauges support more potential field well values when compared with KPIs, nearly all of which are optional in some way. You can also manually define some of these values, for situations where your data model does not contain the required information.
  • All visualizations within Power BI are modifiable from a display or formatting perspective. The same basic options will generally be supported – such as changing a font type or background colour – with more specific configuration properties available per unique visualization type. For example, a KPI visualization can be customised to hide the background Trend Axis entirely. All of these options are designed to give developers greater control over the look and feel of their reports and to mirror them as closely as possible to any potential branding requirement.
  • When building out a solution designed to monitor progress within Power BI, the steps involved will typically be more in-depth than merely creating a new visualization. In most cases, there will be a requirement to bring together a lot of the other skills that have been discussed previously within this series – such as creating DAX formulas, modifying data within Power Query or bringing together different data sources into a single model. It is essential, therefore, not to underestimate the amount of time and effort involved in creating a practical solution that takes advantage of KPIs or Gauges.
Create hierarchies
Skills Measured

May include: Create date hierarchies; create hierarchies based on business needs; add columns to tables to support desired hierarchy

Revision Notes

Exam 70-778 Revision Notes: Creating Hierarchies

Key Takeaways
  • Hierarchies within Power BI provide a means of logically categorising data into an order of preference or precedence, providing greater flexibility to Power BI report users when they interact with visualizations.
  • Date Hierarchies are created and managed automatically by Power BI for each Date or Date/Time field defined within your model. These automatically create fields that contain the Year, Quarter, Month & Day values from the respective date fields. These fields can then be utilised as part of a Table visualization or within a DAX formula. Date Hierarchies can also be completely disabled if required.
  • Custom (or User-Defined) Hierarchies need to be created manually and provide additional customisation options when compared to Date Hierarchies, such as the number of fields they contain, the order and its name. A Custom Hierarchy will typically make use of one of several Parent/Child DAX functions, such as PATH or PATHITEM.
  • Including a hierarchy as part of a chart visualization, such as a Pie chart or Donut chart, opens up other drill-down capabilities around your data. Indicated by the additional arrow icons included at the top of the visualization, they provide the means for users to interrogate data points that interest them the most straightforwardly.
Create and format interactive visualizations.
Skills Measured

May include: Select a visualization type; configure page layout and formatting; configure interactions between visual; configure duplicate pages; handle categories that have no data; configure default summarization and data category of columns; position, align, and sort visuals; enable and integrate R visuals; format measures; Use bookmarks and themes for reports

Revision Notes

Exam 70-778 Revision Notes: Create and Format Interactive Visualizations

Key Takeaways
  • Power BI delivers, out of the box, a range of different visualizations that cater towards most (if not all) reporting requirements. Should you find yourself in need of additional visualizations, then Microsoft AppSource is your go-to destination for finding visualizations developed by others. If you have experience working with either Node.js or R, then these can be used to build bespoke visualizations also.
  • When first developing a report, you should be able to match a requirement for a specific visualization type, to ensure that you are delivering a solution that is both meaningful and useful. From an exam perspective, this becomes a more critical consideration, and you should be prepared to suggest the most optimal visualization to use when given a specific scenario.
  • After adding visualization’s to your report, you have additional options available to customise them further. For example, you can specify a preferred sorting order for your data, override any summarizations used and move/align your visual on the report page.
  • By default, visualizations in Power BI are designed to change automatically, based on how users interact with the report. All of these options are controllable via the Edit interactions button, allowing you to specify your preferred cross-filtering and cross-highlighting conditions.
  • There is a range of report page customisation options available to developers. It is possible to resize a page to any possible height/width, allowing you to optimise your report for specific devices. Also, you can modify the colour of a page (or its wallpaper) or add an image instead. Pages can also be renamed, reordered or duplicated.
  • Measures can be formatted in the same way as calculated columns, meaning you can specify a data type or, for numerics, modify the number of decimal places.
  • Bookmarks allow developers to set up “checkpoints” within a report, based on how a report page has been filtered. These can then be used to automatically navigate the user through a report, applying these filtering steps automatically. This feature can help transform your report into an interactive story.
  • Visualizations will automatically inherit their various colour properties from the currently selected report theme. Although these can be modified granularly, the fastest and most consistent way of making these changes en masse is to change the Theme. Power BI includes some Themes out of the box, but you also have the option of building your own using a custom JSON file; this can then be imported into different reports, providing a portable means of enforcing a particular branding requirement.
Manage custom reporting solutions
Skills Measured
  • May include: Configure and access Microsoft Power BI Embedded; enable developers to create and edit reports through custom applications; enable developers to embed reports in applications; use the Power BI API to push data into a Power BI dataset; enable developers to create custom visuals
Revision Notes

Exam 70-778 Revision Notes: Managing Custom Reporting Solutions

Key Takeaways
  • Power BI Embedded is an Azure hosted offering that allows you add Power BI Report content into bespoke applications. This deployment option can be incredibly useful if you wish to make available your Power BI solution to users outside of your organisation or if you have an existing, bespoke application system that can benefit from utilising Power BI content. An Azure subscription is required to begin working with Power BI Embedded and you are billed based on node size, not individual user licenses. All Power BI content requires publishing to the online service before its contents become available for Power BI Embedded to access. Report developers will, therefore, need granting a Power BI Professional license to carry out these activities.
  • The Power BI API grants access to developers to perform automation or administrative actions programmatically against the Power BI Online service. Utilising a REST API, developers can determine the optimal programming language of choice to interact with the API, allowing them to streamline the deployment of Reports or Dashboards to the Power BI service or leverage additional functionality when utilising Power BI Embedded. The API can also cater to specific data load requirements, although more complex needs in this area would require addressing via alternate means (SSIS, Azure Data Factory etc.)
  • Developers can add their own bespoke visualizations to a Power BI Report by either developing them using Node.js or using the R language. The first of these options facilitate a more streamlined deployment mechanism and allows developers to add their visualizations to AppSource, whereas the second option may be more useful for complex visualization types with an analytical or statistical function.

Configure Dashboards, Reports and Apps in the Power BI Service

Access on-premises data
Skills Measured

May include: Connect to a data source by using a data gateway;publish reports to the Power BI service from Power BI Desktop; edit Power BI Service reports by using Power BI desktop

Revision Notes

Exam 70-778 Revision Notes: Report Publishing, On-Premise Gateway & Creating Dashboards

Key Takeaways
  • The Power BI On-Premise Gateway provides a streamlined route to working with non-cloud data sources within Power BI, Microsoft Flow and PowerApps. As a lightweight and easy-to-configure client application, it supports a wide variety of data sources, making them accessible as if they were in the cloud. Once set up, corresponding Data Sources are then made available for configuration and for leveraging as part of any Power BI Dataset.
  • Reports can be published into Power BI Online, meaning that they become accessible online and to a broader group of users, without requiring access to Power BI Desktop. Reports need deploying into a Workspace, which can be created manually or derived from an Office 365 Group. Each Report contains a corresponding Dataset, where all queries defined within Power BI Desktop exist.
  • Reports that already exist on Power BI Online can be updated by just publishing a new version of the Report from Power BI Desktop. It is also possible to modify Reports from directly within the browser and by downloading a copy of the .pbix Report file as well, which can then be altered and re-published.
Configure a dashboard
Skills Measured

May include: Add text and images; filter dashboards; dashboard settings; customize the URL and title; enable natural language queries

Revision Notes

Exam 70-778 Revision Notes: Report Publishing, On-Premise Gateway & Creating Dashboards

Key Takeaways
  • Dashboards provide a means of grouping together various content as tiles, designed for at-a-glance analysis and optimal end-user experience.
  • The list of content that can be pinned to a Dashboard includes:
    • Visualizations
    • Web content
    • Images
    • Text boxes
    • Videos
    • Custom streaming data
  • Pinned content can be re-arranged on Dashboard via drag and drop functionality. It is also possible to resize tiles to any given height/width.
  • Within the settings of a Dashboard, it is possible to enable/disable features such as natural language queries (Q&A’s) and Notes.
  • Some features of a Dashboard are only available if you have a Power BI Professional subscription, such as sharing and email subscriptions.

 

Publish and embed reports
Skills Measured

May include: Publish to web; publish to Microsoft SharePoint; publish reports to a Power BI Report Server

Revision Notes

Exam 70-778 Revision Notes: Publish and Embed Reports

Key Takeaways
  • The Publish to web option allows for non-licensed, external users to view a Power BI Report in its entirety. A URL and IFrame embed code can be generated for this at any time within the portal and then dropped into virtually any website. Although you will lose some functionality when deploying a Report out in this manner, you can expect that users will be able to perform most types of interactions with visualizations, Report pages and other components, as if they were accessing the Report through Power BI Online. In some cases, you may be unable to use the Publish to web option if your Report uses certain kinds of features, such as R Visuals or row-level security. You must also take into account any privacy or data protection concerns, as Reports deployed out in this manner will be publically accessible; where this is an issue, the Embed option is available as a secure alternative.
  • There are three steps involved if you wish to add a Report to SharePoint. First, you must generate the unique SharePoint embed URL within Power BI. Secondly, you then need to add on the dedicated control for this feature on your target SharePoint page and configure the relevant display options. Finally, you then need to ensure that all SharePoint users have been granted access to the Report, either at a Workspace level (recommended option) or by having the Report shared with them. By implication, in this scenario, all SharePoint users would have to have at least a Power BI Professional license to take full advantage of this functionality.
  • Publishing a Report to Power BI Report Server is mostly the same as if you were to do the same with the online version of the product. Instead of selecting a Workspace to add the Report to you, specify the name of the Report Server folder where the Report will reside. From a development standpoint, the dedicated Power BI Desktop for Power BI Report Server must be used and may differ in functionality from the “normal” version of the tool. There is also no option to edit a report from within Power BI Report Server like you can through the online version.
Configure security for dashboards, reports and apps.
Skills Measured

May include: Create a security group by using the Admin Portal; configure access to dashboards and app workspaces; configure the export and sharing setting of the tenant; configure Row-Level Security

Revision Notes

Exam 70-778 Revision Notes: Securing Power BI Dashboards, Reports and Apps

Key Takeaways
  • Workspaces act as a container for the various components that form a Power BI Reporting solution. Within a Workspace, you will find all of the Dashboards, Reports, Workbooks and Datasets that developers have published content to. Each User has a Workspace created for them in Power BI when they first access the service. Additional Workspaces can be added through Office 365 Groups or by installing a Power BI App from AppSource. Dashboards and Reports created within your a Users Workspace are shareable to other Users, provided that your account has a Power BI Professional license assigned to it.
  • To help manage permissions to Dashboards/Reports in a more efficient manner, Administrators can create Security Groups on the same Office 365 Tenant where Power BI Online resides. These can contain multiple groups of Users, allowing administrators to minimise the amount of effort involved in managing Dashboard/Report access. Most crucially, this will also enable Users that do not have an Exchange Online mailbox to access Dashboards/Reports when they are shared out in this manner.
  • Administrators have a whole host of options available to them within the Tenant settings area of the Admin Portal. These include, but are not limited to:
    • Export and Sharing Settings
    • Enable/Disable Content Sharing
    • Enable/Disable Publish To Web
    • Enable/Disable Export Reports as PowerPoint Presentations
    • Enable/Disable Print Dashboards and Reports
    • Content Pack and App Settings
    • Integration Settings
    • Custom Visuals Settings
    • R Visuals Settings
    • Audit and Usage Settings
    • Dashboard Settings
    • Developer Settings
  • All of these settings can be enabled for a specific security group, the entire organisation (excepting specific security groups) or allowed for particular security groups, excluding all others in the organisation.
  • Row-Level Security (RLS) allows report developers to restrict data, based on Roles. Row-level DAX evaluation formulas are used to achieve this, which filters the data that is returned, depending on a TRUE/FALSE logic test. To utilise the feature, you must define both the Roles and DAX formulas for each query within your data model. Then, after deploying your Report to Power BI Online, you then assign Users or Security Groups to the Role(s) created within Power BI Desktop. It is possible to view the effect of a Role at any time, within Power BI Desktop or Online, via the View As Role functionality. With the wide-array of DAX formulas available, including specific ones that return the details for the current user accessing a Report, it is possible to define very granular filtering within a Power BI report, to suit particular security or access models.
Configure apps and apps workspaces.
Skills Measured

May include: Create and configure an app workspace; publish an app; update a published app; package dashboards and reports as apps

Revision Notes

Exam 70-778 Revision Notes: Working with Apps and App Workspaces

Key Takeaways
  • Workspaces act as a container for the various components that form a Power BI Reporting solution. Within a Workspace, you will find all of the Dashboards, Reports, Workbooks and Datasets that developers have published content to. Each User has a Workspace created for them in Power BI when they first access the service. It is also possible to create additional Workspaces, either through the Power BI Online interface or by creating an Office 365 Group. A new experience for creating Workspaces is currently in preview which, once released, would negate the need for each Workspace to have an associated Office 365 Group.
  • When creating a Workspace, you can define various settings such as the type of access each user has (read-only or ability to modify its content), its members and whether it requires assignment to a Power BI Premium node. It is not possible to change the access type for a Workspace after creation, but you can freely change its name or modify its membership at any time.
  • The contents of a Workspace can be published as an App, enabling you to expose your solution to a broader audience within or outside your organisation. Once published, users navigate to the Power BI AppSource store for their tenant, which lists all Apps available for installation. Once installed, they will then become visible from within the Apps area of the application. You can update content within an App at any time by republishing its corresponding Workspace. It is also possible to define individual properties within an App, such as its description, access rights and landing page. To install and use Apps, the user in question must have a Power BI Professional license.

Additional Preperation Resources

The official Microsoft exam reference book is a helpful learning aid for this exam, particularly given that it includes numerous exercises that you can work through to familiarise yourself with different Power BI functionality. There is also an online course available on the edX website which, despite not covering the whole exam syllabus, does provide a useful visual aid and includes a lot of the features you are expected to know for the exam. Finally, nothing beats actually working with the product itself and trying out the various features yourself. Power BI Desktop is a free download and, with access to one of the sample databases provided by Microsoft, you can very quickly provision an environment on your own home computer to enable you to experience everything that Power BI has to offer.

Exams are always a nightmarish experience, both when preparing for them and when you are sat there in the test centre. I hope that this post, and this whole series, proves to be useful in helping with your exam preparation and getting you ready to pass the exam with flying colours 🙂

Welcome to the final post in my blog series concerning Microsoft Exam 70-778, where I hope to provide a revision tool for those planning to take the exam or a learning aid for those looking to increase their Power BI knowledge. Last weeks post discussed a range of topics about access and security when using Power BI as your Business Intelligence solution. We now move onto the final exam focus area – Configure apps and apps workspaces – which tests candidates against the following subjects:

Create and configure an app workspace; publish an app; update a published app; package dashboards and reports as apps

To follow through any of the examples described in this post, you should make sure that you have access to a Power BI Professional subscription. Free sixty day trials are available for this service if required.

Creating Workspaces

We provided a brief overview of the salient concepts behind a Workspace in last weeks post, chiefly from the perspective of the personal Workspaces that each user has access to from within Power BI Online. As mentioned as part of this discussion, additional workspaces can be created on top of this, to allow for a more logical grouping of Power BI content to become accessible within an organisation. There are a few ways to create a Workspace as part of the “current” experience (more on this later). Workspaces currently take advantage of Office 365 Groups in the background, so creating one of these within the Office 365 Admin Portal will automatically cause a Workspace with the same name to appear within Power BI Online. It is also possible to create all of this from within the Power BI Online interface, by using the Create app workspace button:

You have a couple of options that can be specified when creating a Workspace, as illustrated in the screenshot below:

  • To control how content within a Workspace can be interacted with, you can define whether its membership is Public – Anyone can see what’s inside or Private – Only approved members can see what’s inside. By “anyone”, this refers to any user account that has been provisioned on your Office 365/Azure Active Directory Tenant. You should ensure that the most appropriate option is chosen when the Workspace is created, as it cannot be changed once defined.
  • Permissions for all members of a Workspace are definable as either write access (Members can edit Power BI content) or read-only access  (Members can only view Power BI content) to anything created within a Workspace.
  • If you are fortunate enough to have access to dedicated Power BI Premium capacity, then you can also choose to assign your Workspace to this. Doing this will ensure that the Workspace can benefit from the increased CPU/Memory capacity within your available node(s).

Once created, a Workspace becomes accessible from within the Power BI Online interface and also as a choosable location to publish your .pbix files to from within Power BI Desktop:

It is also possible to add the following components, either from other Workspaces in Power BI or via upload, from within Power BI Online:

As alluded to earlier, the “current” Workspace experience is being overhauled currently, chiefly meaning that there will no longer be a need to create an Office 365 Group in the background. You can find out more about this new experience by reading the Microsoft Docs article on the very subject which, although still in preview, will eventually be something you need to be aware of for the exam.

Publishing Workspaces as an App

To distribute Power BI content to a broader audience, either internally or externally, you can publish all material that has been deployed out to a Workspace as an App. Taking this extra step can help you to simplify any ongoing management of a Workspace as, instead of having to grant access to individual users or Security Groups, you can alternatively create a self-service experience for users across the business, enabling them to install and interact with the Power BI content that is most relevant for their role.

You can create an App from within a Workspace by selecting the Publish app option at the top right of the window:

From here, you must define several different settings across the three tabs listed:

  • Details – Allows you to provide a description for the app and also set the Background color that displays to end users, based on a predefined list (i.e. no option to specify a unique hex colour value):
  • Content – Let’s you determine the default landing page presented to users when they first load the App. In most cases, it is best to choose a Dashboard, although it is possible to use a Report or have no landing page at all for the App (in this scenario, the user will see a list of all the content included within the App instead). It is also possible to review all of the content that will be published out within the App on this tab:
  • Access – Here, you can define who within the organisation can install the App – all users within your Office 365/Azure Active Directory tenant (Entire organization) or a specified list of Users or Security Groups (Specific individuals or group). There is no option to define more granular permissions (e.g. modify the contents within an App), which makes sense given the intended business requirement Apps serve. In most cases, you will probably leave the Entire organization option selected:

With all settings defined, you can then click the Finish button to get your App published out. Depending on the size of your datasets and Reports, this may take a while, as the below confirmation step advises:

After successfully publishing your App, you are presented with a URL link to it and also have the option to navigate straight to it so that you can verify its contents:

Apps, once installed, can also be accessed from the Apps button on the left-hand pane within Power BI Online:

From an end-users perspective, they would then interact with the newly published App by either:

  • Accessing and installing the app using the URL generated in the previous example.
  • Navigating to the My organization tab within AppSource and pressing the Get it now button under the corresponding App:

Apps can be updated with new or modified content at any time, without the need for users to install them again. All you would need to do is update the corresponding Workspace and then select the Update app button at the top left of the Workspace window:

The process of re-publishing the App is then identical to when you first publish it out.

ISV developers who have put together a range of Power BI content that integrates with their solution can also publish Apps to the public AppSource store, allowing any Power BI user the world over to install and utilise the content contained within. The process involved here involves several hoops and is not something you need to worry about for the exam.

Key Takeaways

  • Workspaces act as a container for the various components that form a Power BI Reporting solution. Within a Workspace, you will find all of the Dashboards, Reports, Workbooks and Datasets that developers have published content to. Each User has a Workspace created for them in Power BI when they first access the service. It is also possible to create additional Workspaces, either through the Power BI Online interface or by creating an Office 365 Group. A new experience for creating Workspaces is currently in preview which, once released, would negate the need for each Workspace to have an associated Office 365 Group.
  • When creating a Workspace, you can define various settings such as the type of access each user has (read-only or ability to modify its content), its members and whether it requires assignment to a Power BI Premium node. It is not possible to change the access type for a Workspace after creation, but you can freely change its name or modify its membership at any time.
  • The contents of a Workspace can be published as an App, enabling you to expose your solution to a broader audience within or outside your organisation. Once published, users navigate to the Power BI AppSource store for their tenant, which lists all Apps available for installation. Once installed, they will then become visible from within the Apps area of the application. You can update content within an App at any time by republishing its corresponding Workspace. It is also possible to define individual properties within an App, such as its description, access rights and landing page. To install and use Apps, the user in question must have a Power BI Professional license.

We’ve come to the end of the series (at last!) and covered a LOT of content relating to Power BI. I’ll be doing a roundup post next week, where I’ll try and group together all of the posts into the single location, alongside some general exam preperation tips. If you’ve been following the series, thank you for reading and I hope that it has proved to be a useful learning tool 🙂

Office 365 groups have been a recurring topic of the blog in recent months – we’ve seen how we can force Office 365 to use custom domains when creating groups for the very first time and how you can straightforwardly integrate an Office 365 Group within Dynamics 365 for Customer Engagement. With this in mind, there is little point in providing a detailed description of what they are and how they can be used; suffice to say, if you are wanting to collaborate closely with internal/external colleagues for a particular project or department, Office 365 Groups are an excellent candidate to consider.

One of the cornerstones of Office 365 Groups is the ability for all conversations to be tracked via the use of a dedicated shared mailbox. This perhaps explains why the Office 365 portal will refuse to let you add any user within your organisation who does not have an Exchange Online license assigned to them. Case in point – let’s assume we have a user account with no such license assigned to them on the Office 365 portal:

When attempting to add this user into an Office 365 group, we get a message to let us know No match was found for the user account entered and, as a consequence, it cannot be added to the group:

From this, you can perhaps make the assumption that Office 365 groups are not supported at all for users who do not have a mailbox. This is notwithstanding the fact there are several different business scenarios that may necessitate this requirement:

  • A kiosk/”light-use” account may require access to the group to upload documents and manage the SharePoint site.
  • Integration with external applications may be required, stipulating the need for a service account to authenticate with the group to retrieve/add content dynamically.
  • The need to configure an account for external users to access, that is sufficiently locked down and inexpensive to maintain.

Fortunately, as with many other things relating to Office 365, we can get around this limitation within the Office 365 portal by resorting to PowerShell and adding the John Doe user account above to the Group.

The first step towards achieving this is to boot up a PowerShell window. Make sure you have access to this on your machine of choice then, after opening the application using the Run as administrator option, execute the following script:

##Set Execution Policy to Remote Signed - required to fully execute script

Set-ExecutionPolicy RemoteSigned

##Connect to Exchange Online. Enter administrator details when prompted.

$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $Session

##Add the non-mailbox user to the Office 365 Group. Substitute the Links value with the username of the account to add.

Add-UnifiedGroupLinks -Identity "Test Office 365 Group" -LinkType Members -Links john.doe@domain.com

##Confirm that the user has been added successfully by returning the Group member list

Get-UnifiedGroupLinks -Identity "Test Office 365 Group" -LinkType Members

##Cleanup by disconnecting from Exchange Online

Remove-PSSession $Session

The penultimate command will make something similar to the below appear in the console window. Interestingly, note that the John.Doe test user has a RecipientType value of User:

Now that the user has been added successfully, they will be able to access the SharePoint site for the group by navigating to the SharePoint library URL. This will look similar to the below and can be grabbed by logging in as another user who has the RecipientType value of UserMailbox and navigating to the Groups SharePoint site:

https://<Your On Microsoft domain prefix>.sharepoint.com/sites/<Your Office 365 Group Name/

Note that this will be on the only way the non-mailbox user can access the site. For example, there will be no link to SharePoint within Office 365 to guide you to the above location. After logging in, you should be greeted with a window similar to the one below:

The John Doe “light-use” account, as referenced above, will have full access to everything that is accessible within SharePoint concerning the Office 365 Group, such as:

  • The Home/News Page
  • Shared Documents Folder (“Documents“)
  • Shared OneNote (“Notebook“)
  • All Site Pages
  • Planner (navigated to via the following link: https://tasks.office.com/<Your Office 365 Primary domain>/en-GB/Home/Planner/)

Conversely, the following features will be inaccessible (due to requiring a Mailbox):

  • Conversations
  • Shared Calendar

If for example, you attempt to navigate to Conversations within SharePoint, you will get the following error message:

This is, perhaps, a small price to pay for what ends up to be a pretty feature-rich experience that can be given to additional users within your organisation at virtually no cost. Perhaps another good excuse to start rolling out Office 365 Groups across your tenant in the near future 🙂

With the dizzying array of cloud-hosted applications and database systems available to IT system administrators today (often deployable at a few button clicks), you may be forgiven for thinking that Microsoft Access has joined the ranks of InfoPath, Visual FoxPro and other semi-legendary deprecated applications. Far from it – Access is still a mainstay within most Office 365 subscriptions today, alongside Word, Excel etc. What’s more, if you are looking to develop a very simple application to be utilised within your organisation, you would be hard-pressed to find an equivalent product at the same price point that would do the job as well. Here are just a few reasons on why Access is great.

  • It has the ability to connect to a wide variety of data sources – SQL, SharePoint and others – as well as letting you store data within Access itself.
  • The application contains rich customisation options for forms, buttons and other controls, enabling you to tailor the interface to suit a wide variety of business requirements.
  • Access has full support for Visual Basic for Applications (VBA), giving you the further potential to integrate complex logic when building your Access application.

Although there was some concerning news recently regarding Access within SharePoint Online, that looked like the writing on the wall for Access, quite the opposite effect seems to be happening. The application is being continually updated within Microsoft, with one of the latest of these updates catching my attention:

Last November, we shared our plan to add a set of modern data connectors that will enable Office ProPlus customers to expand what is possible in their organizations.

Today, we are pleased to announce the addition of two new connectors in our portfolio: Microsoft Dynamics and Salesforce. These two connectors are rolling out to customers with Office 365 ProPlus, E3, or E5 plans.

To clarify, Microsoft Dynamics in this context refers to the Dynamics 365 for Customer Engagement (Dynamics CRM) application, and not any other product within the newly revamped Dynamics 365 family. Getting started with the new data connectors is fairly easy, and may be useful for your business to look at more closely. With this in mind, let’s take a look at how to setup a straightforward Access application that links with Dynamics 365.

Before we begin…

At the time of writing, the new data connectors are only available for those who on the First Release branch of Office ProPlus, something which has to be explicitly enabled on the Office 365 Admin centre. As part of this, you may need to reinstall your Office applications (like I did) to ensure that this kicks in correctly.

Once you’ve verified that you’re on the correct Access version, you can then proceed to create your Dynamics 365 “powered” Access app…

Open up Access and create a brand new Blank database app called Contact Management.accdb, saving it in a location of your choosing:

A blank Access app will be created with an empty Table (this can be safely deleted). Navigate to the External Data tab on the Ribbon and select the From Dynamics 365 (onlineexternal data source:

A pop-up will appear, asking for your Dynamics 365 application URL and how you want to store the data within Access – take a one-time copy of it (Import the source data into a new table in the current database) or connect directly (Link to the data source by creating a linked table). Select the 2nd option, which I would always recommend to select:

Access will then attempt to retrieve a list of the Entities within the application. You may also be asked to log in using your Office 365 credentials, but based on my testing, it seemed to automatically pick these up from my currently logged in Office 365 account for activation – which is nice 🙂

The Link Tables window will then return a list of the entities that you are able to select. In this example, select Contacts and then press OK.

Access will then begin importing the table definitions and the underlying data, which can take a few moments. It is worth noting that Access will also automatically import tables for each N:1 entity relationship for your chosen Entity. This is to allow you to effectively query for “friendly name” information, as opposed to returning the rather ugly looking Globally Unique Identifier (GUID) values for each relationship:

With our data successfully imported, we can then start to build out a Form to enable users to interact with and change records. The quickest way of doing this is via the Form Wizard, which can be found on the Create tab:

By following the Wizard’s instructions. we can select the fields we want on our new form…

…our preferred layout…

…and then, finally, our form name:

Clicking on Finish will load up our newly created Form. From there, we can run a test by adding a Mobile Number value to the Marissa Burnett sample Contact record and then verify this appears successfully on Dynamics 365 after saving:

Conclusions or Wot I Think

The role of Access within the wider context of Microsoft’s offerings is one that has been increasingly open to question in recent years. The debut of exciting new solutions such as PowerApps and Dynamics 365 for Business, does make you start to think whether Access will be for the “chop” in the near future. By adding the new Dynamics 365 for Customer Engagement and Salesforce connectors, along with the other updates that are continually being rolled out for the application, Microsoft makes it clear that for, the time-being, Access is very much here to stay. The reasons for this can be perhaps garnered from my opening comments – it still remains a very versatile way of building quick to deploy, database vendor agnostic solutions that are tailored for desktop use within businesses of any size globally. Another reason for its mainstay – and the release of these new connectors – could be seen as a stealthy means of getting organisations slowly moved across to solutions like Dynamics 365, without the need of moving everything across in one go.

Whatever the reason(s), we can be encouraged by the fact that Access is very much being actively developed, even within the current landscape of varying CRM/ERP solutions. And, what’s more, it’s very cool to be able to say that Dynamics 365 for Customer Engagement is Access compatible 🙂

We saw previously on the blog some of the great features that you have at your disposal when using Office 365 Groups in conjunction with Dynamics CRM/Dynamics 365 for Enterprise. When deployed prudently, they can massively enhance what is traditionally possible via distribution groups and give internal/external users a better way to collaborate with content, as opposed to a mountain of email attachments. As I cautiously hinted towards in the above post, some thought should go into how you go about rolling out Office 365 Groups across your organisation, which should inevitably include internal testing. By doing this, you can highlight a particular functionality quirk that you will more than likely need to address before issuing a general rollout.

When you first go to create an Office 365 Group, you will notice that the domain address of the newly created group mailbox has defaulted to the onmicrosoft.com domain for your Office 365 tenant, instead of any bespoke domain you may have setup on your tenant:

You would think that this is caused by the fact that the Default Domain for the tenant is still set to the onmicrosoft.com domain, as we can see below:

However, after changing this accordingly, we are still forced to create a Group email address that utilises the onmicrosoft.com domain:

Rather frustrating! Fortunately, there is a way in which we can get around the issue, which involves two steps if you have existing Office 365 Groups that need renaming:

  1. Modify the Email Address Policy for your tenant via PowerShell to force new Groups to use your desired domain.
  2. Update the SMTP address of all existing groups via PowerShell.

Both of these steps will now be demonstrated, but be sure to have PowerShell installed on your machine first before you begin.

Connecting to your Office 365 tenant

Start your PowerShell client as an Administrator (Run as Administrator option on Windows) and execute the following command first to ensure all subsequent scripts run correctly:

Set-ExecutionPolicy RemoteSigned

You should receive a prompt similar to the below:

Press to proceed

Now we are ready to connect to Exchange Online, using the following cmdlets:

$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session

You’ll be prompted to enter credentials to authenticate:

Make sure the credentials used have the relevant privileges on Office 365 and then hit OK. After a few moments, you should see a window similar to the below, which indicates that you have successfully connected and have all the Exchange Online cmdlets at your disposal:

Set Default Email Policy for Office 365 Groups

Modifying Office 365 to use a different domain when creating Office 365 Groups requires invocation of the New-EmailAddressPolicy cmdlet, a broad brush command that is useful for a number of different Exchange management scenarios. The command can be specifically tailored to create a policy applicable to Office 365 Groups, basically telling your tenant which SMTP domain to use when creating new Groups:

New-EmailAddressPolicy -Name Groups -IncludeUnifiedGroupRecipients -EnabledEmailAddressTemplates "SMTP:@mydomain.com" -Priority 1

You can tell if the command has executed successfully if you see something similar to the below in your PowerShell window:

Next, we can then verify that Office 365 has detected the change by creating a new Group and verifying that the Email address value reflects our desired domain:

Rename Existing Office 365 Group

Assuming you are just starting out with Office 365 Groups, the simplest way of renaming your existing groups would be to recreate them. This may not work if they are already being utilised, given the level of effort involved in migrating existing content across to a new group. In this scenario, there is an additional cmdlet we can rely upon to change the SMTP address of our group to our desired domain:

Set-UnifiedGroup -Identity "Test Office 365 Group" -PrimarySmtpAddress test@mydomain.com

PowerShell will only return a message in case of an error, so to verify our changes have taken place, we must again return to Office 365 to confirm that the new SMTP address is in place:

With this now updated, email messages should flow successfully to the new Group SMTP address.

Conclusions or Wot I Think

It is rather strange that there are no obvious means of specifying which default domain should be used with newly created Office 365 groups, nor at the fact that there is any way for the user to override their choice so they can select a domain that is available on the tenant. As discussed at the start of this post, this situation provides an excellent argument for ensuring that proper processes are followed for the introduction of all new technologies within the business. Whilst exciting and innovative solutions should always be duly considered and not hampered from being rolled out, it is crucial that an appropriate amount of time is allocated for thorough testing. The last thing you want to do, in reference to this situation, is to cause irritation to end users by not giving them the ability to present a professional and correct domain name for their Office 365 Groups; by running through some test scenarios and involving end users as part of this process, where possible, you can help to prevent issues resulting from a roll out and (hopefully!) enthuse colleagues within your organisation at the new piece of technology that is being introduced.