{"id":68839,"date":"2016-10-26T15:41:36","date_gmt":"2016-10-26T15:41:36","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=68839"},"modified":"2021-09-15T13:22:22","modified_gmt":"2021-09-15T13:22:22","slug":"importing-excel-data-power-bi-desktop","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/importing-excel-data-power-bi-desktop\/","title":{"rendered":"Importing Excel Data into Power BI Desktop"},"content":{"rendered":"<p>For many organizations, Microsoft Excel remains a critical tool for recording, calculating, and sharing data, especially when it comes to number crunching and disseminating vital information. Those workbooks contain a great deal of important data, and more and more organizations are looking for ways to visualize and subsequently capitalize on that information.<\/p>\n<p>Given this trend, it\u2019s no surprise that Power BI Desktop lets you import data directly from an Excel workbook into the Power BI Desktop environment. Not only can you retrieve data from the spreadsheets themselves, but you can also capture Power Pivot tables, Power View visualizations, Power Query queries (Get &amp; Transform in Excel 2016), and other components.<\/p>\n<p>If all you\u2019re after is basic spreadsheet data, then the process of getting the information you need into Power BI Desktop is a relatively straightforward operation. However, it can be less apparent how to capture the other components, especially if the source workbook contains a mix of data such as spreadsheets, queries, and PivotTables based on Power View tables.<\/p>\n<p>In this article, I walk you through the process of using Power BI Desktop to capture information from an Excel workbook that contains a mix of components. We\u2019ll work with a local .xlsx file that contains data from the <strong>AdventureWorks2014<\/strong> database on a local SQL Server instance.<\/p>\n<p>For the purposes of this article, the source and nature of the data are secondary to getting the information you need into Power BI Desktop. You can use whatever data you want to create your test Excel file. However, it you plan to follow along with the examples in this article, the file should include a number of specific components, which we\u2019ll go through shortly. In the meantime, if you\u2019re new to Power BI Desktop, you might want to first review the previous articles I\u2019ve written on the topic:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/working-with-sql-server-data-in-power-bi-desktop\/\">Working with SQL Server data in Power BI Desktop<\/a><\/li>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/reporting-services\/power-query-formula-language-in-power-bi-desktop\/\">Power Query Formula Language in Power BI Desktop<\/a><\/li>\n<li><a href=\"https:\/\/www.simple-talk.com\/sql\/bi\/power-bi-elbow\/\">More Power BI to your Elbow<\/a><\/li>\n<\/ul>\n<h2>Setting up the Excel file<\/h2>\n<p>Before we get into the nitty gritty of how to get data from an Excel file into Power BI Desktop, we need to set up our example source file. The file should include a basic spreadsheet, a spreadsheet table based on an Excel query, a PivotTable based on a Power Pivot table, and a Power View visualization.<\/p>\n<p>Before creating the Power Pivot and Power View components, you might need to enable these features on your Excel installation, if you haven\u2019t already done so. How you do this will depend on which version of Excel you\u2019re using. Be sure to check the Microsoft documentation for details.<\/p>\n<p>Regardless of what data you use to create the Excel file, it is important that you understand the components that go into the file in order to see what\u2019s going on in Power BI Desktop when you try to import information from that file.<\/p>\n<p>Let\u2019s begin with the basic spreadsheet. For my example, I started by running the following <strong>sqlcmd<\/strong> command to create a .csv file:<\/p>\n<pre>sqlcmd -S localhost\\sqlsrv16 -d AdventureWorks2014 -Q \"SELECT BusinessEntityID EmpID, FirstName, LastName, CountryRegionName Region, TerritoryName Territory FROM Sales.vSalesPerson WHERE JobTitle='Sales Representative' ORDER BY EmpID;\" -o C:\\DataFiles\\SalesReps.csv -s\",\" -W<\/pre>\n<p>The command simply retrieves a subset of data from the <strong>vSalesPerson<\/strong> view into the .csv file. I then opened the file in Excel 2016, cleaned up the results to get rid of any double lines and comments, and then saved the file as an .xlsx file, giving it the name <strong>Employees.xlsx<\/strong>. This provided me with my initial spreadsheet, which I named <strong>SalesReps<\/strong>, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-18.png\" alt=\"_screenshots4\/stPowerBI4_fig_a_01.png\" \/><\/p>\n<p>Next I added a query to the Excel file, based on the following T-SQL statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql decode:true\">SELECT BusinessEntityID EmpID, FirstName, \r\n  LastName, SalesLastYear, SalesYTD\r\nFROM sales.vSalesPerson\r\nWHERE JobTitle='Sales Representative'\r\nORDER BY EmpID;\r\n<\/pre>\n<p>Like the preceding <strong>sqlcmd<\/strong> command, this statement also retrieves data from the <strong>vSalesPerson<\/strong> view in the <strong>AdventureWorks2014<\/strong> database, but instead captures the sales figures for each sale rep. When setting up the query, I created a connection to the SQL Server 2016 instance, specified that a T-SQL query be used rather than a table or view, and named the query <strong>qrySales<\/strong>. The following figure shows the edit window for the new <strong>q<\/strong>uery.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-19.png\" alt=\"_screenshots4\/stPowerBI4_fig_a_02.png\" \/><\/p>\n<p>After I created the query, I sent the data to its own spreadsheet, which I named <strong>Sales<\/strong>. Notice that the data is rendered on the spreadsheet as a table, rather than just a simple range of data.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-20.png\" alt=\"_screenshots4\/stPowerBI4_fig_a_03.png\" \/><\/p>\n<p>Next, I opened the Power Pivot management window and created a connection to an external data source (the same SQL Server instance as the query), basing the connection on the following <strong>SELECT<\/strong> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql decode:true \">SELECT CountryRegionName Region, TerritoryName Territory, SalesYTD\r\nFROM sales.vSalesPerson\r\nWHERE JobTitle='Sales Representative'\r\nORDER BY Region, Territory;\r\n<\/pre>\n<p>Again, we\u2019re retrieving data from the <strong>vSalesPerson<\/strong> view. In this case, I named the query <strong>qrySalesYTD<\/strong>. After completing the process, Excel fetched the data and created a Power Pivot table with the same name, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-21.png\" alt=\"_screenshots4\/stPowerBI4_fig_a_04.png\" \/><\/p>\n<p>Next, I created a PivotTable based on the three columns in the <strong>qrySalesYTD<\/strong> table. I named the PivotTable <strong>SalesYTD<\/strong>, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-22.png\" alt=\"_screenshots4\/stPowerBI4_fig_a_05.png\" \/><\/p>\n<p>To finish setting up the sample Excel file, I created a simple Power View visualization based on the <strong>qrySalesYTD<\/strong> query. As the following figure shows, I named the Power View sheet <strong>RegionalSales<\/strong>.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-23.png\" alt=\"_screenshots4\/stPowerBI4_fig_a_06.png\" \/><\/p>\n<p>This completes the sample Excel file. Just to sum up what I\u2019ve done, the workbook now includes the following components:<\/p>\n<ul>\n<li>Spreadsheet named <strong>SalesReps<\/strong><\/li>\n<li>Query named <strong>qrySales<\/strong><\/li>\n<li>Spreadsheet table named <strong>Sales<\/strong> based on the <strong>qrySales<\/strong> query<\/li>\n<li>Pivot View table named <strong>qrySalesYTD<\/strong><\/li>\n<li>PivotTable named <strong>SalesYTD<\/strong> based on the <strong>qrySalesYTD<\/strong> Power Pivot table<\/li>\n<li>Power View visualization named <strong>RegionalSales<\/strong><\/li>\n<\/ul>\n<p>Again, it\u2019s not the data itself that\u2019s important in this case, but rather the various Excel components. You need to understand what your worksheet includes in order to know how best to get the information into Power BI Desktop. As long as you\u2019ve populated the components with some sort of data, you\u2019re ready to pull that data into Power BI Desktop.<\/p>\n<h2>Retrieving information from an Excel workbook<\/h2>\n<p>If you\u2019ve worked in Power BI Desktop before, you\u2019ve likely used the Get Data feature to import data from different sources, so you\u2019re first instinct might be to go there to get the data from the Excel file, in which case, you\u2019ll be quickly rewarded with an <strong>Excel<\/strong> option, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-24.png\" alt=\"_screenshots4\/stPowerBI4_fig_b_01.png\" \/><\/p>\n<p>You can certainly use this option to get at some of the Excel information. Simply click <strong>Excel<\/strong>, navigate to your source file, and double-click it. Before long, Excel opens the <strong>Navigator<\/strong> dialog box, listing the components you can import, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-25.png\" alt=\"_screenshots4\/stPowerBI4_fig_b_02.png\" \/><\/p>\n<p>The <strong>Navigator<\/strong> dialog box gives us a choice of four components:<\/p>\n<ul>\n<li>Query named <strong>qrySales<\/strong><\/li>\n<li>Spreadsheet table named <strong>Sales<\/strong> based on the <strong>qrySales<\/strong> query<\/li>\n<li>Spreadsheet named <strong>SalesReps<\/strong><\/li>\n<li>PivotTable named <strong>SalesYTD<\/strong> based on the <strong>qrySalesYTD<\/strong> Power Pivot table<\/li>\n<\/ul>\n<p>Within the <strong>Navigator<\/strong> dialog box, you can preview data for each component, and you can select any of the available components to load into Power BI Desktop. However, as you\u2019ve probably noticed, a couple components are missing:<\/p>\n<ul>\n<li>Power Pivot table named <strong>qrySalesYTD<\/strong><\/li>\n<li>Power View visualization named <strong>RegionalSales<\/strong><\/li>\n<\/ul>\n<p>We\u2019ll get to these missing components shortly, but first let\u2019s look at what we get when we load all four available components into Power BI Desktop. First, we\u2019ll start in Data view, which shows the imported data from all four components saved to tables with the same names, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-26.png\" alt=\"_screenshots4\/stPowerBI4_fig_b_03.png\" \/><\/p>\n<p>Not surprisingly, the <strong>Sales<\/strong> table and <strong>qrySales<\/strong> table include the same data, so we really needed to import only one of those source components.<\/p>\n<p>If we move on to the query editing window, shown in the following figure, we\u2019ll also find that the four source components have been created as queries, which serve as the basis for the four tables. As a result, we can apply any of the transformations to the data that are available through the query editing window and then apply those changes to our tables.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-27.png\" alt=\"_screenshots4\/stPowerBI4_fig_b_04.png\" \/><\/p>\n<p>Now let\u2019s return to the Data view and the <strong>SalesYTD<\/strong> table (or you can instead stay in the query editing window and view the <strong>SalesYTD<\/strong> query from there). What you\u2019ll quickly discover is that Power BI Desktop flattened out our PivotTable, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-28.png\" alt=\"_screenshots4\/stPowerBI4_fig_b_05.png\" \/><\/p>\n<p>What this points to is that Excel data should be in a flattened form when you import it into Power BI Desktop so you don\u2019t run into any surprises. In this case, it would be more efficient to bring in only the source table\u2014the original <strong>qrySalesYTD<\/strong> Power Pivot table in Excel\u2014and then create the reports we need in Power BI Desktop to render the data how we want it. But that leads us back to the fact that the Get Data import process does not include the Power Pivot table or the Power Query visualization.<\/p>\n<p>Before we get into how that do that, however, let\u2019s look at one more aspect of the Get Data import process. Suppose our Excel workbook did not include the spreadsheet table named <strong>Sales<\/strong> or the PivotTable named <strong>qrySalesYTD<\/strong>. If we were to try to import the stripped-down version of the workbook, the <strong>Navigator<\/strong> dialog box would present us with only one component available for import, the <strong>SalesReps<\/strong> spreadsheet, as shown in following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-29.png\" alt=\"_screenshots4\/stPowerBI4_fig_c_01.png\" \/><\/p>\n<p>It\u2019s not surprising that the only spreadsheet object to appear is <strong>SalesReps<\/strong>, but what does seem odd is that the <strong>qrySales<\/strong> query no longer shows up. Apparently, Power BI Desktop will include the query only if the workbook also includes an associated spreadsheet table.<\/p>\n<p>The conclusion to all this is that the Get Data approach is best suited for spreadsheets that contain only flattened data, whether in the form of a basic spreadsheet or data that has been turned into a table. For anything else, we need to look to a different approach for capturing the Excel components.<\/p>\n<h2>Importing the Excel file<\/h2>\n<p>As we have seen, to use the Get Data method to capture an Excel query, the workbook must include an associated spreadsheet table, otherwise you need to take a different approach. In fact, for most non-spreadsheet components, you have to <em>import<\/em> the Excel file into the Power BI Desktop environment. To do this, click <strong>File<\/strong>, point to <strong>Import<\/strong>, and then click <strong>Excel<\/strong> <strong>Workbook<\/strong> <strong>Contents<\/strong>, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-30.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_01.png\" \/><\/p>\n<p>From there, you need to navigate to your Excel file and double-click it. Power BI Desktop will then present you with the following screen, which provides an overview of the import process.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-31.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_02.png\" \/><\/p>\n<p>When you click <strong>Start<\/strong>, Power BI Desktop imports the various non-spreadsheet components from the Excel file and eventually returns a summary screen, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-32.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_03.png\" \/><\/p>\n<p>If you were to scroll down, you would also see the Power View visualization, giving us the following components:<\/p>\n<ul>\n<li>Query name <strong>qrySales<\/strong><\/li>\n<li>Query named <strong>qrySalesYTD<\/strong><\/li>\n<li>Data model table named <strong>qrySalesYTD<\/strong> based on the <strong>qrySalesYTD<\/strong> query<\/li>\n<li>Auto-generated measure named <strong>Sum<\/strong> <strong>of<\/strong> <strong>SalesYTD<\/strong> based on the <strong>qrySalesYTD<\/strong> table<\/li>\n<li>Power View sheet named <strong>RegionalSales<\/strong><\/li>\n<\/ul>\n<p>When you import an Excel workbook, it is an all-or-nothing proposition. You cannot select individual components. In addition, you cannot import spreadsheet data, such as that contained in the <strong>SalesReps<\/strong> spreadsheet. For that, you still need to take the Get Data approach.<\/p>\n<p>Now let\u2019s look at what we do get with our import. Once again, we\u2019ll start with the Data view. What you\u2019ll quickly discover is that the only table included here is <strong>qrySalesYTD<\/strong>, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-33.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_04.png\" \/><\/p>\n<p>Although the data in the <strong>qrySalesYTD<\/strong> table is what we would expect, it points to the fact that a table based on the <strong>qrySales<\/strong> query is missing. So let\u2019s look go to the query editing window to see what might be going on. As the following figure shows, both queries are listed.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-34.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_05.png\" \/><\/p>\n<p>It turns out that we must specifically enable the <strong>qrySales<\/strong> query for the data to be loaded into a table. To enable the query, right-click the query name in the <strong>Queries<\/strong> pane and then click <strong>Enable<\/strong> <strong>Load<\/strong>, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-35.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_06.png\" \/><\/p>\n<p>After you enable the query, close the query editing window, being sure to apply your changes, and then return to the Data view. You should find that the table has been added, but as you\u2019ll see the following figure, it might not include the data.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-36.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_07.png\" \/><\/p>\n<p>To get the data in there, you need to right-click the name of the table in the <strong>Fields<\/strong> pane and then click <strong>Refresh<\/strong> <strong>data<\/strong>. The table should now be populated, as shown in the following figure.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-37.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_08.png\" \/><\/p>\n<p>Now we have the data we need from the Excel query and the Power Pivot table that are in the source Excel file. The only other missing component is the Power View visualization. For that, we need to go to Report view. As the following figure shows, Power BI Desktop automatically adds a page for the visualization.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-38.png\" alt=\"_screenshots4\/stPowerBI4_fig_d_09.png\" \/><\/p>\n<p>We can now change or modify the settings as we would with other visualizations. Like any Excel component that we import, once we get the information into Power BI Desktop, the data is no longer tied to the original Excel file, and we can do whatever we want.<\/p>\n<p>That said, we must still deal with our wayward spreadsheet as a separate step, but even for that, once we have it in Power BI Desktop, we have free reign.<\/p>\n<h2>On the road to Power BI Desktop<\/h2>\n<p>We have two basic approaches for bringing Excel data into Power BI Desktop: the Get Data process and the import process. For the most part, we\u2019ll use the Get Data process to bring in spreadsheets and use the import process to pull in the non-spreadsheet components.<\/p>\n<p>Where things get a little tricky is if we have a spreadsheet table based on a query. (No doubt there are other tricky areas that I\u2019ve yet to discover.) You can use the import process to bring in the query, in which case you have to take the extra steps of creating and populating your table, or you can use the Get Data process to bring in either the table or query. If the query exists without as associated spreadsheet table, your only option is to import the Excel file.<\/p>\n<p>By using the Get Data and import processes, you should be able to capture most of the Excel data you need. You might run into a few limitations, such as some Power View and data model features not being supported, but overall Power BI lets you capture most of the available information. Once you have that information in Power BI Desktop, you can transform the data and create a variety of visualizations. You can then save file, share it with others, or publish your reports to the Power BI service.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In any commercial setting, Excel Spreadsheets remain the preferred way of  collecting and analysing data, and it makes sense that  it should be easy to  get the data into PowerBI so it can be made more generally available for sharing and further analysis.  As well as the data, we&#8217;d probably need the other analysis components such as the Power Pivot tables, Power View visualizations, Power Query queries.  It is all possible, and Robert Sheldon demonstrates how.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[],"coauthors":[6779],"class_list":["post-68839","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68839","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=68839"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68839\/revisions"}],"predecessor-version":[{"id":92437,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68839\/revisions\/92437"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=68839"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=68839"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=68839"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=68839"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}