Power BI - Write Data to SQL

UPDATED: See this video for a better technique to write back multiple rows: • BULK Write Back to SQL...
In this Power BI tutorial, learn how to write your data back to a SQL database! I will show you how to query a web API and then store the results in a SQL Server database which you can then report off of! You can even set up multiple datasets on scheduled refresh to push your data to SQL every hour or even every 30 minutes!
Elite Power BI Consulting:
bielite.com
To enroll in my introductory or advanced Power BI courses:
training.bieli...
Connect with me on Twitter!
/ powerbielite

[@BIElite]

UPDATED: See this video for a better technique to write back multiple rows: kzbin.info/www/bejne/jZismaZ8bNB4oKs

[@MTUK1981]

Great info. Tried to look for a simple explanation of Power BI data to SQL Server and you've done it! Thanks 🙂

[@dannyaguirre2309]

This is awesome !!! I just created process to read SharePoint Online Lists and sink the data to the SQL Data Warehouse. I did have to modify the code as I need to run an insert for each row however Text.Combine() was the unsung hero. Passing the sql scripts as one large text tuple set, with a group by transformation. Thanks for explaining this !!!

[@MacPaulos]

Hey Danny, I'm very interested in the steps you did for this. This is exactly what I'm trying to do now :). Thanks for letting me know it's possible

[@JackOfTrades12]

I was literally trying to do this using workaround R script (which I'm not good at). I can't believe this is so easy.... Thank you for your video!

[@BIElite]

Good to hear, Jon! I've also tried with Python and R lol

[@JackOfTrades12]

@@BIElite have you found any performance difference?

[@thiagofraletti4220]

Amazing. This approach enable the usage of Power Query to one of the most useful cases of ETL. Thanks!

[@BIElite]

I agree! It's lightweight and free.

[@cristian.angyal]

WOOW! This is Pure Gold! Thanks for sharing ! I'm already thinking of at least two use-cases I have:)

[@BIElite]

Great to hear, Cristian! I'm looking for other datasets to use this for as well.

[@nehashahpatel1741]

What a mastermind trick Parker you show.. Millions Thanks

[@tommynguyen4253]

Wow, this is very eyes-opening. I am wondering how we can insert multiple values at the same time? The drill down took only one row at a time.

[@BIElite]

Good point, Tommy. You can get around this by converting your SQL column to a list and then using the Text.Combine() function to concatenate all of your rows.

[@brijendraydv]

@@BIElite Hi,thanks for the tip but it throws me error after list that cannot convert list to type text when i want to insert more than one record.Is it possible to make to more clear how to insert more than 1 record,thanks

[@noyraz]

its will be great to know how can we insert to sql more than 1 row

[@brijendraydv]

@@noyraz indeed ,looking forward for it😉

[@DreeKun]

I did it like this: let fxListGenerate = () => let Source = List.Generate( ()=> [Result= try fxRunSQL(0) otherwise null, x = 0], each [Result] null, each [Result = try fxRunSQL([x]+1) otherwise null, x =[x]+1], each [Result]), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandTableColumn(your column names), #"Removed Duplicates" = Table.Distinct(#"Expanded Column1", {primary key column}), in #"Removed Duplicates" fxRunSQL = (x as number) => let Source = Sql.Database(server name, database name), INSERT = Value.NativeQuery(Source, #"API Call and INSERT Data"{x}[Custom]) in INSERT in fxListGenerate Although it works, it isn't very efficient because it just selects all tables, combines them and then removes the duplicates. Now that I saw this, I'm going to try Parker's advice.

[@kirisko3067]

Very very helpful Thank you

[@Tegarcahya]

I'm using Oracle as a database and I get errors like this "Native queries aren't supported by this value." any suggestion?

[@mediocrestuff2783]

Hey did you get any solution for this error?

[@Bananathan25]

Hi, thank you so much for this. I can get the query to run, however it only runs for the first row in my dataset. Do you know how to get the Native query to run for each row in the dataset?

[@Bananathan25]

In case anyone comes across this, I've got it: Instead of right clicking and drilling down on the column, add this line of M code after the AddSQLColumn part: SQL = Text.Combine(#"Added Custom"[SQL],"#(lf)")

[@tenaciousdata9200]

This is a good one! Thank you Parker!

[@BIElite]

No problem! Thanks for the comment

[@zacharydrake7925]

Do you know how I can expand this to multiple rows on a tabel? Currently only getting 1 row to write when I need it to look through a number of rows.

[@BIElite]

Hi Zachary, see my latest video from today! It shows how to insert multiple records in a SQL db.

[@louism.4980]

This is fantastic, thank you! :)

[@timvollman9017]

Which statement actually pulls data from the API data source

[@kaushmat]

This is very useful. 😲 1 question. In this example, you are writing back the data as part of a scheduled refresh process. Can it be done in a non-scheduled manner (i.e., the user opens the dashboard at her own convenient time, and wants to write certain data back to SQL through Power BI). Is this possible?

[@BIElite]

Really interesting question. Yes, definitely. Don't set this up for scheduled refresh, and instead just refresh the dataset manually. If your end user doesn't have access to the workspace, you can leverage Power Automate to refresh the dataset via button click in your Power BI report.

[@saadnageeb4633]

Wonderful, its a very helpful Idea, Thanks a lot

[@padreloco9733]

Thanks for the video! Amazing. One quick question: I have created a dataflow in power bi. A colleague of mine needs this dataflow to feed its PIMS. Is there I way I can pass her this dataflow? Let's say like exporting this dataflow to MySQL? Your Idea will be much appreciated. Thanks.

[@yoismelperez2744]

Has anyone tried this and works in the Desktop but not in the service? I have an example that works fine in both sides but a new report I am working on is writing to SQL from the Desktop but not in the service, so just checking if we know of any limitations on doing this?

[@JonasArnout]

Interesting! Why would you not use MS Flow for this?

[@user-hu7ov6fi9y]

Hey Parker, thanks a lot for this video! Very informative.

[@michamuszynski6832]

Hi! this is very useful solution. BTW which of the dataset is your source for the raport? As I know there is no possibility to use multiple datasets for one report.

[@BIElite]

Dzień dobry Michał! Not sure exactly what you mean in this question. Could you elaborate?

[@michamuszynski6832]

@@BIElite I have understood that you have build three datasets 'DataInsert No 1,2,3' to insert data into a data base. Is your report refreshed once per hour as well? How are you able to connect three datasets to one report.

[@tejasbrahmbatt5344]

Hi Parker, How can I delete the current SQL Table before and then bulk insert all the data from Power Query ? Please help..!!

[@Rafian1924]

Hi, How do we do it if we have 1000 rows in a dataset table.

[@zxuan]

This is great! However it only works in Import data mode

[@deniseyu9015]

It's really cool and amazing! Excellent tips

[@HachiAdachi]

Ah-Mazing! 😲 Thank you, Parker!

[@BIElite]

Haha thanks Hachi!

[@yudhinugrahariyansyah9432]

i have multiple row to insert , it can work with text combine the list , but i got error if the data has single quotation mark in the text. any advice ?

[@plgmgua]

this is crazy good!!! thanks alot!

[@BIElite]

No problem, Peter!

[@ducthinhvu2167]

how did you do it can you share with me , thank you

[@vladimir.v.saveliev]

Thanks!!! It's amazing!

[@RahulNegiworld]

When I'm using INSERT I'm getting error i.e. Expression.Error: We cannot convert a value of type Table to type Text. Could you please suggest correct solution? Thanks in advance!

[@didijaba]

I had same problem, you need to turn AddSQL column to list and then use Text.Combine with space as separator. After that it works. AddSQLColumn = Text.Combine(#"Changed Type"[AddSQLColumn]," ")

[@samwong777]

@@didijaba After the AddSQLColumn step, I turned my SQL Column into a list. So does the Text.Combine step come in after I turn the SQL Column into a list? And what text am I combining?

[@didijaba]

@@samwong777 #"Create SQL expression" = Table.AddColumn(#"Changed Type3", "AddSQLColumn", each "IF EXISTS (SELECT * FROM tblHops_SatnoOpterecenje WITH (UPDLOCK) WHERE tblHops_SatnoOpterecenje_ID = '" & [tblHops_SatnoOpterecenje_ID] & "')#(lf)SELECT * FROM tblHops_SatnoOpterecenje#(lf)ELSE INSERT INTO tblHops_SatnoOpterecenje (DatumOpterecenja,Sat,Plan_MWh_Per_h, Ostvarenje_MWh_Per_h, tblHops_SatnoOpterecenje_ID) VALUES ('" & [DatumOpterecenja] & "','" & [Sat] & "'," & [Plan_MWh_Per_h] & "," & [Ostvarenje_MWh_Per_h] & ", '" & [tblHops_SatnoOpterecenje_ID] & "')#(lf)SELECT * FROM tblHops_SatnoOpterecenje"), #"Changed Type of SQL expression column" = Table.TransformColumnTypes(#"Added Custom4", {{"Create SQL expression", type text}}), AddSQLColumn = Text.Combine(#"Changed Type5"[AddSQLColumn]," "), Source3 = Sql.Database("server.xxxxxxx,12314", "NameOfDB"), INSERT = if Text.Length(AddSQLColumn)> 1 then Value.NativeQuery(Source3, AddSQLColumn) else Table.TransformColumnTypes(#table({"A"},{{0}}), {{"A", type text}}) in INSERT

[@gabrielkn]

Interesting .. would this be adaptable for a medium size table to be written back to SQL?

[@BIElite]

I'm not sure how this performance would look with scale. I imagine someone can come up with some better SQL to check if the record already exists, and then this should work ok.

[@gabrielkn]

@@BIElite I was thinking more on a flush and fill technique, definitely something to try. Thank you!

[@novak2033]

Amazing! Thanks a lot!

[@BIElite]

No problem Jasa!

[@rakeshnag70]

Can we use this approach with sap data sources ?

[@mehulbhatia5469]

Is it possible to writeback dax measure to sql server?

[@shashankjyothi7979]

Hi. Does anyone know if you can write to a SQL db from a power BI report that has been published to a workspace?

[@MTerkait]

Hello, and thanks for the video, but I'm looking to change a value in the table, give the user to privilege to click and modify the Value in the table, and updating it where it should reflect back to the SQL/Oracle Database.

[@davidlang8101]

I need this too, is there any way a user could do an entry, and to push this value back to the DB? Thanks.

[@hoke73]

Hi, this is great. is there a way to do the same against oracle database? I get error ORA-00933 while running several sql statements in one value.nativequery. thx in advance

[@roxanarodriguez1828]

Woa can be this done with sharepoint list? or excel?

[@BIElite]

Yes it can! Any data source will work.

[@roxanarodriguez1828]

@@BIElite Oh really? is it the same process? isn't it? thank you very much

[@lohitmm]

can you share this Power Bi file please for reference?

[@majdyazigi8185]

Great video, just a question : does this work with MS Access database

[@BIElite]

Not totally sure, but you might be able to test it fairly quickly!

[@jorgeeduardomoltenimoreno3048]

Nice Tip Man! thanks

[@BIElite]

No problem Jorge!

[@aswin131]

How to insert the values to sql database, if we have mutliple records to be inserted?

[@BIElite]

You would need to employ some logic to group the rows together instead of drilling down into the value like I do in the video.

[@mikapeltokorpi7671]

Thanks for the video. BTW: That's true "How to lie with statistics" classic in the first pic (showing 1,3 M to ... 1,3 M views) 😂

[@BIElite]

Scale could have been better for sure!

[@jcmv007]

Hi Parker, awesome video tried it out but got stuck on the Drill Down part as the values are not changing for new records, was only able to insert the one record. How can I get it to work?

[@Rafian1924]

Yes, Same case goes with me.

[@yoismelperez2744]

Yes, same here. James, were you able to find out how to get around this?

[@yoismelperez2744]

@@Rafian1924 same here, were you able to find out how to get around this?

[@jcmv007]

@@yoismelperez2744 nope I was not.

[@yoismelperez2744]

@@jcmv007 I just did. Need to use Result = Text.Combine(AddSQLColumn, " ") right after creating the list

[@MuhammadBerki]

Fantastic tip, thank you 🙂

[@BIElite]

No problem, Muhammad!

[@AdamSaxton]

Just want to call out to be very careful with doing any type of INSERT/UPDATE/etc... within Power Query. In the video he had a check to make sure to not insert if it already exists which is good. the statement can run one or more times - which could lead to problems. Be very very careful. 🙏

[@BIElite]

Hi Adam, thanks for the input. I tried to make this point clear in the video.

[@ertf7777]

Will the source sata will get affected if we write insert/update in power query?

[@rakeshpanigrahi577]

Thanks Sir

[@NikoSuomiUlkoilee]

Interesting!

[@amarnadhgunakala2901]

missed SQL double check

[@rubenbasco7554]

Like it, but looks so complicated...

[@Crisicus_01]

This is awfully hacky for something you can do with a stored procedure and an SQL job.

[@ПавелЛинев-ч7м]

норм 🙈

[@sonhuynhtien4744]

how did you do it can you share with me , thank you