Querying SQL Server Data with Power BI Desktop

Problem

I saw your recent tip on
Getting Started with Power BI Designer and now I want to find out some of
the functionality related to the Power BI Desktop query tools. What
functionality is available? What data sources can I connect to? How
can I manipulate the data? Can I modify the queries directly?

Solution

In a previous tip, Getting Started with Power BI Desktop,
we described in detail the new Power BI Desktop (AKA Power BI
Designer) that Microsoft has added to the Power BI tool set. Of
course just as the tip was published, Microsoft renamed the tool from
Power BI Designer to Power BI Desktop (PBID). Even so, the basics of
the tool and the details in the tip still apply. Getting the designer is easy as
the Power BI Desktop is available for download at: https://powerbi.microsoft.com/downloads.
The current edition of the software is available free of charge and
Microsoft has indicated that the tool will continue to be free. Of
course that could change. Furthermore to publish the PBID reports to
the
web, you must have an account setup on the Power BI site. Before
I go too far I need to warn you that the tool is still in its early
stages. Sure it works well from my testing, but features are
being added and removed every month. Be sure you temper any move
to production with the fact the tool set may not scale as anticipated
or may plainly just not work. OK..... So it may not work. Enough
said. With all these changes and updates, Microsoft is currently
releasing an updated preview about once per month. You can see the
latest version information on the Microsoft Power BI Support page at: https://support.powerbi.com/knowledgebase/articles/489224-what-s-new-in-the-latest-power-bi-designer-preview.

Querying data with Power BI Desktop

When you open PBID, the start screen, as shown below, allows you to
start the query process via clicking on Get Data. Alternately, if you
bypass the start screen, you can click on the Get Data button in the
ribbon.

Both options open the data source selection screen. As you can see
from the three illustrations below, the data source list is both long
and growing each month. You will note some are still in beta mode,
some, like SQL Server, Excel, and Access are "old stand bys", while
others I find to be particularly noteworthy including:

Folder- This source allows you to examine the meta data of all the files and sub-folders in a folder

ODBC - If all else fails, hopefully a data source allows for ODBC based connections

You
need to be aware that many of these data sources will require you to
know and setup the connection string details without much assistance
from a fully automated wizard. Some of the sources provide more details
than others. Be sure you know the pertinent details to setup a connection (server and
URL names / addresses, ports, databases names, user ids, passwords, etc.).

Working with SQL Server Data in the Power BI Desktop

In order show the full functionality of the query tools, we will use
one of the data sources for now. I hope to complete tips on the
various data sources in the near future. As such, the first query we
will
create is from the AdventureWorks database; thus it will be a SQL
Server data source. As shown below, the SQL Server data source requires
the server and, optionally, a database name and a SQL Statement.

After filling in the server and database and
clicking ok, the Navigator screen opens. As seen below, this screen
allows for the selection of individual tables from the AdventureWorks
database. You should take notice of a couple of items. First, when you click to
select a table, a preview of that table shows on the right side of the
screen; to stop the preview, you can click on the refresh button which
appears in the upper right part of the screen (see green circle below).
Furthermore, you have three options for completing the selection of
your data: 1) Load runs the queries and returns the data back to the
PBID main screen, 2) Edit allows you to adjust each query (details
about this option will be described below), or 3) Cancel the new data
source load altogether. For this example, I selected load which ran
each of the queries (similar to running a SELECT * without any
criteria).

Now that we have loaded the various tables into the Desktop design
window, as illustrated next, we could begin dragging and dropping the
tables and fields onto the dashboard design grid. However, this tip is
on the query process, so will save the dashboard design for another tip.

Edit the Power BI Queries

Thus, we are going to modify the queries we have created by clicking on the Edit Queries button in the ribbon.

Now we can actually edit the queries to match the requirements for a
report's design. The main Edit query window opens in a completely
separate window as a
shown below.

To allow for access to some additional information right at the get
go, I would recommend immediately jumping to the View tab on the Ribbon, as seen
below and then 1) click on the query settings button which will show
several properties in the Query Settings property box (right side in
below screen print) and 2) click the Formula Bar check box. The formula bar will
allow the query designer to see what formula is being used to retrieve the data.

Advanced Editor in Power BI Desktop

Another interesting view is to jump back to the home tab, and click
on the Advanced Editor option. This button appropriately opens the
advanced editor window, but more importantly shows you the query that is
being executed against the data source. The syntax may look unfamiliar
to you; PBID uses M, the Power Query Formula Language. I hope
to do an upcoming tip on using the Power Query Formula Language, but
for now, you can see in the below illustration the language specifies
the data source connection and then requests the data from the
SalesOrderHeader. The second illustration below filters on
SalesOrderNumber (4 sales orders to be exact). You can see the filter
details show up in M
query.

Filtered Rows in Power BI Desktop

You will also notice that once the filter
was applied (you apply a filter similar to how you would in an Excel
table by clicking the down arrow next to the column header), the Query
Settings actually shows that the results were filtered by adding a Filtered Rows line to the Applied Steps.

These
filtered results are then the basis for the data loaded onto to the
main dashboard design grid; notice in the below illustration from the design grid
that only the 4 filtered rows appear in the report results.

The neat part is that we can easily go back to the
Edit Queries window and "undo" our filter in the Query Setting window.
As shown below, by clicking on the X icon next to Filtered Rows, we can
remove the filter which results in all rows now being returned to the
query results.

Modifying Columns Displayed in Power BI Desktop

If we again select close and load on the Edit
Query window, now all the rows are shown in our dashboard, as seen in
the below illustration.

Just like it is not a good idea to do a "SELECT
*" in regular T-SQL, it would also be beneficial to include only the
columns we need for our reports. Thus the Query Editor provides a quick
way to remove columns from a query by selecting the columns to be
deleted and then right mouse clicking and selecting Remove Columns (or
you could keep the selected columns by selecting Remove Other
Columns).

Group By in Power BI Desktop

An alternate transformation that we can complete
is a Group by rollup. As shown below, we can transform the Sales Reason
table into a rollup count by ReasonType using the Group by
functionality. The below series of illustrations show the transformation that
the table goes through to eventually generate a quick graph that shows the
Count of ReasonTypes.

Duplicate a Query in Power BI Desktop

We can also easily duplicate a query table
by right mouse clicking on a table and then selecting Duplicate. Now we
have the ability to make adjustments to the new query without impacting the
original query.

Rename a Query in Power BI Desktop

Furthermore, we can easily rename the query, as shown below, by editing the Name Property.

Split a Column in Power BI Desktop

Next we can easily parse out a column by
splitting a column into several parts. First as illustrated below, we
select the column, then click Split Column and choose by Number of
Characters. Next, we tell the query designer how to split up the column
and finally tell it to do the split repeatedly until the end of the
string.

Review of Advanced Editor with all Changes

If we review both the Advanced Editor and the Query
Settings in the below screen print, you will notice that it shows all
the work we have done on the query including duplicating a column,
reordering the column, and splitting the column.

Data Type Change in Power BI Desktop

Finally, we can easily change the data type of a
column. For instance, we can adjust a DateTime field to Data Only by
using the date only functionality.

Conclusion

The Edit Query functionality in the Power BI Desktop (PBID), even in
these early stages of its release, contains a rich set of features and
functionality. For instance, PBID provides the ability to: 1)
duplicate a column 2) split a column into parts 3) to change column data
types. Furthermore, we can filter the data based on column values which
in turn filter the results on the dashboard. Finally, we can use the
group by function to aggregate values in a query. We have touched
just the tip of the BPID functionality, so look for more tips on the
new Power BI Desktop and how to get the most out of this new tool.