{"id":1686,"date":"2019-02-09T11:37:43","date_gmt":"2019-02-09T10:37:43","guid":{"rendered":"https:\/\/didierterrien.cf\/?p=1686"},"modified":"2021-03-25T15:40:06","modified_gmt":"2021-03-25T14:40:06","slug":"export-data-to-excel","status":"publish","type":"post","link":"https:\/\/thebipower.fr\/index.php\/2019\/02\/09\/export-data-to-excel\/","title":{"rendered":"Export Power-BI desktop data to Excel"},"content":{"rendered":"\n<p>Export Power-BI desktop data to Excel with R. This R script exports a Power BI table automatically to an Excel (xlsx) file. This works from your report with an &#8216;Export&#8217; button. The maximum number of exported lines is 150 000.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to export Power-BI desktop data to Excel<\/h2>\n\n\n\n<ul><li>Install R and R studio. Please have a look at <a rel=\"noreferrer noopener\" aria-label=\"Ruth Pozuelo's video (Curbal) (opens in a new tab)\" href=\"https:\/\/www.youtube.com\/watch?v=HcTdXgWGdS8\" target=\"_blank\">Ruth Pozuelo&#8217;s video (Curbal)<\/a> <\/li><li>In some cases you may need to install Rtools which you will find at the same place than R<\/li><li>In R studio, install &#8220;openxlsx&#8221; package  <\/li><\/ul>\n\n\n\n<ul><li>In the desktop, create a new tab and name it &#8216;Export&#8217;<\/li><li>Add a R visual and enable R visuals when the popup appears<\/li><\/ul>\n\n\n\n<div class=\"wp-block-media-text alignwide has-media-on-the-right\" style=\"grid-template-columns:auto 22%\"><figure class=\"wp-block-media-text__media\"><img loading=\"lazy\" decoding=\"async\" width=\"168\" height=\"198\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/R-values-1.png\" alt=\"export\" class=\"wp-image-1691 size-full\"\/><\/figure><div class=\"wp-block-media-text__content\">\n<ul><li>Select this visual and, in the &#8216;VISUALIZATIONS&#8217; pane, add some fields under &#8216;Values&#8217;. These fields will be exported in the same order than in this list.<\/li><\/ul>\n<\/div><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<div class=\"wp-block-media-text alignwide has-media-on-the-right\" style=\"grid-template-columns:auto 22%\"><figure class=\"wp-block-media-text__media\"><img loading=\"lazy\" decoding=\"async\" width=\"177\" height=\"286\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/R-visual-title-1.png\" alt=\"export\" class=\"wp-image-1694 size-full\"\/><\/figure><div class=\"wp-block-media-text__content\">\n<ul><li>In the format tab, under &#8216;Title&#8217;, type &#8216;Export with R script &#8216;<\/li><\/ul>\n<\/div><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<div class=\"wp-block-media-text alignwide has-media-on-the-right\" style=\"grid-template-columns:auto 51%\"><figure class=\"wp-block-media-text__media\"><img loading=\"lazy\" decoding=\"async\" width=\"899\" height=\"491\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Paste-the-script-1.png\" alt=\"export\" class=\"wp-image-1700 size-full\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Paste-the-script-1.png 899w, https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Paste-the-script-1-300x164.png 300w, https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Paste-the-script-1-768x419.png 768w\" sizes=\"(max-width: 899px) 100vw, 899px\" \/><\/figure><div class=\"wp-block-media-text__content\">\n<ul><li>In the &#8216;R script editor&#8217;, paste the code down below. <\/li><\/ul>\n\n\n\n<p><\/p>\n<\/div><\/div>\n\n\n\n<ul><li>Change the &#8216;ExportFile&#8217; parameter in R script <ul><li>The file and its path must not include any accent<\/li><li>Path separator is &#8220;\/&#8221;<\/li><li>Folders must exist and not read only<\/li><li>The file will be created if it doesn&#8217;t exist<\/li><li>Extension must be &#8216;.xlsx&#8217;<\/li><li>The Excel file must not be open<\/li><li>If &#8216;ExportFile&#8217; parameter is empty, you will be able to chose the file or to create a new one <\/li><\/ul><\/li><\/ul>\n\n\n\n<div class=\"wp-block-media-text alignwide has-media-on-the-right\"><figure class=\"wp-block-media-text__media\"><img loading=\"lazy\" decoding=\"async\" width=\"629\" height=\"356\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Play-button-1.png\" alt=\"export\" class=\"wp-image-1704 size-full\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Play-button-1.png 629w, https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Play-button-1-300x170.png 300w\" sizes=\"(max-width: 629px) 100vw, 629px\" \/><\/figure><div class=\"wp-block-media-text__content\">\n<ul><li>When you are ready, click on the play button<\/li><li>The file path and number of rows will be displayed if exportation has been done<\/li><\/ul>\n<\/div><\/div>\n\n\n\n<ul><li><strong>Exported data are filtered according to the filter context !<\/strong><\/li><\/ul>\n\n\n\n<ul><li>If you get an error, check carefully the &#8216;Export file&#8217; parameter <\/li><\/ul>\n\n\n\n<ul><li>Once it will work, you may change other parameters according to your needs <\/li><\/ul>\n\n\n\n<ul><li> The R visual always removes duplicated rows. If you don&#8217;t want, you need to add an index column<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">R code to paste into the R visual:<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>### Export parameters #################################################################################\n\n# The file and its path must not include accent. Path separator is \"\/\". If ExportFile is empty, the file will be chosen.\nExportFile = \"\"\n#\"C:\/TP\/Extractions\/Automatic export test.xlsx\" \n\n# Sheet name\nExportSheet   = \"Sheet 1\"\n# Appends data to existing file or overwrites existing fle\nAppendToPreviousFile  = FALSE\n# Includes header. Better to set to false if AppendToPreviousFile is TRUE\nWithHeader    = TRUE  \n# Minimum days between 2 exports. 0 = no minimum. Very usefull if AppendToPreviousFile is true\nDaysBetweenExports    = 0   \n\n### Export functions ##################################################################################\n\nMessage &lt;- function(Text,Comment) { \n    par(mar=c(1,1,1,1)) \n    NoPlot = c(0) \n    barplot(NoPlot)\n    plot(1:1, 1:1, main = \"\", sub = \"\", fg=\"white\")\n    text(1, 1.3, Text, cex = 2, fg=\"white\")\n    text(1, 0.8, Comment, cex = 1.5, fg=\"white\")\n    return()\n}\n\nExtract &lt;- function() { \n    # require(tcltk)\n    #   button &lt;- tkmessageBox(title = \"Export confirmation\", message = \"Extract ?\", icon = \"info\", type = \"yesno\")\n    #   button &lt;- tclvalue(button)\n    #   if (button == 'yes') {\n    \n    #Message(\"Export in progress\",\"\")\n    \n    # Extraction\n\n    # openxlsx package (based on C#)    https:\/\/cran.r-project.org\/web\/packages\/openxlsx\/openxlsx.pdf\n    require(openxlsx)\n    excel = createWorkbook(ExportFile)                          # Create workbook (i.e. file) to put data in\n    addWorksheet(excel, ExportSheet)                            # Add worksheets to workbook\n    writeData(excel, sheet = 1, dataset, , withFilter= TRUE)    # Add data to workbook\n    saveWorkbook(excel, file = ExportFile, overwrite = !AppendToPreviousFile)    # Finally write out to file\n\n    # xlsx package (based on java)\n    #Sys.setenv(JAVA_HOME='C:\\\\Program Files\\\\Java\\\\jre1.8.0_202')\n    #Sys.setenv(JAVA_HOME='C:\\\\Program Files (x86)\\\\Java\\\\jre1.8.0_202')\n    #sessionInfo()\n    #require(xlsx)\n    #write.xlsx2(dataset, file=ExportFile, sheetName=ExportSheet, col.names=WithHeader, row.names=FALSE, append=AppendToPreviousFile)\n    \n    # WriteXLS package (based on Perl)\n    #require(WriteXLS) \n    #WriteXLS(dataset , ExcelFileName = ExportFile , SheetNames = NULL, perl = \"perl\", verbose = FALSE, Encoding = c(\"UTF-8\", \"latin1\", \"cp1252\"), row.names = FALSE, col.names = TRUE,           AdjWidth = TRUE, AutoFilter = TRUE, BoldHeaderRow = TRUE, na = \"\", FreezeRow = 1, FreezeCol = 0, envir = parent.frame())\n    \n    # Success notification \n    Message(paste(\"Export completed (\", RowCount, \"rows )\"), ExportFile)\n    \n    #SecondsToWait = 1\n    #require(tcltk)\n    #tt = tktoplevel()\n    #tktitle(tt) = \"Data export\"\n    #tkwm.minsize(tt, 400, 50)\n    #label = tklabel(tt, text = \"Export completed\")\n    #tkpack(label)\n    #Sys.sleep(SecondsToWait)\n    #tkdestroy (tt)\n    #   }\n    return()\n}\n\n#######################################################################################################\n\noptions(java.parameters = \"- Xmx1024m\")     # Extends memory\nMessage(\"Export not done\",\"\")               # No export notification \nRowCount = nrow(dataset)\n\n#Choose a file if ExportFile is empty\nif (ExportFile == \"\"){\n    Filters = matrix(c(\"xlsx\", \"*.xlsx\"), 4, 2, byrow = TRUE)\n    ExportFile = choose.files(default = \"\", caption = \"Select a file\", multi = FALSE, filters = Filters)\n    #, index = nrow(Filters))\n}\n\nif (length(ExportFile) == 0){\n        Message(\"File selection was cancelled by user\",\"\")             \n} else {\n    # No extract if the previous extract was made less than x days ago\n    ExportFolder = dirname(ExportFile)\n    if (dir.exists(ExportFolder)){\n        File_informations &lt;- file.info(ExportFile , extra_cols = FALSE)\n        if (is.na(File_informations&#91;1,\"mtime\"])){\n            # File doesn't exist\n            Extract()\n        } else {\n            if (difftime(Sys.time(), File_informations&#91;1,\"mtime\"], units = \"days\") &gt; DaysBetweenExports) {\n                Extract()\n            } else {\n                Message(paste(\"Export already done within \", DaysBetweenExports, \" day(s)\"),\"\")\n            }\n        }\n    } else {\n        Message(\"Folder does not exist\",ExportFolder)\n    }\n}<\/code><\/pre>\n\n\n\n<p>Each time the R script will execute, it will export data according to parameters and filter context<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to add an &#8216;Export&#8217; button to other pages (optional)<\/h2>\n\n\n\n<ul><li>Open the &#8216;Export&#8217; tab <\/li><li>Open the &#8216;Bookmark&#8217; pane<\/li><li>Click on &#8216;Add&#8217; and rename the bookmark &#8216;Export&#8217;<\/li><\/ul>\n\n\n\n<div class=\"wp-block-media-text alignwide has-media-on-the-right\" style=\"grid-template-columns:auto 14%\"><figure class=\"wp-block-media-text__media\"><img loading=\"lazy\" decoding=\"async\" width=\"173\" height=\"553\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Export-button-properties-1.png\" alt=\"export\" class=\"wp-image-1720 size-full\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Export-button-properties-1.png 173w, https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Export-button-properties-1-94x300.png 94w\" sizes=\"(max-width: 173px) 100vw, 173px\" \/><\/figure><div class=\"wp-block-media-text__content\">\n<ul><li>On other pages, add a blank button<\/li><li> In the &#8216;VISUALIZATIONS&#8217; pane, activate the &#8216;Button text&#8217; option<\/li><li>Type &#8216;Export&#8217; in the &#8216;Button text&#8217; field<\/li><li>Increase the &#8216;Text size&#8217; <\/li><\/ul>\n<\/div><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<div class=\"wp-block-media-text alignwide has-media-on-the-right\" style=\"grid-template-columns:auto 26%\"><figure class=\"wp-block-media-text__media\"><img loading=\"lazy\" decoding=\"async\" width=\"176\" height=\"226\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/Export-button-action-property-1.png\" alt=\"export\" class=\"wp-image-1725 size-full\"\/><\/figure><div class=\"wp-block-media-text__content\">\n<ul><li>Activate the &#8216;Action&#8217; <\/li><li>Select &#8216;Bookmark&#8217; type<\/li><li>Select &#8216;Export&#8217; bookmark<\/li><li>Optionally type a tooltip<\/li><\/ul>\n<\/div><\/div>\n\n\n\n<p>Now, you may click on &#8216;Export&#8217; button to open the &#8216;Export&#8217; page which will automatically export data to Excel<\/p>\n\n\n\n<hr class=\"wp-block-separator is-style-wide\"\/>\n\n\n\n<p>Download the report here :  <a href=\"https:\/\/github.com\/didierterrien\/Power-BI-assistant\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"'Export data to xlsx file.pbix' (opens in a new tab)\">&#8216;Export data to xlsx file.pbix&#8217;<\/a><\/p>\n\n\n\n<p><\/p>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n\t\t\t<div class=\"wp-block-uagb-post-carousel uagb-post-grid  uagb-post__image-position-top uagb-post__image-enabled uagb-block-cf4b5224     uagb-post__arrow-outside uagb-post__items uagb-post__columns-3 is-carousel uagb-post__columns-tablet-2 uagb-post__columns-mobile-1\" data-total=\"1\" style=\"\">\n\n\t\t\t\t\t\t\t\t\t\t\t\t<article class=\"uagb-post__inner-wrap\">\t\t\t\t\t\t\t\t<div class='uagb-post__image'>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/10\/30\/easily-export-to-excel-with-power-bi-sidetools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"414\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-1024x414.png\" class=\"attachment-large size-large\" alt=\"Export to Excel\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-1024x414.png 1024w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-300x121.png 300w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-768x311.png 768w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-1536x621.png 1536w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-1320x534.png 1320w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel.png 1854w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/>\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<h4 class=\"uagb-post__title uagb-post__text\">\n\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/10\/30\/easily-export-to-excel-with-power-bi-sidetools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Easily export to Excel with Power BI Sidetools<\/a>\n\t\t\t<\/h4>\n\t\t\t\t\t\t<div class='uagb-post__text uagb-post-grid-byline'>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<div class='uagb-post__text uagb-post__excerpt'>\n\t\t\t\t\t<p>While working in Power BI desktop, we often need to export to Excel some data&#8230;\t\t\t\t<\/div>\n\t\t\t\t\t\t<div class=\"uagb-post__text uagb-post__cta wp-block-button\">\n\t\t\t\t<a class=\"wp-block-button__link uagb-text-link\" href=\"https:\/\/thebipower.fr\/index.php\/2020\/10\/30\/easily-export-to-excel-with-power-bi-sidetools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Read More<\/a>\n\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/article>\n\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t","protected":false},"excerpt":{"rendered":"<p>Export Power-BI desktop data to Excel with R. This R script exports a Power BI table automatically to an Excel (xlsx) file. This works from your report with an &#8216;Export&#8217; button. The maximum number of exported lines is 150 000. How to export Power-BI desktop data to Excel Install R and R studio. Please have&#8230;<\/p>\n","protected":false},"author":2,"featured_media":2447,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_uag_custom_page_level_css":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"categories":[8,56],"tags":[11,20,21,24,27,63],"uagb_featured_image_src":{"full":["https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/export-button.png",1031,468,false],"thumbnail":["https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/export-button-150x150.png",150,150,true],"medium":["https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/export-button-300x136.png",300,136,true],"medium_large":["https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/export-button-768x349.png",768,349,true],"large":["https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/export-button-1024x465.png",1024,465,true],"1536x1536":["https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/export-button.png",1031,468,false],"2048x2048":["https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/export-button.png",1031,468,false],"mailpoet_newsletter_max":["https:\/\/thebipower.fr\/wp-content\/uploads\/2019\/02\/export-button.png",1031,468,false]},"uagb_author_info":{"display_name":"Didier TERRIEN","author_link":"https:\/\/thebipower.fr\/index.php\/author\/masterpower\/"},"uagb_comment_info":4,"uagb_excerpt":"Export Power-BI desktop data to Excel with R. This R script exports a Power BI table automatically to an Excel (xlsx) file. This works from your report with an &#8216;Export&#8217; button. The maximum number of exported lines is 150 000. How to export Power-BI desktop data to Excel Install R and R studio. Please have...","_links":{"self":[{"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/posts\/1686"}],"collection":[{"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/comments?post=1686"}],"version-history":[{"count":15,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/posts\/1686\/revisions"}],"predecessor-version":[{"id":3815,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/posts\/1686\/revisions\/3815"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/media\/2447"}],"wp:attachment":[{"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/media?parent=1686"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/categories?post=1686"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/tags?post=1686"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}