Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I know that PowerQuery is great for consuming and transforming data, but are there any export options? If not, what's the least klugey way to get data out of a BI model and into SQL tables, or a CSV?
Solved! Go to Solution.
Consider making a feature request on the "Give Feedback" site.
@hendustu the Excel Add-on in https://www.acterys.com has a feature to load result sets from Power Query in relational tables.
Martin
You can also add an R script step in the query editor that writes the data set to a csv.
Here is the code.
Transform and load your data with Power BI, M and R | Anthony Bulk | Pulse | LinkedIn
# 'dataset' holds the input data for this script
# set the working directory to the location in which you want the transformed CSV saved
setwd("C:/temp")
# write out the transformed data set
write.table(dataset, file="transformed_data.csv", sep = ",", row.names = FALSE)
# put the data set into a data frame so that you can continue to work with it.
# I did an extra transformation and then built a report off of it
df1 <- data.frame(dataset)
Hi
Using R to export data out does seem to work.
But here is a step by step approach for those who are not familiar with R (myself).
1) Down load and install R ( probably need admin rights).
To install a MIcrosoft owned version of R click here download
Download the windows version ( will only work with 64 bit power bi).
Alternative, info on getting R
In power bi set up R, FIle -> options and settings -> options -> R scripting
Click on the How to install R link
2) In power bi set up R, choose menu in power bi FIle -> options and settings -> options -> R scripting
set your home R directory to eg. depending on your version browse to
C:\Program Files\Microsoft\R Open\R-3.4.3
also as shown here
3) Select a table you want to write to file in the query editor.
4) Select all column change to text ( optional step). to prevent type conversion errors.
5) Transform -> Run R Script ( if this is greyed out) step 2 failed.
6) Paste in this code. As supplied by jbulk
# 'dataset' holds the input data for this script # set the working directory to the location in which you want the transformed CSV saved setwd("C:/temp") # write out the transformed data set write.table(dataset, file="transformed_data.csv", sep = ",", row.names = FALSE)
7) press Ok, try for a small table . There is 1800000 millisec timeout 30 minutes.
I was able to export a table of 2 million rows to CSV.
😎
Check row counts. Open exported file in power bi, , Transform -> count row in query editor with your original table.
Hi Antony,
Thanks for R script, it helped me a lot.
Is there a way to add current date to these script?
So, when I refresh, it will automatically save the date with the day in the title like "transformed_data-06/09/2017.csv".
Regards,
Gozde
I would simply add it to the dataset itself: Add a custom column with: DateTime.LocalNow()
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
another option that does not require any third party is to use powershell, i tried the vba approach and it does not work with large data, R works very well, but in some environement it may not be available
the script is here
This is brilliant mim !!!
Re size-limitations: Change the export-format to txt (instead of csv) and these size-limits are gone 🙂
Thx a ton!
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
You can use SSIS to connect to Power BI desktop and use SSIS to export the data to wherever you like.
The method described here: http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-ssms/ can be used as well to connect from SSIS.
Another option:
With the December update you can use the workaround via R to export your data from Power BI to txt or SQL-server: http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Consider making a feature request on the "Give Feedback" site.
Thanks! If it gets approval, you can find it here:
No.
Easiest/quickest way is to go into your PowerPivot model, select the table, Ctrl-A, Ctrl-C. Open new Excel file, Ctrl-V, save as CSV
Hi,
now you can right click on the table, Select copy table, and then you can past directly in Excel spreadsheet.
Regards,
Mohamed.
Yeah, it's just that we're often dealing with very, very large datasets.
There's no way to pull a data model into access is there? I'd say that we could take our workbook and pull it into Tabular Services, but I don't think on-prem SQL Server can refersh Power Query.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.