{"id":3199,"date":"2021-04-09T21:59:30","date_gmt":"2021-04-10T04:59:30","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3199"},"modified":"2021-04-09T21:59:33","modified_gmt":"2021-04-10T04:59:33","slug":"how-to-export-power-bi-data-to-excel-step-by-step","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3199","title":{"rendered":"How to Export Power BI Data to Excel (Step-by-Step)"},"content":{"rendered":"\n<p><em>Written by:&nbsp;<a href=\"https:\/\/spreadsheeto.com\/about\/#kasper-langmann\"><u>Kasper Langmann<\/u><\/a>, Microsoft Office Specialist.<\/em><\/p>\n\n\n\n<p>Exporting data from Power BI to Excel only takes&nbsp;<strong>1 to 2 clicks<\/strong>!&nbsp;<\/p>\n\n\n\n<p>Though normally, it\u2019s the other way around \u2014 importing data from Excel to Power BI.<\/p>\n\n\n\n<p>Nevertheless, it\u2019s super easy and intuitive to do.<\/p>\n\n\n\n<p>In this article, we\u2019ll show you the step-by-step process in exporting Power BI data to Excel.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>We\u2019ll also include an old-school copy-paste method that can only be done from the Power BI desktop version.<\/p><\/blockquote>\n\n\n\n<p>Excited? Let\u2019s get into it!&nbsp;<\/p>\n\n\n\n<p><em>*This tutorial is for Power BI Online (also called &#8220;Power BI Service&#8221;). If you have Power BI Desktop, then&nbsp;<a href=\"https:\/\/powerbi.microsoft.com\/en-us\/landing\/signin\/\" target=\"_blank\" rel=\"noreferrer noopener\">click here<\/a>&nbsp;and go to the online version.<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Introduction: Exporting Data<\/strong><\/h2>\n\n\n\n<p>Like what you read earlier, what normally happens is that you import data from Excel to Power BI as an&nbsp;<strong>.xlsx<\/strong>&nbsp;or&nbsp;<strong>.csv file<\/strong>&nbsp;to run insights and analytics on it.<\/p>\n\n\n\n<p>However, there could be reasons why you would want to export data from Power BI.<\/p>\n\n\n\n<p>Some of these include:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Double-checking the actual data but you don\u2019t have access to the data sources<\/li><li>Having the need to provide the data to an analyst (or someone) who is more familiar and comfortable with Excel<\/li><li>Using the current Power BI data in Cortana Analytics<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Exporting Data from a Power BI Dashboard<\/strong><\/h2>\n\n\n\n<p>A&nbsp;Power BI dashboard&nbsp;is like a page where you can&nbsp;<em>highlight<\/em>&nbsp;your data to tell a story. You do this by pinning tiles \u2014 visualizations \u2014 from reports.<\/p>\n\n\n\n<p>Let\u2019s say you have the following dashboard and you would like to export the data behind the&nbsp;<strong>\u2018This Year\u2019s Sales, Last Year\u2019s Sales\u2019<\/strong>&nbsp;tile:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/retail-analysis-sample-dashboard-export-tile.png\" alt=\"A tile in Retail Analysis Sample Dashboard in Power BI\" class=\"wp-image-12409\" title=\"retail-analysis-sample-dashboard-export-tile\"\/><\/figure>\n\n\n\n<p><em>Hover<\/em>&nbsp;your mouse over the tile and you\u2019ll notice an&nbsp;<strong>ellipsis<\/strong>&nbsp;or a&nbsp;<strong>3-dot icon (\u2026)<\/strong>&nbsp;fade in on the upper-right corner.<\/p>\n\n\n\n<p><em>Click<\/em>&nbsp;the ellipsis and&nbsp;<em>select<\/em>&nbsp;<strong>\u2018Export to .csv\u2019<\/strong>:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/export-dashboard-tile.png\" alt=\"Export to .csv option on a dashboard tile\" class=\"wp-image-12410\" title=\"export-dashboard-tile\"\/><\/figure>\n\n\n\n<p><em>Save<\/em>&nbsp;the file and&nbsp;<em>open<\/em>&nbsp;it in Excel:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/exported-dashboard-tile.png\" alt=\"\" class=\"wp-image-12411\" title=\"exported-dashboard-tile\"\/><\/figure>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>Take note that if you&nbsp;<strong>filter the visualization before exporting<\/strong>, you\u2019ll also get the&nbsp;<em>filtered<\/em>&nbsp;data.<\/p><\/blockquote>\n\n\n\n<p><strong>Exporting Data from a Power BI Report<\/strong><\/p>\n\n\n\n<p>The tiles you pin on dashboards came from reports. A Power BI report contains visualizations that represent the&nbsp;<em>insights<\/em>&nbsp;from a dataset.<\/p>\n\n\n\n<p>Basically, the steps into exporting data from a dashboard, report, and even dataset are&nbsp;<em>similar<\/em>.<\/p>\n\n\n\n<p>Let\u2019s say you\u2019re looking into your&nbsp;<strong>\u2018District Monthly Sales\u2019<\/strong>&nbsp;and you would like to export the data behind the&nbsp;<strong>\u2018This Year Sales by StoreNumberName\u2019<\/strong>&nbsp;report:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/district-monthly-sales-report-export.png\" alt=\"A report to export in District Monthly Sales report in Power BI\" class=\"wp-image-12412\" title=\"district-monthly-sales-report-export\"\/><\/figure>\n\n\n\n<p><em>Hover<\/em>&nbsp;your mouse over the report until you see the&nbsp;<strong>ellipsis<\/strong>&nbsp;on the upper-right corner.<\/p>\n\n\n\n<p>For some cases, you have to right click the report, and then click <strong>&#8220;Show as a table&#8221;<\/strong> or you won&#8217;t see the ellipsis.<\/p>\n\n\n\n<p><em>Click<\/em>&nbsp;the ellipsis and&nbsp;<em>select<\/em>&nbsp;<strong>\u2018Export data\u2019<\/strong>:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/exported-report-data.png\" alt=\"Export to .csv option on a dashboard tile\" class=\"wp-image-12413\" title=\"exported-report-data\"\/><\/figure>\n\n\n\n<p>Power BI will ask&nbsp;<em>specific<\/em>&nbsp;questions about your export data:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Export&nbsp;<strong>summarized data<\/strong>&nbsp;or&nbsp;<strong>underlying data<\/strong><\/li><li>File format either in&nbsp;<strong>.xlsx<\/strong>&nbsp;or&nbsp;<strong>.csv<\/strong><\/li><\/ul>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>What you have to look out here is the&nbsp;<em>size<\/em>&nbsp;of your report. The first one \u2014 .xlsx \u2014 supports a maximum of&nbsp;<strong>150,000 rows<\/strong>&nbsp;compared to the&nbsp;<strong>30,000 rows<\/strong>&nbsp;of .csv file.<\/p><\/blockquote>\n\n\n\n<p>For this example, let\u2019s export the underlying data in .xlsx format:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/export-data-report-prompt.png\" alt=\"\" class=\"wp-image-12414\" title=\"export-data-report-prompt\"\/><\/figure>\n\n\n\n<p><em>Save<\/em>&nbsp;the file and&nbsp;<em>open<\/em>&nbsp;it in Excel:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/exported-report-data-1.png\" alt=\"How the exported report data looks on Excel\" class=\"wp-image-12415\" title=\"exported-report-data\"\/><\/figure>\n\n\n\n<p>Like in exporting data from a dashboard, filters will also apply to the export data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Copy Table in Power BI Desktop<\/strong><\/h2>\n\n\n\n<p>When talking about&nbsp;Power BI Desktop vs Power BI Service&nbsp;(the online version), you\u2019ll notice a lot of differences.<\/p>\n\n\n\n<p>One of which is the&nbsp;<strong>data view<\/strong>&nbsp;in Power BI Desktop.<\/p>\n\n\n\n<p>This method requires that you have a Power BI Desktop since you\u2019ll be copying tables in the data view.<\/p>\n\n\n\n<p>Let\u2019s use the same dataset we\u2019ve used in the previous sections. To go to the data view, press the&nbsp;<strong>table icon<\/strong>&nbsp;on the left-hand sidebar:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/data-view-desktop.png\" alt=\"Data view in Power BI Desktop\" class=\"wp-image-12416\" title=\"data-view-desktop\"\/><\/figure>\n\n\n\n<p>You\u2019ll then notice a table on the center with the field options on the right-hand sidebar.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>Opening a specific table by&nbsp;<em>clicking<\/em>&nbsp;its name on the right-hand sidebar will list out its available fields under the table\u2019s name and display the table in the center.<\/p><\/blockquote>\n\n\n\n<p>To demonstrate, let\u2019s open the&nbsp;<strong>\u2018Store\u2019<\/strong>&nbsp;table:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/store-table-data-view.png\" alt=\"Store table of Retail Analysis Sample in the data view\" class=\"wp-image-12417\" title=\"store-table-data-view\"\/><\/figure>\n\n\n\n<p>If you like to copy that table, simply&nbsp;<em>right-click<\/em>&nbsp;on it on its name on the right-hand sidebar and&nbsp;<em>select<\/em>&nbsp;<strong>\u2018Copy Table\u2019<\/strong>:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/copy-table-data-view.png\" alt=\"Copy Table option on the data view\" class=\"wp-image-12418\" title=\"copy-table-data-view\"\/><\/figure>\n\n\n\n<p><em>Open<\/em>&nbsp;Excel and&nbsp;<em>paste<\/em>&nbsp;the table either by&nbsp;<em>clicking<\/em>&nbsp;the&nbsp;<strong>\u2018Paste\u2019<\/strong>&nbsp;icon or by simply&nbsp;<em>pressing<\/em>&nbsp;<strong>\u2018Ctrl\u2019<\/strong>&nbsp;+&nbsp;<strong>\u2018V\u2019<\/strong>&nbsp;on your keyboard:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/v1.nitrocdn.com\/MmRYricBGnwFelNvIykEOHWwZuUwjnwj\/assets\/static\/optimized\/rev-e6386bb\/wp-content\/uploads\/2019\/11\/copied-table-excel.png\" alt=\"\" class=\"wp-image-12419\" title=\"copied-table-excel\"\/><\/figure>\n\n\n\n<p>This method lets you copy entire tables including the&nbsp;<strong>DAX calculated columns<\/strong>.<\/p>\n\n\n\n<p>However, you might not want to do this with larger tablers. It takes more time to copy larger tables and paste them into Excel.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Limitations and Considerations in Exporting Data<\/strong><\/h2>\n\n\n\n<p>There are certain limitations and considerations in exporting data that apply to both the Power BI Desktop and Power BI Service versions as well as in all the plans including the Power BI Pro and Premium plans.<\/p>\n\n\n\n<p>Microsoft laid them out in detail in their&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/power-bi\/visuals\/power-bi-visualization-export-data\">Power BI Documentation<\/a>.<\/p>\n\n\n\n<p>Here\u2019s a summary of those limitations and considerations:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>You need to have&nbsp;<strong>build permission<\/strong>&nbsp;for the dataset before you can export data from a visual of that dataset.<\/li><li>The maximum number of rows that Power BI can export to a CSV file is&nbsp;<strong>30,000<\/strong>&nbsp;and&nbsp;<strong>150,000<\/strong>&nbsp;to an XLSX file.<\/li><li>To export underlying data, your version should be&nbsp;<strong>older than 2016<\/strong>&nbsp;and that the tables in the data model&nbsp;<strong>don\u2019t have a unique key<\/strong>.<\/li><li>You can\u2019t export underlying data if the administrator has&nbsp;<strong>disabled this feature<\/strong>. Exporting won\u2019t also work if the&nbsp;<strong>\u2018Show items with no data\u2019<\/strong>&nbsp;option is enabled for that specific visualization data to be exported.<\/li><li>The maximum amount of data that Power BI can export is&nbsp;<strong>16 MB uncompressed data<\/strong>.<\/li><li>Only data from&nbsp;<strong>1 table<\/strong>&nbsp;is exported if there\u2019s no relationship between the data even if data from that visualization came from different sources.<\/li><li>You can\u2019t export data from&nbsp;<strong>custom visuals<\/strong>&nbsp;and&nbsp;<strong>R visuals<\/strong>.<\/li><li>If a field is renamed in Power BI, it\u2019s possible that Excel would use the&nbsp;<strong>original field name<\/strong>.<\/li><li>Some&nbsp;<strong>Unicode characters<\/strong>&nbsp;may no properly display in Excel.<\/li><li>Administrators can&nbsp;<strong>disable data export<\/strong>&nbsp;in Power BI.<\/li><\/ul>\n\n\n\n<p>Ref: https:\/\/spreadsheeto.com\/export-power-bi-excel\/<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Written by:&nbsp;Kasper Langmann, Microsoft Office Specialist. Exporting data from Power BI to Excel only takes&nbsp;1 to 2 clicks!&nbsp; Though normally, it\u2019s the other way around \u2014 importing data from Excel to Power BI. Nevertheless, it\u2019s super easy and intuitive to do. In this article, we\u2019ll show you the step-by-step process in exporting Power BI data <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3199\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18,621],"tags":[667],"class_list":["post-3199","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","category-power-bi","tag-export-power-bi-data-to-excel"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3199","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3199"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3199\/revisions"}],"predecessor-version":[{"id":3201,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3199\/revisions\/3201"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3199"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3199"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3199"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}