Power BI, the solution developed by Microsoft, allows you to create data models and then turn them into dynamic, interactive reports that are easily accessible to your intended recipients. Show
Although Power BI is being increasingly used by finance professionals, teams still have to produce static reports in Excel to format and analyze their data. So how do you export data from Power BI to Excel? In this article Roxanne, Power BI consultant at F31 will walk you through the different methods available to export Power BI to Excel: Option 1: Export data from a Power BI visual to Excel with “Export Data”Here is how to export data from an online Power BI report to Excel:
You can now edit your data or re-make graphs directly in Excel. This first type of Power BI export to Excel is simple but limited as:
However, it is possible to export a table from the Power BI desktop to Excel: in the “Data” view, select a table, then right-click and “Copy” for a column, or “Copy Table” for the entire table. Simply paste the data into an Excel file to retrieve it. > Discover 8 ways to make beautiful financial charts in Excel. Option 2: Export large data sets from Power BI to Excel with “Analyze in Excel”The second way to export data from Power BI to Excel can handle whole data models, plus offers the advantage of being able to refresh the data directly in Excel! This will help when you want to export large amounts of data. Here is how to connect your data model to a blank Excel file with the “Analyze in Excel” function:
This downloads an Excel file in .ODC (Office Data Connection: an extension stored using HTML and XML) version. From here you can create PivotTables, visuals, etc. directly in Excel from the data derived from the Power BI data cube. For example, when you want to create a new PivotTable in the Excel file (“Insert” > “PivotTable” tab), simply choose “Use an external data source” and then connect to the desired data cube by clicking on “Choose Connection.” To refresh the data from the Power BI report, simply go to the “Data” tab and click on “Refresh All.” This second type of Power BI export to Excel has the advantage of being semi-dynamic, yet it’s still limited as:
> Get to know our top 3 productivity tips to become an Excel champion Option 3: “Get Data” from within ExcelHere’s how to access Power BI data from within Excel:
Whilst this is a quick, easy way to export data from Power BI to Excel, it isn’t the most efficient. This is because selecting fields from a PivotTable Field List can make it difficult to visualize and understand the data set, especially if you didn’t create the Power BI dashboard yourself. Clicking through the various PivotTable fields to create your intended table in Excel could cost more time in the long run. > 7 easy ways to reduce Excel file size right now Option 1: Export data from a Power BI visual – it is useful when you are simply looking to obtain the data underlying a visual on a Power BI report. Option 2: Analyze in Excel – it is more suitable if you want to study your Power BI datasets. By importing the entire dataset, you have more flexibility, as long as you make sure that your metrics are created in advance so you can use them in your tables and graphs. Option 3: Get Data from within Excel – it’s a relatively quick and easy way to export Power BI data to Excel. However, if you didn’t create the Power BI dashboard yourself, selecting the correct PivotTable fields could be time-consuming. > Discover how to link your Excel data to PowerPoint How to export Power BI to PowerPoint or WordGood news! You can now export Power BI visuals to PowerPoint or Word using UpSlide’s Power BI Link! This feature allows you to access your Power BI reports and dashboards directly from PowerPoint or Word, choose the visuals you need and insert them into your presentation in a few clicks. Once your Power BI is linked to your PowerPoint or Word, you can also update your entire presentation or specific visuals with the latest data by simply clicking the ‘Update Links’ button. UpSlide’s Power BI Link enables you to reduce your time spent on low value-added tasks and be sure the data in your presentation is always up-to-date. Here are some resources to help you learn more about Power BI:
Level: Intermediate Updated: 10 Nov 2020 I first wrote this article about how to import Tabular Data from Power BI into Excel back in Jan 2017. The benefits of extracting tabular data are still the same as they were back then however the process of extracting the tabular data from Power BI has changed. This update today re-presents the concept and shows the current process to complete the task. What Problem Does this Solve?Let’s assume you want to get a table of data from your data model, eg a printable list of customers with their annual sales year to date. Let’s say that there are 50,000 customers. How do you do this?
Create an Excel Table Connected to Power BIThis technique allows you to write a DAX Query and run that from within an Excel workbook table with a live connection back to an online Power BI Dataset. The example I am going to use in this article about extracting tabular data from Power BI is to create the below table of Products by Colour with the total sales for each colour. I am using this as a demo to show you how to do it. If I actually wanted to build this table, I would frankly just use a pivot table. But it is the concept I want to show, not the outcome. The steps to complete this process so you can import your tabular data from power bi are.
Here is a walk through. Create a Link to PowerBI.comThere are a few ways you can do this. You could use Analyze in Excel to create the link starting from PowerBI.com.
The other way is to start from Excel. Note: This is only available in the most recent versions of Excel. If you don’t see the feature below, you should use the Analyze in Excel approach mentioned above. You can directly connect to a Power BI Data Set from within Excel. Start with a brand new Excel file, go to the data menu and click on Get Data (#1 below). Then select From Power BI (#2 below). Go to Power BI Datasets (#1 below) and then select the dataset you want to use (#2 below). The Power BI data model gets connected to Excel and an empty shell of a pivot table appears on the worksheet. Create a Table that Links to Power BIThis is where the secret sauce is. The easiest way to extract tabular data from Power BI by creating an Excel Table linked to Power BI is to first create a pivot table. Just create anything simple, like this. Actually, you don’t even need the years, you can just add any measure into the values section, and that’s it. Double click on any one of the data points in the pivot table. When you do this, Excel will create an Excel table showing you all the records that make up the value in the pivot table. This new table has an underlying connection to the Power BI Service. You can now delete the pivot table sheet – it is not needed anymore. Write the QueryTo do this next step, you need to learn a bit of the DAX Query Language. I am not going to go into the DAX Query language in detail in this post as I cover that extensively in other blog articles. This one is a good place to start. I will simply show how to edit the current table code so you can build the table you need. To edit the query, I simply right clicked anywhere in the table (#1 below), then selected Table (#2 below), then Edit Query (#3 below). This brings up a dialog like the one below. The connection string at the top can be edited if needed, eg if you want to change the connection to a different report. The command text that you can see at the bottom can be deleted and replaced with any valid DAX table query. To demonstrate, I could write a simple DAX query as follows. After clicking OK, the query is executed against the service and the table (Products in this case) is returned to Excel. Total Sales by Product ColourNext, to get total sales by product colour, I edited the query as shown below. Here is my query EVALUATE And this gave me a table of all product colours and the total value of sales for those products as shown below. I could have used SUMMARIZE instead of ALL to get only the colours with sales. Interestingly the number formatting for Total Sales did not flow through to Excel as I expected it to. First I thought this was a bug, but the more I thought about it, the more I think this is “working as designed”. The representation of the Sales above is actually not the measure [Total Sales] that has the formatting. It is actually a new column projection in a new table that uses the Measure as an input. The new column clearly does not inherit the formatting from the measure. It would be useful it if it did, but think about the following query. EVALUATE ADDCOLUMNS ( ALL ( products[color] ), “Markup %”, DIVIDE ( [Total Sales], [Total Cost] ) – 1 ) The above query returns a % markup as the new column. The measures [Total Sales] and [Total Cost] are both formatted as $ with zero decimal. So how will the query know to return % as the number format? I therefore think this is working as designed. The good news is that if you apply the formatting you want to the entire column of the new table, the formatting will be applied to all values in that column even if the column grows or shrinks on a future refresh. What Next?Once you extract the tabular data and finally have a table, you can use the Excel print capabilities to turn this into a paginated report (if you want to). Eg you can repeat the headers on each printed page, etc. To refresh the table, just right click on the table and select “Refresh”. Further LearningIf you finally know how to import tabular data from power bi and would like to learn more about DAX as a query language, you can check out my series of articles that starts here An Introduction to DAX as a Query Language |