BULK Write Back to SQL from Power BI

what's up everybody it's parker with bi elite in this video i'm going to show you how you can write back to sql in bulk from power bi about a year or two ago i created a video on how to write back to sql from power bi but it was limited in that you could only write back a single row or record at a time but since then i figured out how you can write back multiple records at a time so in this video i'm going to show you how you can connect to data and write it all back to a sql database table before we dig in big disclaimer there are tools that handle this better than power bi such as azure data factory or ssis but i like working in power bi you probably do too so we're going to learn how to do it in power bi i like doing this in power bi because power bi comes with a bunch of different connectors that we can use to connect to data it also comes with power query which is a wonderfully powerful tool to transform and match up data so those two key factors make this very attractive in my mind so with that let's go ahead and dive into how to set this up [Music] so i'm going to go ahead and open up power query and i've already connected to my data this data is actually microsoft forms data that i'm connecting directly to it's actually another cool trick on my channel if you want to check out how to connect to microsoft forms data make sure you check out the link down in the description so i already have these form responses pulled into power query and i've filtered down to three columns so i have the id of the response i have the start time of the response and the actual text feedback of that response so now i want to take these three columns and push them to a sql table so with that i'm going to go ahead and go over to ssms and i'm going to create a table so i'm going to create this table called survey responses and i've set up three columns that mimic what i have in power query so i have my id column it's an int column i have a start time column which is a date time two column and a feedback column which is a varchar max column so i'm going to go ahead and create this table and then i can simply select from it and we'll see that there are no records in this table so now back in power bi i need to create a column that is going to mimic an insert statement to be used on that new table we just created so we do that by going to add column and custom column and i'm going to call this sql insert and this is basically just going to be a long string that's going to concatenate some of our column values in order to create that insert statement i'm going to go ahead and copy something i've already written and we'll walk through it so basically it's just an insert into and i'm going to call the table we just created and specify my columns values and i'm going to basically concatenate the values for my columns so my id column is a number so we call text.from to turn it into a text value similarly we get text.from from our start date which is a datetime and then we are taking our let us know your feelings about the course column which is that feedback column and one thing specifically is i need to call the text.replace function on that feedback column and replace all single quotes with two single quotes since this is an open text field i have plenty of feedback responses that have a single quote and that is going to not work with our insert statement so we actually need to replace those with two single quotes and then i'm going to close off this clause with a semicolon and then select a text called inserted row as message and this bit here is just because i want our sql statement insert to actually return a little piece of data so after we perform the insert i want to return this little bit and i'll show you why in just a little bit so i'll go ahead and click ok and that's going to create our new insert statement so if i were to grab this and paste this in let's say down here and run this if i wrote this correctly it should work um so we see that i it's just showing the select at the very end but what it actually did was insert that value in so if i now select from my table i see that one record if i actually run this again i should see two records unfortunately those are the same records and there's going to be some duplication i'm going to show you how we can handle this later as well so let's go ahead and get rid of this and go back to power bi so now let's learn how to push these insert statements into that database and actually write those into the table so before i do that i am going to quickly only keep my top five rows so we can quickly show how this is working because i have you know several hundred rows of responses here so now we need to connect to our sql table so just like a normal way that you connect to a sql table in power bi so i'm going to connect to sql server and i'm going to grab my server so i'm going to paste that in here and my database which i know to be called mailchimp also if you want to connect to this database to build off of since this contains a lot of sample data make sure you check out our training at training.bea elite.com we give these databases away for free for our students let me go ahead and create a connection to that database as if we were pulling data from that table so i'm gonna create or i'm gonna pull from just a random table and we actually don't need this navigation step so we just need the source step is creating a connection to the sql database with our server and our database and now let's open this up and add just a couple extra lines here firstly i'm going to turn this into a function by creating an open and close parenthesis and i'm going to call the parameter insert statement as text so our parameter is a text parameter called insert statement hashrocket to make it a function and then one more line here i'm going to say the insert step is equal to value dot native query and this is going to take a target which is our source database table i'm going to pass in my insert statement finally let's return insert so to run through that again we're connecting to our database server and database and then we're running a native query against our source and we're going to run this insert sql statement so with that our function is done i'm going to rename this to be fx insert let's just do something like that and now back in our main table i'm going to add a new column i'm going to invoke a custom function i'm going to call this let's just call this insert we're going to use the function called fx insert and i'm going to pass in the column that is called sql insert so with that let's go ahead and run that hey everyone this is parker from the future uh one thing that i forgot to mention when recording through this the first time is you're going to want to go to file options and settings options and you're going to want to turn off having to run native queries or having to approve all native queries because each one of these records is going to have its own unique native query and it's going to ask you if you want to approve approve approve each individual one so uncheck require user approval for new native database queries if you want to just run these queries without your intervention then click ok and you'll be good to go and it's going to return a table let's go ahead and check to see what we have in sql now we see that it actually wrote all of those records back to sql that's really cool we do see those duplicates but that's okay so now one other thing i'm going to do is i'm going to open this up and make sure we return one of these columns because i'm fairly certain that you need to return a column back in order for the step to actually run with your normal power bi refreshes i'm not so sure about that but just for now i'm just going to return this column as well so with that we're done and if we were to refresh this preview over and over and over we would see more and more data being written to sql see we see all of these records being written several times so now let's go ahead and handle duplicates so in order to do that let's go ahead and drop this table and we're going to recreate it but we're going to add a unique clause here so that the id coming from my microsoft forms needs to always be unique if it is not unique or we try to pass in an id that is already existing in our table it's going to throw an error and if it throws an error it's actually going to create problems for our power bi refresh so we need to handle that in our sql insert statement so when we come to our added custom which was our sql statement we want to add a bit of logic to handle if it were to run into an error so that is why i have the second one here let's paste this in so it's the exact same thing except i have this extra little clause here so it's begin try and then end try begin catch in catch so basically it's saying try to insert all of these values in try and then if that fails you want to run everything between the begin catch and in catch we don't actually have to run anything it's totally fine we just need it to not run and then we're returning the same select statement afterwards so we result in something like this so i'll go ahead and take this and let's paste this in and try to run it so i'll run this part here so we see one row was written oh ctrl z so let's go ahead and see what we have in here let's try to run it one more time zero rows affected nothing happened no errors were thrown still just one record so we're perfect right there so now we're completely done let's go ahead and get rid of our step to keep first rows so we can have all of our data being returned to us let's go ahead and close and apply and let's let power bi load that data and it should load all of our records so all those records are loaded on our sql table we have 438 rows just to show you that this is actually working let's go ahead and create a new response record so i'm going to respond i'm going to say this course was awesome what a great course and click submit so that's going to write that response and now if we were to refresh we're going to see an extra response being added because we're going to basically grab the newest row write it to the database and now we're just going to return all 439 rows and there we go so we have 439 we see the most recent response was just now if we go over to sql and we select star we see our most recent record what a great course so it's looking great back to power bi so that is the entire trick right there so we're able to write back as many records as we want and we're also not going to run into issues with power bi refreshing and running that same query multiple times so i know this video is going on for a long time but i hope you got something out of it if you like this video make sure you hit the subscribe button it's the best way to show your support of the channel and helps me continue creating power bi content if you like the way i explain power bi concepts make sure you check out my training over at training.bealey.com with some awesome courses on power bi dax sql and alteryx and i will see you in the next video

Channel: BI Elite

Views: 29,884

Keywords: Power BI, Data Analysis, Data Visualization, Data Science, Business Intelligence, BI, Microsoft, Dashboard, Report, power bi write back to sql, write back to power bi, write back to database power bi, write back to sql power bi, power bi sql, power bi sql insert, power bi sql write back, power bi write data to sql, power bi data, sql database

Id: WgIhsF7kEjI

Length: 11min 40sec (700 seconds)

Published: Wed Sep 22 2021