Exporting to SQL

Watch the video below for exporting data from Power Query to SQL Server. Below are the links to the blogs and packages mentioned in the video:

Python Code

Install Python, Sqlalchemy
Setup Python in Power BI
Temporarily set the data privacy to Public. Be sure to change it back to the original privacy level
Delete the export query after you are done exprting to SQL server

#hide_output
import sqlalchemy


from sqlalchemy import create_engine, MetaData, Table, select


from six.moves import urllib


params = \

    urllib.parse.quote_plus('DRIVER=ODBC Driver 17 for SQL Server;SERVER=localhost;DATABASE=pbi;trusted_connection=yes'

                            )



engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect=%s'

                                  % params, echo=False)


connection = engine.raw_connection()


dataset.to_sql(name='from_pbi2', con=engine, index=False,

               if_exists='append') // or 'replace'

PowerQuery Custom Function

Copy everything between the dotted lines ( --- ) and paste it in the 'Advanced Editor'.

#hide_output
"""


---------------------------------Start Copy--------------------------------------------------------


let fn = (source as table, Driver as text, database as text, table as text, server as text, if_exists as text) as table =>

 let
    Source = source,
    
    // Script to Export Power BI to SQL database 
    // Author: Sandeep Pawar 
    // Visit www.pawarbi.com for details and 
    // Version: 0.1
    // Date: Jun 23,2020

    Run_Python = Python.Execute("# 'dataset' holds the input data for this script#(lf)# install pyodbc , sqlalchemy#(lf)import sqlalchemy#(lf)#(lf)from sqlalchemy import create_engine, MetaData, Table, select#(lf)#(lf)from six.moves import urllib#(lf)#(lf)# change SERVER name with your server name & database name with your database name#(lf)# For Driver, search odbc in Windows > Driver Use the driver installed there#(lf)params = urllib.parse.quote_plus(""DRIVER="&Text.From(Driver)&";SERVER="&Text.From(server)&";DATABASE="&Text.From(database)&";trusted_connection=yes"")#(lf)#(lf)engine = sqlalchemy.create_engine(""mssql+pyodbc:///?odbc_connect=%s"" % params, echo=False) #(lf)#(lf)connection = engine.raw_connection()#(lf)#(lf)#engine.connect() #(lf)# suppose df is the data-frame that we want to insert in database#(lf)#(lf)#if_exists: Use replace if you want to replace an existing table with named ""from_pbi"" You can use any table name#(lf)dataset.to_sql(name="&Text.From(table)&",con=engine, index=False, if_exists="&Text.From(if_exists)&")",[dataset=Source])
 in
   
    Run_Python,

documentation = [
Documentation.Name   = " Export to SQL",
Documentation.Description = " Power BI to SQL export Using Python  ",
Documentation.Category = " Table ",
Documentation.Source = "www.PawarBI.com ",
Documentation.Author = " Sandeep Pawar PawarBI.com  ",
Documentation.Examples = {[Description =  "Use this function to export a dataset imported in Power Query to a SQL Server database. You will need to install Python, Pandas, pyodbc and sqlalchemy for this code to work. If you get firewall error, set the data privacy to public for the purposes of exporting the data and then you can revert it back to chosen data privacy setting. Visit www.pawarbi.com or my youtube channel 'PawarBI' for details",
Code = "Enter SQL Driver: SQL Server Driver 17, 
database: pbi, 
table: 'from_pbi', 
if_exists: 'replace' or 'append'  ",
Result = " The script will export the data to the specified SQL server, under 'pbi' database in table named 'from_pbi'  "]}]
  
in  
  Value.ReplaceType(fn, Value.ReplaceMetadata(Value.Type(fn), documentation))



---------------------------------------End Copy-----------------------------------------------------------------------








"""