Power BI - Write Data to SQL 

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

I agree! It's lightweight and free.

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

No problem! Thanks for the comment

Haha thanks Hachi!

No problem, Peter!

No problem Jasa!

No problem Jorge!

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

@@BIElite have you found any performance difference?

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

@@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.

No problem, Muhammad!

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

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)")

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.

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.

@@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

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

@@noyraz indeed ,looking forward for it😉

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.

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.

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

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

Hey did you get any solution for this error?

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

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.

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]," ")

@@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?

@@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

Yes, Same case goes with me.

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

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

@@yoismelperez2744 nope I was not.

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

Yes it can! Any data source will work.

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

Scale could have been better for sure!

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

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