BULK Write Back to SQL from Power BI

  Рет қаралды 33,295

BI Elite

BI Elite

Күн бұрын

Пікірлер: 98
@ramyagopinath8320
@ramyagopinath8320 2 жыл бұрын
Perfect video that helped me write back to postgresql. I published my pbix and tried a incremental refresh so that every week I write back only new records to postgresql table which isn't writing back but the refresh is successful and its showing up new modified date in report
@rajarshisingh2547
@rajarshisingh2547 3 жыл бұрын
I have been looking to something as such to be done from power bi itself. My firm doesn't allow me to use powerapps for writeback to database. You just gave me a solution which will be very beneficial to me.
@irfanshaikh262
@irfanshaikh262 3 жыл бұрын
Its so amazing that when you need to find something related to MSBI and you get it on the BI Elite channel. Thank you so much for lending me this logic I've been struggling to find. Love from India❤❤❤
@BIElite
@BIElite 3 жыл бұрын
Haha glad to hear it, Ifran!
@shyamchandankalgutkar4720
@shyamchandankalgutkar4720 2 жыл бұрын
Its a great video and hats off to you for sharing this. In case there are multiple sources just make sure to set the Privacy settings to same such as Private or Organizational in both the sources. Many Thanks.
@dansilecchia7355
@dansilecchia7355 8 ай бұрын
It took a little re-jiggering for my purposes, but I got it to work. If you want to replace the data, you would some how do a drop table function at some point. Trying to figure that part out now. Thanks!
@SamundraDarion
@SamundraDarion Жыл бұрын
This has been BRILLIANT! I do have one request though - you created an ID column to load - but you don't cover the creation of that column, I'm trying to piece loose things together now.
@cristian.angyal
@cristian.angyal 3 жыл бұрын
WOW!!! That's a really awesome idea to write the INSERT Statement in a Custom Column and use a Custom Function to run it. Great work Parker! Thanks for sharing and stay assured I will totally "steal" this and use it in my Daily work ! Thanks again!
@BIElite
@BIElite 3 жыл бұрын
No problem! Yes, please use this!
@cristian.angyal
@cristian.angyal 3 жыл бұрын
@@BIElite I've just tested it getting data from a SharePoint list with Power Query in Excel and it totally works!!! This opens a lot of use-cases !!! Thanks again for sharing!
@BIElite
@BIElite 3 жыл бұрын
Love it, Cristian! Thanks for sharing your use case.
@VladMZ
@VladMZ 3 жыл бұрын
This is an interesting solution to my challenge to store historical copies of the data produced inside Power Query. I do have a problem though with initiating a new SQL session for every insert. It's not truly "bulk", and may be quite slow with a decent number of rows. I will try to move it outside of the function, and may be pass the session object as a parameter.
@BIElite
@BIElite 3 жыл бұрын
That’s a very good point… please let me know what you come up with.
@rajarshisingh2547
@rajarshisingh2547 3 жыл бұрын
Good thought, I shall also try the same
@christophertauss3640
@christophertauss3640 Жыл бұрын
Excellent demo. I am looking at something like this to insert records into an Access db housed in SharePoint. One concern I have with this approach is that there is no way (I am aware off) to write the record asynchronously. So if multiple users try to insert records at the same time, sometimes Access has real problems. But I am continually amazed by what Power Query can do. I would estimate that at least 50% of VBA code can now be automated with Power Query. I have subscribed and I look forward to your other videos.
@lekhasri2360
@lekhasri2360 2 жыл бұрын
This really helps a lot..I am actually searching and struggling for this option to write back to SQL where I have billions of records to be written back to SQL from my Power BI report
@TTTTT5050
@TTTTT5050 2 жыл бұрын
Hi i follow your step and script exactly but some how the message column doesnt show: "INSERT INTO FromView (rownum,Category) VALUES ( '" & Text.From([rownum]) & "', '" & Text.From([Category]) & "', ); SELECT 'Inserted Row' AS [Message];"
@michaelc2712
@michaelc2712 3 жыл бұрын
Parker, nice video to explain this. For many rows wouldn't it be more efficient to create a stored procedure in SQL and pass the rows as one JSON value, chunking into a reasonable number of rows?
@samaguire1
@samaguire1 2 жыл бұрын
yip - way more efficent
@Miketochy
@Miketochy 2 жыл бұрын
Kindly share what the procedure would look like Thanks
@laplasparadox3138
@laplasparadox3138 Жыл бұрын
Any follow up on this
@jorgedelgado4846
@jorgedelgado4846 2 жыл бұрын
Hi Parker, then we can publish and configure scheduled updates so that the process continues in Power Bi Service? or is only done on power bi desktop?
@tejasbrahmbatt5344
@tejasbrahmbatt5344 3 жыл бұрын
As always great video.. will it be possible to write data based on DAX conditions.. Let say the user interacts with the report and changes some slicers etc.. Then the final table | results should be written back ? Thanks
@harshitdoshi2004
@harshitdoshi2004 3 жыл бұрын
This is an excellent solution. We just have to mindful that we don't start using this method as semi ETL :)
@Pinkpalmpufffyslay
@Pinkpalmpufffyslay 3 жыл бұрын
Hi Parker, many thanks for sharing this excellent video. Just one question, would there be any efficiency issue if the size of the table is so big like 50000+ rows?
@nagendraveera1577
@nagendraveera1577 2 жыл бұрын
use ADF :)
@tomeering9228
@tomeering9228 2 жыл бұрын
Hi Parker, Thank you verry much for making this video. I have copied your code and when i publish it to PowerBi Service it is not able to refresh, is that something on my end or do you experiance the same? In the Desktop app it works great !
@jamesmundy3127
@jamesmundy3127 2 жыл бұрын
Parker - Great video. I applied to a local MySQL database and it worked like a charm. I had to modify my table a bit to create a unique constraint but everything is working fine now. Do you think this same approach would work with a snowflake database? Seems like a database server is a server and an insert statement is an insert statement. Might be a good weekend project. Thanks again.
@kevinwallst
@kevinwallst Жыл бұрын
Thanks for sharing the great video! Would this require the premium license of PowerBI?
@mohaarulez
@mohaarulez 3 жыл бұрын
What abour sql injection? Does power bi or forms handle that behind the scenes?
@BIElite
@BIElite 3 жыл бұрын
Very good point. No, it runs the “native query” against your database, so anything goes.
@majdyazigi8185
@majdyazigi8185 3 жыл бұрын
Great one, thank you, does it work with MS Access database?
@Grace-fz4og
@Grace-fz4og Жыл бұрын
You save my day! thank you
@bethkemp6934
@bethkemp6934 2 жыл бұрын
Can this be done using Power BI Report Server, or is this for the cloud/service only?
@abhishekstatus_7
@abhishekstatus_7 3 жыл бұрын
Excellent Solution as always by Parker ! Thanks a ton ! Your solutions keep us going back to check to implement something new in our report. Also would like to know if you are planning to have Power Apps Integration with Power BI, Financial reporting or NLP kind of implementation with the help of Power Apps and Power BI on KZbin! Thanks a ton again:)
@moindharwad1130
@moindharwad1130 2 жыл бұрын
@BI Lite I tried the above solution but at the end am getting please rebuild the data combination error.. At it is exactly pointing at invoked custom function step in the power query Bi Lite please guide us from here.. Thanks
@muhammadtanveerislam5998
@muhammadtanveerislam5998 3 жыл бұрын
Sir it is really good tutorial.
@BIElite
@BIElite 3 жыл бұрын
Thanks!
@MuhammadBerki
@MuhammadBerki 3 жыл бұрын
Great video Parker 👍
@diaconescutiberiu7535
@diaconescutiberiu7535 Жыл бұрын
After I write the insert statement and click ok .. i get null for about 90% of my rows. The odd part is ... that it worked for about 10% !? Any ideas?
@mad.z9064
@mad.z9064 3 жыл бұрын
It’s really awesome, and thank you for sharing! Can we write measures and calculated column values with this trick?
@ThankYouGoogle4
@ThankYouGoogle4 3 жыл бұрын
Logically you won't be able to write measures back using this method because it's modelled calculation. Any column you wanna write back to the SQL must be available in the querying data while applying this logic. You can invoke custom column while transforming your query and then add it in the function column created. That should work.
@iancockcroft9427
@iancockcroft9427 Жыл бұрын
Great video. thanks. gives me some ideas to work on. this isnt true write back? its just moving data from one source to another. is it possible for users to write a comment on the report and write that back. without power apps? I use a pagenated report with an insert query and pass parameters to it, but surely there is an easier way?
@gregamos7987
@gregamos7987 Жыл бұрын
Trying to take this process and modify it to write data to an sqLite .db via an ODBC connection. It works OK until the final step (Invoke Custom Function), where it fails with this message: Expression.Error: Native queries aren't supported by this value. Any ideas?
@thosewhoslaytogetherstayto3412
@thosewhoslaytogetherstayto3412 Жыл бұрын
Same issue here
@AnkitKshatriya
@AnkitKshatriya 2 жыл бұрын
Can this be done using Excel PowerQuery?
@bachirouadama5826
@bachirouadama5826 3 жыл бұрын
Very very interesting. Well done
@BIElite
@BIElite 3 жыл бұрын
Thanks!
@jimihieu
@jimihieu 3 жыл бұрын
Amazing technique! How do we add another column as insert timestamp as well please? And is it better to create that column in power query then insert to sql or use sql function while inserting?
@ThankYouGoogle4
@ThankYouGoogle4 3 жыл бұрын
Add one more column for timestamp in the SQL format you like (I prefer UNIX timestamp)... He's basically passing a SQL query, and it's always best to have insert timestamp at place where you're actually inserting data (this makes sure there isn't any discrepancy in case of insert failure for a specific row).
@jimihieu
@jimihieu 3 жыл бұрын
@@ThankYouGoogle4 im not good in sql, can you please help to expand the code please?
@ThankYouGoogle4
@ThankYouGoogle4 3 жыл бұрын
@@jimihieu add a custom column with hardcoded value for all rows as- UNIX_TIMESTAMP() Then just add the column in the m query to pass it just like other 3 column values are passed in insert statement. Use - "&Text.From([column_name])&"
@mre_man
@mre_man 3 жыл бұрын
Are you using MS SQL? What I did was create a date column datetime not null then set the default Value or Binding to (CONVERT([datetime],datediff(day,(0),getdate()))) in your SQL table . Whenever you add a new row it will automatically stamp the date for you. No need to pass or stamp the date manually through PowerBI or SQL.
@ShivShankar-kw1uc
@ShivShankar-kw1uc 4 ай бұрын
Kindly reply, if we need to replace data with fresh records however it must stay unique, what I need to write as a code.
@mre_man
@mre_man 3 жыл бұрын
A simple and very intuitive solution. It was easy for me to implement! thanks a lot and much appreciated.
@davidpimental6704
@davidpimental6704 Жыл бұрын
Is it possible to do updates in a similar way? What about Bulk Updates - such as every record with the same issue?
@kennynguyennz
@kennynguyennz 3 жыл бұрын
That's really cool. Thanks for sharing.
3 жыл бұрын
Great writeback! Thanks!
@mirrrvelll5164
@mirrrvelll5164 Жыл бұрын
Good one, can you also delete from sql too? I assume I need to delete some rows too... p.s your page BIElite is quite bad responsive....I cant access it easily.
@padreloco9733
@padreloco9733 3 жыл бұрын
Thank you again for an amazing video, useful. But I need more guidance to overcome a challenge I am facing: my Power BI is connected to a file that is adjourned every day. In other words, each file is replaced by a new file each day. The structure doesn't change of course. My question is: since I don't have those indexes to keep track of which row to replace or not, is there a way to make sure that I have replaced all old data in the "old" table with new data that just came? Thank you again.
@ThankYouGoogle4
@ThankYouGoogle4 3 жыл бұрын
If you are receiving files of same structure. Create an SFTP (legacy or custom) location and put original file there. Create a shell script to rename any incoming file to the original file's name and then replace the file. This way you'll always have a full load in Power BI. In case you can create an index which is highly recommended (maybe try using a composite key) to determine the changes, then go for incremental refresh on the composite key.
@padreloco9733
@padreloco9733 3 жыл бұрын
@@ThankYouGoogle4 thank you for your answer. Theoretically this answer seems to me feasible. I have to try it in practice though. Thanks again.
@padreloco9733
@padreloco9733 3 жыл бұрын
@@ThankYouGoogle4 Remaining in Power Query, during fiction creation, what if a code is added there with two purposes: 1- Drop the existing table (with data to replace, if it exists) 2- Create another table in the database and lastly load data...?
@ThankYouGoogle4
@ThankYouGoogle4 3 жыл бұрын
@@padreloco9733 I don't think i fully understood the requirement here because from what i understood is that you want to drop the data, and then load the new data. But that what a full load (refresh) does. Can you create your question on power bi community with your requirement and I'll be able to create a sample pbi file for you.
@Fabiano1223
@Fabiano1223 2 жыл бұрын
Astonishing !!!!!!
@tangtom2478
@tangtom2478 3 жыл бұрын
I guess this solution is also working on Excel, if it is, we can design a template in Excel, and ask user to input the data, then eventually save the data into data lake.
@Imrannaseem818
@Imrannaseem818 3 жыл бұрын
It’s really awesome technique
@julescaruso4398
@julescaruso4398 3 жыл бұрын
Excellent as always! Thanks!
@sameerulhaq4066
@sameerulhaq4066 4 ай бұрын
how to make it automatically detect the Item code and system time and datae and user email. I just want them to add comemnts and they cant edit the date time, email or item code? I have tables having relationship based on Item code?
@rakeshnag70
@rakeshnag70 3 жыл бұрын
Thanks for sharing. It is really helpful
@BIElite
@BIElite 3 жыл бұрын
Glad to hear, Rakesh!
@snehalgawari6756
@snehalgawari6756 2 жыл бұрын
But from which form we are writing back?
@AmitKumar-ox1qk
@AmitKumar-ox1qk 9 ай бұрын
can this type of query work with MariaDB too?
@Erebosaither
@Erebosaither 3 жыл бұрын
This is really great! Congrats and you have a new sub :)
@PatrickWallet
@PatrickWallet 3 жыл бұрын
This can be very usefull! Thank you for this tutorial.
@BIElite
@BIElite 3 жыл бұрын
No problem!
@ajaaskelainen
@ajaaskelainen 3 жыл бұрын
Something very new :) thnk you!
@ncbshiva
@ncbshiva 3 жыл бұрын
Is it possible to publish this pbix file and schedule a refresh in power bi service ?
@BIElite
@BIElite 3 жыл бұрын
Yes! That’s one of the great things about this. Scheduled refreshes will write data to the db on a schedule 8 times per day with pro.
@didijaba
@didijaba 3 жыл бұрын
@@BIElite can you show that also on video?
@jackstrudley1039
@jackstrudley1039 Жыл бұрын
@@BIElite Great video. In Desktop it's perfect but when I attempt it in the Power BI Service nothing happens (the refresh is successful but no write back occurs and I receive no error messages). Can you show this working?
@haribhaktapujari4763
@haribhaktapujari4763 3 жыл бұрын
Very useful. Thank you
@BIElite
@BIElite 3 жыл бұрын
No problem!
@thing72
@thing72 Жыл бұрын
it doesnt seems to write new rows when data set is refreshed thru gateway :( or its just me? when i open PBI report and refresh manualy, it makes records into DB
@anuradhavummaneni7372
@anuradhavummaneni7372 Жыл бұрын
how to get pbix file of this
@AmitKumar-ox1qk
@AmitKumar-ox1qk 9 ай бұрын
Hi there, I am getting following error - An error occurred in the ‘’ query. Expression.Error: Native queries aren't supported by this value. Details: [Table]
@sameerulhaq4066
@sameerulhaq4066 4 ай бұрын
but how will it take input from front end if I give to the user?
@jawwadchaudhry367
@jawwadchaudhry367 3 жыл бұрын
This is really awesome!!
@remib5863
@remib5863 3 жыл бұрын
I like the idea for the possibilities it brings in very specifics use cases but Ouch! I can already see Cyber Security teams pulling their hair out and having a heart attack with this kind of tricks! Hopefully people will understand what they are doing otherwise it might become messy. Power Automate might be a "cleaner" alternative to move data, not sure if bulk upload exists with this either.
@ncbshiva
@ncbshiva 3 жыл бұрын
Can you provide the PBIX file used for this demo ?
@BIElite
@BIElite 3 жыл бұрын
The PBIX would be very personal to my data and SQL table, but I will try to post something. Might not be too helpful though.
@infobymattcole7724
@infobymattcole7724 Жыл бұрын
Wow. Well done.
@trynewthings5927
@trynewthings5927 2 жыл бұрын
getting an error. I don't think you can do this anymore
@mudassirsyedrashidali9787
@mudassirsyedrashidali9787 3 жыл бұрын
I can't see Chris Wagner in the comments
@BIElite
@BIElite 3 жыл бұрын
Should we be expecting Chris?
@druthorah
@druthorah 3 жыл бұрын
⭐⭐⭐⭐⭐
@kaisquared90
@kaisquared90 2 жыл бұрын
You know what else starts with "SQL In-" ...
Power BI - Write Data to SQL
9:49
BI Elite
Рет қаралды 62 М.
Yay, My Dad Is a Vending Machine! 🛍️😆 #funny #prank #comedy
00:17
Человек паук уже не тот
00:32
Miracle
Рет қаралды 2,4 МЛН
А что бы ты сделал? @LimbLossBoss
00:17
История одного вокалиста
Рет қаралды 11 МЛН
😜 #aminkavitaminka #aminokka #аминкавитаминка
00:14
Аминка Витаминка
Рет қаралды 2,3 МЛН
What is Query Folding in Power Query in Power BI Tutorial (35/50)
11:57
Analytics with Nags
Рет қаралды 51 М.
Dataverse + Power BI: What you didn't even know you needed!
12:45
Guy in a Cube
Рет қаралды 61 М.
Yay, My Dad Is a Vending Machine! 🛍️😆 #funny #prank #comedy
00:17