Welcome to post number 6 in my series concerning Microsoft Exam 70-778. The series aims to provide a revision tool for those who are looking at taking the exam and to also provide an introduction into some of the fundamental concepts around Power BI. As alluded to previously on the blog, Power BI is increasingly a topic that Dynamics 365 Customer Engagement professionals need to grapple with, particularly if they wish to implement a reporting solution that helps to ensure the long-term success of their CRM deployments. Going for Exam 70-778 gives you an ideal opportunity to familiarise yourself with this exciting technology area.

We moved into the Modeling and Visualizing Data theme for the first time last week, and we now jump into a relatively big subject this week – Create calculated columns, calculated tables, and measures. The skill areas covered here are as follows:

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

To follow on as part of the examples that follow, make sure you have downloaded the WideWorldImporters sample database and hooked up Power BI Desktop to the tables described in my first post in this series.

Now, to begin this week’s post, we must first ask ourselves an essential question:

What is DAX?

Data Analysis Expressions, or DAX, has its roots in SQL Server Analysis Services (SSAS). First released as part of the PowerPivot for Excel 2010 Add-in, DAX has quickly become the preferred language to use when working with Excel PowerPivot, SSAS and – most importantly – Power BI. DAX bears a lot of similarity to standard Excel functions, but a critical differentiator is that DAX does not operate in the context of cells and is considered a strongly typed language (i.e. can lead to a higher risk of errors at runtime). A benefit of using DAX over PowerQuery/M is that the language handles a lot of data conversions implicitly. It also supports the usual list of data types associated with SQL type databases – Decimal/Whole Numbers, DateTime, Text, Boolean etc. A successful Power BI Reporting solution typically relies heavily on the building of complex DAX formulas, which are then complemented by the most appropriate visualisation at the report level.

Context in DAX

An important concept to grapple with DAX is that of context, and how this applies to the various formulas that you build out. There are two types of context:

  • Row Context: Essentially meaning the “current row”, functions that operate in this manner will be processed for every single row on your dataset, outputting the result required each time. For example, the IF function works on a row context.
  • Filter Context: Functions that support this will take into account any filters defined at the report level when performing the relevant calculation. Consequently, a lot of the work involved in contextually updating visuals is handled automatically by your DAX formulas, with no need to configure specific Measures/calculated columns as a workaround.

It is essential to understand these two types of context and on how they can impact on each other (the “context transition“). More critically, it’s also crucial to be aware of when a DAX function ignores a context completely. For example, some aggregation functions completely ignore row context. This article on the SQL BI website is a great resource that discusses this whole topic in depth.

Utilising DAX in Power BI

The Modeling tab is your go-to area when working with the three types of DAX calculations within Power BI Desktop – MeasuresCalculated Columns and Calculated Tables:

The sections that follow will discuss each of these in further detail, but there are some useful points to highlight that apply when using DAX generally:

  • Unlike the M Query Editor, there is full Intellisense support provided for DAX within Power BI. Straightaway, this makes this a much more streamlined tool that should be gravitated towards automatically as a consequence.
  • You should always use fully-qualified names that reference both the table object and field name that you are attempting to access. An example of how this should look can be seen below with the custom column EditedByDay on the WideWorldImporters Sales.OrderLines table:
  • It is also possible to add comments to your DAX code, with three flavours on offer; this is the part where we distinguish between the C# and SQL Server developers 🙂 :
    • Single Line Comments: // or
    • Multi-Line Comments: /* and */
  • The screenshot below indicates how to utilise these as part of a DAX calculated column:

Let’s take a look now at the specific areas referenced above in greater detail:


Measures are best thought of as being fixed (or scalar) values, typically aggregations based on simple or more complex underlying formulas. A straightforward example could be just a Count of all rows on a table e.g

  • SalesOrderLinesCount = COUNTROWS(‘Sales OrderLines’)

Although Measures are compatible with any Visualization, the most common place to find them will be in a Card, as indicated below:

When first working with Measures, an advantageous feature at our disposal is the New Quick Measure option, which provides a softer, GUI-focused approach to creating common types of Measure:

Beyond this, the sky is the limit when working with DAX Measures, with a whole range of different functions that can be used in isolation or tandem with each other.

Calculated Columns

We saw how it is possible to perform simplistic custom/calculated columns using the Power Query Editor as part of an earlier blog post. The functionality offered here can prove useful for straightforward requirements – for example, if Column1 equals “AAA”, then TRUE, else FALSE – but may soon become insufficient if you have more advanced needs for your new column. In this scenario, DAX calculated columns could come to the rescue and give you the ability to more familiarly define your business logic, particularly for those with their Excel head on.

A key consideration when working with DAX Calculated Columns is understanding the impact they have on performance; specifically, that they consume RAM and disk space within your report. You should be aware also of the concept of circular dependencies. It is possible to build highly complex formulas within DAX, that may reference other DAX columns that you have built out. As a consequence, attempting to modify any dependent DAX formulas could result in errors. In addition to this, trying to define multiple calculated columns that perform the same operation may also lead to circular dependencies. There is another excellent article on the SQL BI website that jumps into this whole subject area in greater detail and is worth a read.

Similar to Measures, pretty much every type of DAX function listed here (minus ones that return table values) is useful in some way when working with Calculated Columns.

Calculated Tables

The types of DAX functions discussed so far focus on returning a single value – either in the sense of a fixed, often aggregated, value as part of a Measure or a single value on a row-set with a Calculated Column. The next step up from that, and where DAX enters a whole new realm of usefulness, is the ability to define Calculated Tables based on DAX formulas. There are a LOT of options at your disposal here. As well as being able to derive Calculated Tables directly from other objects within your model (e.g. the DAX formula SalesOrderLinesDupe = ‘Sales OrderLines’ would create a copy of the Sales OrderLines table), there is also a wide array of different functions available that, when used individually or in combination, can satisfy any particular requirement:

  • The FILTER function returns a new table object filtered from another, based on a specific value. The CALCULATETABLE function is the “big brother” of this particular function, with the ability to define multiple, potentially complex filter conditions.
    • e.g. The following formula will return all Sales OrderLine records with a PickedQuantity value greater than 7.
  • The ALL function returns all data from a table with underlying filters removed (i.e. filters that are defined using the options indicated in the screenshot below. It can be used to specify a whole table or single/multiple column(s). There are also variants of this, such as ALLEXCEPT (which will return all columns except the ones listed) and ALLNOBLANKROW (which removes any blank rows before returning the data).
  • VALUES and DISTINCT return a list of distinct rows only. VALUES can only work with physical tables, whereas DISTINCT also works with table expressions.
    • e.g. Both of the DAX functions below do the same thing – return 227 distinct Description values from the Sales OrderLines table:
  • SUMMARIZE groups a table by one or more columns, adding new ones where necessary. The grouping is optional. Only rows that contain data will return.
    • e.g. The follow DAX formula will return the total UnitPrice for each Sales OrderLines Description:
  • ADDCOLUMNS returns an existing table with new columns defined, typically as DAX formulas. SELECTCOLUMNS behaves similarly, but you have to specify the columns to return, including any new ones.
    • e.g. SalesOrderLinesWithCalcColumn = ADDCOLUMNS(‘Sales OrderLines’, “IsEndOfMonth”, IF(DAY(‘Sales OrderLines'[LastEditedBy]) >= 25, TRUE(), FALSE())) returns the Sales OrderLines table, with the example IsEndOfMonth column included as a new column based on a DAX formula.
  • TOPN performs ranking based on conditions and returns the specified number of records based on the ranking criteria. Ties can also occur, meaning that the specified ranking number may not correlate to the actual number of rows returned.
    • e.g. The following formula will return the Top 25 Sales OrderLines records by UnitPrice. Notice the number of records returned exceeds 25; this is because of multiple ties within the underlying data.
  • There is a range of functions available to accommodate common table-join scenarios. CROSSJOIN allows for Cartesian product joins from multiple tables; NATURALINNERJOIN & NATURALLEFTOUTERJOIN perform self-explanatory joins, which seasoned SQL Server developers should have no trouble in guessing; and GENERATE & GENERATEALL allow for more bespoke table joins to take place.
    • e.g. the following formula will perform an inner join of the Sales Orders and Sales OrderLines table and return the ID values from both as a new table object
  • GENERATESERIES creates a single table with a list of numerical values. It requires the start & end number and the (optional) increment value.
    • e.g. SeriesExample = GENERATESERIES(1, 150, 1) will generate a single column table object with 150 rows, numbered 1 to 150.
  • CALENDAR generates a list of date/time values in a single table column object, based on the range specified. CALENDARAUTO behaves the same but creates a list of relevant date values after evaluating the whole model (e.g. if there is another table with dates between January 1st and November 31st 2018, then this would be the list of dates generated).
    • e.g. Using CalendarAuto with the WideWorldImporters Sales Orders and Sales OrderLines tables will create date values from the 1st January 2013 through to the 31st November 2016.
  • ROW generates a single row table with the column values specified, based on a key/value pairing.
    • e.g. RowExample = ROW(“My DAX”, “Brings all the nerds to the yard”, “And they’re like”, “Do you wanna write Measures”, “Damn right, I want to write Measures”, “I can teach you, but you must learn M first”) produces the following table:
  • UNION combines two or more tables vertically, retaining duplicates. Column names do not need to match, but the number of columns does. INTERSECT is similar to UNION but maintains only values that exist the same in both tables. Nested UNIONS are usable alongside this, but the specified order can impact the result. EXCEPT is similar to UNION and INTERSECT, but outputs rows that exist only in the first table, not the second one.
  • DATATABLE allows the user to specify tables that contain manually entered data, similar to the Enter Data feature:
    • e.g.:

What If Parameters

There may be times within Power BI where you need to provide some predictive modelling capability. For example, show how potential sales will look based on an increased margin of 25%. Or it could be, as developers, we wish to test the functionality of some of our DAX formulas by having the ability to see how potential ranges of values will affect our calculations. For either scenario, What If Parameters can provide a solution. By default, on creation, a slicer control is provided that can be placed anywhere on your Report. To create a What If Parameter:

  1. Within Power BI Desktop, on the Modeling tab, select the New Parameter option:
  2. On the What-if parameter dialog box, enter the details as indicated below and press OK:
  1. This will then add the following components to your report:
    • A Slicer control called TestWhatIfParameter
    • A new Calculated Table called TestWhatIfParameter:
  2. Under the Visualizations tab, select the Gauge visualization to add it your report. Define the properties for the Gauge visual as follows:
  1. Now, when you adjust the value in the TestWhatIfParameter, the Gauge will update accordingly. For example, setting it to 75 will update the report as follows:

This simple example does not do justice to the potential that this feature has, so I would recommend exploring it further yourself.

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.

DAX is a subject so vast that there are entire books devoted to it. Therefore, from an exam perspective, don’t worry too much about becoming a DAX master. In next weeks post, we’ll hopefully go down a few gears as we take a look at how to work with KPIs and how to apply them to visualizations.

Another week, another Power BI post 🙂 This is the fifth post in my series focusing on Microsoft Exam 70-778, where I aim to provide a detailed overview and description of the skills needed to tackle the exam successfully. Last week’s post rounded off the opening theme, Consuming and Transforming Data By Using Power BI Desktop, as we focused on the options available to us to help tidy up data within the Power Query Editor. We now start to move outside of this area of the application with the Modelling and Visualising Data theme and, specifically, the following skill area:

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

As before, to follow on as part of any examples, make sure you have downloaded the WideWorldImporters sample database and hooked up Power BI Desktop to the tables described in my first post in this series.


It will be a rare occurrence if you find yourself regularly working with a single table object within Power BI. The required data for most reporting situations will generally reside in different locations – whether within separate tables in a single SQL database or across different instances or applications. In most cases, there will be some field – or key – that will link records together and act as the bedrock when defining Power BI Relationships.

For those who are coming from a SQL or Dynamics 365 Customer Engagement background, I would expect that the concept of Relationships will present little difficulty in grasping. But, for those who are coming in cold, it is worth considering the following question – Why might you want to look at defining Relationships in the first place? The reasons will generally be specific to your business need, but can be generalised as follows:

  • Database normalization creates a degree of complexity that can be difficult to unweave from a reporting standpoint. Customer records may exist in one table, Address details in another, their orders in a third…you get the picture. Consequently, there is a need to bring this all together into a single/simplified view when developing reporting solutions; Relationships help to meet this objective.
  • The great benefit of using Power BI is its ability to provide a consistent, contextual filtering experience across Reports. So, for example, when adjusting the range on a Date slicer, all other visualisations are automatically refreshed to only show data from the updated period. This wizardry is all achieved by Relationships and the cross-filtering rules you define for them.
  • Power BI data models can grow in complexity over time. The defining of Relationships simplifies this to a degree and allows you to view all of your queries in a clean, visual manner.

We can see an example of how a Power BI Relationship looks in the picture below:

We can see above two one-to-many (1:N) Relationship between the Sales.Customers and Sales.Invoices tables from the WideWorldImporters database. The matching key, in this case, is the CustomerID field.

Relationships are created and managed in one of two ways:

  • Automatically when bringing data into Power BI Desktop the first time. Power BI will automatically detect and build any Relationships within a SQL Server-derived data source, based on any PRIMARY KEY/FOREIGN KEY CONSTRAINTs that exist already. The Select Related Tables option can be used to intelligently determine which tables can be brought in on this basis.
  • By going to the Manage Relationships button on the Home tab within the main Power BI window (NOT the Power Query Editor). This option will allow you to manage and create new Relationships based on your requirements. An Autodetect… option is also available, which behaves similarly to the data source auto-detection discussed previously. The example below shows the WideWorldImporters Relationships referenced earlier:

The walkthrough exercise at the end of this post will go through the process of creating a Relationship from start to finish, but it is useful at this stage to dive into the more detailed concepts involving Relationships:

  • The following types of Relationships (or Cardinality) can be specified:
    • One to Many (1:N) / Many to One (N:1)
    • One to One (1:1)
    • Many to Many (N:N) – This feature is currently in Preview and caution is advised when using it.
  • Each Relationship requires that a Cross filter direction is specified, which will determine how filters cascade out to related tables. These are configurable as either Single (in the direction of your choosing – left to right or right to left) or Both. 1:N Relationships must always use Both.
  • Only one Relationship is classifiable as Active between two tables at any given time. Despite this limitation, it is possible to define multiple Relationships, if required. It is essential to understand with this that only Active Relationships are utilisable when performing DAX column/measure calculation, which may lead to unintended results. You can get around this by either changing the Inactive Relationship to Active or by taking advantage the USERELATIONSHIP function to force DAX to pick your preferred Relationship.
  • The Assume Referential integrity option is only available when you are using DirectQuery and, if enabled, underlying queries will attempt to use INNER JOINs as opposed to OUTER JOINs in any query folding. While this can speed things up, it may also lead to missing or incomplete results.

Formatting Columns

We saw in last week’s post some of the more functional data formatting options that the Power Query Editor provides. I say the word functional in the sense of meaning formatting that serves a specific, data quality need, as opposed to a styling requirement. Moving outside of the Power Query Editor and we can discover a whole host of additional styling formatting options on the Modelling tab:

Let’s break all this down into a bit more detail:


  • Data Type: Here, it is possible to override the data type for a particular column value. The options available are:
    • Decimal Number
    • Fixed decimal number
    • Whole Number
    • Date/Time
    • Date
    • Time
    • Text
    • True/False
    • Binary
  • Format: Depending on the Data Type value selected, the list of choices here will change accordingly. In most cases, such as for Text values, you will only have a single selection available, but your options for Date/Time and Currency values are considerably richer. For example:
    • Date/Time values can be formatted in numerous different ways, both from a styling and locale perspective. This includes formatting dates in the good ol’ British way, meaning that the ISO 8601 date value 2018-01-01 can appear as 01-01-2018, 1 January 2018 or even Monday, 1 January 2018.
    • Currency values are modifiable to any global currency sign or shortcode. So, the value 50 is displayable as both £50 and 50 GBP for British Pound currency figures.
  • $ Button: This is, in effect, an alternate way of changing the Currency value on a field, as described previously.
  • % Button: Converts a value to a percentage. For example, the Whole Number value of 75 would appear as 75%.
  • ‘ Button: Adds a thousand separator to a number value. Therefore, 100000 becomes 100,000.
  • .0/.00 Button: Adjusts the number of decimal places at the end of a numeric, up to a maximum limit of 15.


  • Data Category: This option, designed primarily for String values, allows you to indicate the type of data the field represents, with a specific focus towards mapping capabilities. For example, it is possible to mark a field as State or Province, to ensure that Power BI does not make an incorrect assumption when attempting to plot an address to a map. Some additional options here include:
    • Latitude/Longitude to, again, ensure accurate plotting to a map visual.
    • Classifying a field as containing either a Web URL or Image URL.
    • Categorising a field as Barcode value.
  • Default Summarization: When building out a report and adding fields to visualizations, Power BI assumes the preferred aggregation value to use. This will be pretty spot on in most cases – for example, Currency fields will default to a Sum aggregation. However, it is possible to override this option to use any of the following, easily guessable, alternatives:
    • Don’t summarize.
    • Sum
    • Average
    • Minimum
    • Maximum
    • Count
    • Count (Distinct)

Sort By Column

There may be a requirement to perform sorting of column values, based on some predefined logic as opposed to merely alphabetical or numeric order. The best example to provide here would be for Month Names. Instead of sorting in alphabetical order, like so:






We would want to sort by logical Month order i.e.:






Another scenario could be column sorting based on High, Medium and Low categorisation; all records with a value of High would need to appear first and the Medium second, as opposed to Low if sorted alphabetically.

Sort By Column gives us the flexibility to achieve these requirements, but it is crucial first to ensure that a proper sorting column resides within your query. Going into the Power Query Editor and modifying the Sales CustomerTransactions table enables us to add on a Month Number column for the TransactionDate field by going to the Add Column tab and selecting Date -> Month -> Month option:

Coming out of the Power Query Editor using the Close & Apply option, go into the Sales CustomerTransactions table within the Data tab and select the Sort By Column option, selecting the MonthNumber field as the Sort by Column:

We should now see when adding a simple Table visualization to a report, that the Month values derived from TransactionDate appear in correct sort order:

Through this example, you can hopefully see how straightforward it is to accommodate more unique sorting requirements with minimal effort.

Manual Data Entry

Although Power BI assumes, for the most part, no requirement to define any manual data as part of your reports, there may be situations where this need arises. For example, on the WideWorldImporters Sales.CustomerTransactions table that has been built out through this series, you may want to define a manual list of all FullName values that can then be linked up to a slicer for filtering purposes. There are two ways this requirement can be met, with both options having their benefits and potential preference, based on how much you like to code 🙂 :

  • The Enter Data button on the Power Bi Desktop Home tab allows you to create a new Table object based on manual data entry. The table can have as many columns as you like and, what’s nifty is that it supports full copy + paste functionality from other applications. It is a VERY convenient feature to bear in mind:
  • Using PowerQuery, it is also possible to define custom Tables populated with data. We saw an example of how to achieve this in last weeks post when creating some example data for cleansing, but there are some further options available to us:
    • Simple lists of data can appear within brackets. For example, the following M code:
      • let
            Source = { "This", "Is", "A", "Test"}
    • Would generate the following data within Power Query:
    • Records with multiple rows/fields can also be generated using the example code below:
      • Table.FromRecords({[Did = "This", You = "is", Know = "Testing"]})
    • Which would appear as follows:
    • Finally, as shown last week, it is possible to create a Table object, using the example code below:
      • #table(
            {"Forename", "Surname", "Description"},
                {"JANE","smith","   this describes the record"},
                {"alan", "JOHNSON", "    record description detected    "},
                {"   MARK", "CORRIGAN    ","another description"},
                {"JANE","smith","   this describes the record"}

The tools available here help to satisfy many potential ad-hoc requirements when using Power BI Desktop and when used in conjunction with other features described in this post and earlier in the series, start to become pretty powerful.

Example: Creating Relationships

In this very straightforward example, we will see how it is possible to create a Relationship manually from within Power BI Desktop, using the WideWorldImporters sample database as our connection source:

  1. It will be necessary to disable to automatic creation of Relationships when importing data for this exercise. This is done by going to the Options -> Data Load screen and ensuring that the appropriate option has been unticked:

  1. Follow the instructions discussed in this post to connect to the WideWorldImporters database but, on this occasion, ensure that the following tables only are selected for import:

  1. Once the data loads into Power Bi, navigate to the Relationships tab and you should see the two table objects indicated below (you may need to resize them accordingly for all fields to display):
  2. Because of the setting change carried out in step 1, the underlying Relationship between both of these tables in SQL has not been detected and added. To fix this, click on the Manage Relationships button on the Home tab and then the New button to open the Create relationship window, as indicated below:

  1. Define the following settings for the new Relationship, as described and illustrated below:
    • In the first drop-down box, select Sales Orders and verify that the OrderID column highlights itself.
    • In the second drop-down box, select Sales OrderLines and verify that the OrderID column highlights itself.
    • For Cardinality, ensure One to many (1:*) is selected.
    • For Cross filter direction, ensure Both is selected.
    • Ensure Make this relationship active is ticked.

  1. Press OK and then Done to create the Relationship. The Relationship window should refresh accordingly to indicate that a new Relationship exists between the two tables:

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.

Hopefully, by now, you are starting to get a good feel for how Power BI works and also the expected focus areas for the exam. Next weeks post is going to be a biggie, as we jump head first into DAX formulas and how they can be used for calculated columns and Measures. We’ll also introduce the concept of What-if Parameters and how they work in practice.

Welcome to the third post in my series on Microsoft Exam 70-778, where I aim to provide a detailed overview and description of the skills needed to tackle the exam successfully. We saw in the previous post how we could use the Power Query Editor to perform a multitude of different transformations against data sources; this will now be taken further, as we start to look at how to ensure optimal quality within our Power BI data models. The relevant skills for this area as follows:

Manage incomplete data; meet data quality requirements

To follow on as part of the examples below, make sure you have downloaded the WideWorldImporters sample database and hooked up Power BI Desktop to the tables described in my first post in this series. With all this done, it is crucial first to grapple a vital concept relating to the management of incomplete data, namely, the ability to…

Filter Data

If you are consuming an entire SQL tables worth of data within the Power Query Editor, the size of your model can grow over time. In this scenario, it may be necessary to apply column-level filters to your data directly within Power Query, as opposed to merely dumping a cartload of irrelevant data in front of your users. Fortunately, the whole experience of filtering data should be a cakewalk, given its similarity with Microsoft Excel filters, as we can see below when attempting to filter the FullName field on the WideWorldImporters Sales.CustomerTransactions table:

This particular feature does have some differences compared with Excel, though:

  • For large datasets, only a subset of all total, distinct record values are returned. This fact is indicated above via the List may be incomplete warning sign. Clicking the Load more option will do exactly that, but may take some time to process.
  • The range of filters available will differ depending on the data type of the column. For example, String data types will have filters such as Begins With...,  Does Not Begin With…, whereas Dates are filterable based on Year, Quarter, Month etc.
  • The Remove Empty option will do just that – remove any columns that have a blank or NULL value.
  • As discussed on the Performing Data Transformations post, when you start combining filters with Parameters, it is possible to transform particularly unwieldy filtering solutions into more simplistic variants.

Column Errors

When it comes to adding custom columns as part of your Power Query transformations, there is always the potential for these to error, due to a misconfigured calculation or some other kind of unforeseen issue. When this occurs, the corresponding column value is flagged accordingly within the Power Query Editor and can be inspected further to determine the root cause of the issue. To demonstrate this, add on the following custom column using the following formula onto the Sales.CustomerTransactions table

[AmountExcludingTax] * [Error]

The value for each row in the new column should resemble the following:

When this issue occurs, it may be most prudent to first try and address the issue with your underlying calculation, ideally fixing it so that no error occurs at all. Where this is not possible, you can then look towards removing any rows that contain such a value. We will see how this can be done in the Adding/Removing Rows section later in this post.

Blank Data

On the flip side of field value errors is blank data. In most cases, when working with SQL data sources, this will rear its head when there are NULL values in your database. For example, take a look at below at the CreditLimit field on the WideWorldImporters Sales.Customers table:

When these fields are fed through from Power Query and relied upon as part of DAX custom column/Measure creation, you may start to get some unintended consequences. For example, after filtering the same table above only to retain rows where the CreditLimit equals null, attempting to create a Measure that totals up all CreditLimit values results in the following when displayed as a Card visualisation:

If you, therefore, have a desire to perform additional aggregations or custom calculations on fields that contain blank/null values, then you should take the appropriate steps to either a) remove all rows that contain one of these two values or b) perform a Replace action on the column to ensure a proper, default value appears instead. For the CreditLimit field, this can be as simple as replacing all null values with 0:

Adding/Removing Rows

Often our data sources are not pristine clean from a data perspective – duplicate rows may be common, it could be that rows exist with completely blank or null values or your incoming data file could be a complete mess from a column header perspective. With this problem in mind, the Power Query Editor provides us with the functionality to keep or remove rows based on several different conditions:

The options granted here should be reasonably self-explanatory, but the list below contains some additional guidance if you need it:

  • Keep/Remove Top Rows: Keeps or removes the top number of rows, in ascending order, based on the amount you specify.
  • Keep/Remove Bottom Rows: Keeps or removes the bottom number of rows, in descending order, based on the number you specify.
  • Keep Range of Rows: Keeps the number of rows specified based on the starting row number. For example, for a 50-row table, if a First row value of 1 and a Number of rows value of 10 is selected, then the first ten rows will be retained.
  • Keep/Remove Duplicates: Based on the currently selected column(s), keeps or removes all rows with duplicate values.
  • Keep/Remove Errors: Based on the currently selected column(s), keeps or removes all rows that have an Error value.
  • Remove Blank Rows: Removes any row that has a blank or NULL value.

Formatting Column Data

Data from a live, production system, such as Dynamics 365 Customer Engagement, can sometimes be a complete mess from a readability perspective; incorrect casing and invalid characters are typically commonplace in this situation. Fortunately, there are a range of options at our disposal with the Power Query Editor, on the Transform tab:

Most of these are self-explanatory, with the exception of the Trim and Clean options:

  • Trim removes any leading/trailing whitespace characters from a string value.
  • Clean detects and removes any non-printable characters from a string value.

Although not technically a data cleansing options, there are some clear usage scenarios for the Add Prefix & Add Suffix options, such as creating unique reference code for each column value, based on the unique record number value (e.g. ABCD-1, ABCD-2 etc.).

Formatting options for other column types are not available from within Power Query. So if, for example, you wished to format all date values in the format YYYY-MM-DD, you would have to move outside of the Power Query Editor to achieve this. The steps involved to accomplish this will be a topic for a future post.

Example: Cleansing Data

Having reviewed each of the possible cleansing options at our disposal, let’s now take a look at an example of how to cleanse a troublesome dataset:

  1. Within the Power Query Editor, on the Home tab, select the New Source -> Blank Query option. This will create a new Query in the left-hand pane called Query1.
  2. Select Query1 and press F2 to allow you to rename it to CleanseExample.
  3. Right-click the CleanseExample query and select the Advanced Editor option:
  4. Within the Advanced Editor window, copy & paste the following code into the window:
    • #table(
          {"Forename", "Surname", "Description"},
              {"JANE","smith","   this describes the record"},
              {"alan", "JOHNSON", "    record description detected    "},
              {"   MARK", "CORRIGAN    ","another description"},
              {"JANE","smith","   this describes the record"}
  5. It should resemble the below if done correctly:
  6. When you are ready, press the Done button. PowerQuery will then create a table object using the code specified, populating it with records, as indicated below:
  7. There are three key issues with this data that need resolving:
    • The inconsistent word casing on the Forename/Surname.
    • Whitespacing on the Description and ForeName fields.
    • Duplicate records.
  8. These issues are fixable by taking the following action:
    • For the casing issue, CTRL + left click to select the Forename & Surname fields, go to the Transform tab and select Format -> Capitalize Each Word. Your data will then be modified to resemble the below:
    • For the whitespace issue, select the Forename & Description fields and, on the Transform tab, select Format -> Trim:
    • Finally, to remove the duplicate record for Jane Smith, highlight the Forename & Surname fields, navigate to the Home tab and select Remove Rows -> Remove Duplicates. This will then leave us with three records, as illustrated below:
  9. As a final (optional) step, we can also look to clean up the Description field values by applying the Capitalize Each Word formatting option:

Et voilà! We now have a tidy and clean table, ready for consumption within Power BI 🙂

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.

Data cleansing is a reasonably short subject area in the grander scheme of things, but the steps covered represent key stages towards building out a competent and trustworthy reporting solution. The next post in the series will discuss the options available to us in building out more complex and bespoke data models.

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.