Get Free Tips
MSSQLTips.com
Home » Querying SQL Server Data with Power BI Desktop
Power BI

Querying SQL Server Data with Power BI Desktop

written by Scott Murray 0 comments

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.PBID Start Screen

Get Data Alternative

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
  • Microsoft Azure MarketPlace, Azure SQL Database, Azure HDInsights, Azure Blob Storage, Azure Table storage – These sources provide a tight integration with Azure based services
  • Web – Web based html tables can be scraped to get data
  • SharePoint Lists – SharePoint lists data can be extracted from a SharePoint site
  • Hadoop – Allows for easy retrieval of Hadoop based data structures
  • Google Analytics – Surprising to me is the availability of Google Analytics as a data source
  • SalesForce Objects and Reports – Opens up access to SalesForm CRM data and reports
  • 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.).


Sources 1
Sources2

Sources3

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.

SQL Data Source

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).

Navigator

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.

Desktop after load

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.EditQueries

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.

Edit query Main window

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.

View Changes

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.

Advanced Editor

Advanced Editor with filter

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.query settigns filter

filtered rows

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.

query 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.

remove filter
remove filter 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.design grid query results no filter

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).

remove 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.group by select

group by window

group by results
group by results in grid

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.duplicate
duplicate table

Rename a Query in Power BI Desktop

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

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.split column

split column window
split column results

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.New M query

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.dateonly

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.

Next Steps

mssqltips white logo

MSSQLTips.com delivers SQL Server resources to solve real world problems for DBAs, Architects, DevOps Engineers, Developers, Analysts, Cloud and Business Intelligence Pros – all for free. The content we serve is all human written and based on our authors’ real-world experience. Check out tips, articles, scripts, videos, tutorials, live events and more all related to SQL Server on-premises and in the cloud.

Resources

Community

Subscribe

Be a better SQL Server Professional – Join MSSQLTips.com

Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
Facebook X-twitter Rss Envelope Linkedin
Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
sql server 2025 guide