How to use SQL with Power BI 💡 ~ End-to-end Demo with Sample Files

hello everyone Welcome to our monthly live stream this month we are going to talk about power bi and SQL integration and how to use these two technologies together to create powerful reports and visualizations for your audience before I jump into any further let me know in the chat if you can hear me loud and clear while you are coming up with that I do want to start off by putting in official welcome message on the screen welcome everyone to the stream and yes Mikel says yes very well that's all good that is all I need to know all right so as I mentioned today we are gonna look at how to take some SQL data that is in in the databases and then bring it into Power bi this is a little bit tricky to kind of follow along but if you would like to follow along I have got a sample data set for you that is available on my website but you would need to install it and use it with a database that you have uh in your computer or somewhere else for the purpose of demonstrations I will be using MySQL on my computer and then I'll demonstrate that so yeah but if you do want the particular thing check out the video description for a link to download the data or I have put that into the chat messages as well in case you want to grab it from there all right the the stream is now slowly picking up we have people from all over the world joining today you know I have been up for about 15-20 minutes and I was watching all the comments and messages from others let's let's pop some of these greetings uh Maria says greetings from Canada hello Marius uh and uh vinod says Anna Namaste Anna which simply means uh hello brother I guess hello our Namaste to you we know that as well uh and now sheep says chandu hasn't slept uh I I did actually whenever I have a live stream I would have like a patchy sleep because I wake up around 4 15 for four o'clock to get ready for the stream which starts at five but last night I slept and I actually got up at 4 15 I didn't even wake up in the middle of the night to look at my watch or anything so I slept all right but I think I need more sleep so maybe when when I'm done I'll go back and take another nap um and Anand says yes from Gujarat India hello Anand uh in the Vijay vasant says Niha from Shanghai hi hi Vijay Niha as well uh and Sarah says hello from Nama Bengaluru hello Sarah wow uh you know we have got like people from all over the world Jam says good morning from California uh Sri Lankan Tamil hey Jam uh nice to see you there and Randy is from Philippines awesome so while while you all are saying hellos I thought you know we can kind of introduce into the topic with a very small presentation that I have for you this will basically set the agenda for for the next uh 90 or 60 Minutes however long this takes uh and then you know we'll jump into Power bi so let me pull up the presentation here and that's not where I want you to go okay and yeah like I mentioned I got a small presentation this basically sets the agenda for us today you know sets the expectations right and then kind of helps us go through the whole thing so the topic for today is using SQL with RBI and the agenda is it's a four-step agenda will do we'll start with a quick introduction to SQL by no means this is like an SQL class I do have other videos on the channel if you want to learn SQL but this is basically kind of setting the ground and introduce you to the data that I will be using in this particular video so I'll do a quick introduction to SQL with uh with the database that I have on my computer using MySQL and then uh we will figure out how to get the SQL data into Power bi through through the connections so data loading and simple queries and then we will create a simple sales report you can once the data is in power bi you can use anything all the advanced ideas and Concepts that you are familiar with to build any complex stuff it all works but this is just to show you what happens after the data comes to is power bi so we'll create a simple report using some measures and visuals and once that is done we are going to understand how to make the queries Dynamic so that is if I want to parameterize my queries and set a variable within power bi or somewhere else so that if I change that variable my queries will update and the whole thing changes so how do we do all of that so those are the four points a b c d uh introduction to SQL how to get the data into Power bi how to create some report and then how to make the whole thing dynamic sounds good and [Music] then once this this is done during the process if you if you have got any questions like you know oh how does this query work or what if this happens or any of those kind of things use the live chat or the comment feature if you're watching it in the recording to to put a message with your questions and if you are asking a question unlike you know you're saying hello from California please start that with the queue so that I can watch for your questions in the live chat uh and then I can address them if possible stay on the topic again you know you can ask anything I'm not gonna answer other questions but if it is on the topic and if if I happen to either know the answer or you know we have time to address that I will certainly help you out with that have already asked a question and I didn't address it please do not repeat it just creates unnecessary uh you know clutter in the chat chat area so those are the ground rules I hope that all makes sense nothing too confusing and you know if you would like to support the stream and the channel you could use either super chat or super thanks and if you're watching it again on the replay you can use super thanks too send a support message or you know you don't have to do it if you if you don't want to that's all fine and don't forget you know if you're watching this live give it a like that way more people will uh you know discover this and use it and this is the data set that we are using uh I have a separate article on my website for learning SQL for data analysis uh and the same data in that article is what I'm using again this way if you have watched some other tutorials from me you know it's not too confusing to connect the dots so you can download the SQL file uh from that thing there using this particular link that is shown on the screen so this part this URL is in the video description go there grab the SQL file if you want to follow along or if you are watching it in future and you want to um you know follow those steps alternatively whatever you are seeing can be applied with any other SQL database that you have with your disposal all right so now that we have set that let's uh let's go and you know start by doing everything but before we do that we do have some some rituals and formalities in our live stream so I have to obey these rituals so let me know what what are you drinking I just made myself a nice cup of coffee and you know this is this is going to help me with with the live stream and keep my eyes open what is it that you are having we already start getting some questions so I might actually look at some of these but do let me know meanwhile sorrow says question since power bi isn't available on Mac will the steps be relevant to the power bi service they should be the limitation here is you will use a SQL connection that can be reached from Service as well here I'm just using localhost which means my computer's SQL but normally when you have service you would use a a cloud based server or or a SQL or MySQL or any other kind of server that that can be reached through through the service as well so that is the only difference it's just tricky for me to simulate all of that set up a server just so I can demonstrate this so I haven't bothered with that but the process is entirely same and uh CN Michael says how do I learn SQL using a machine where I can't install anything um that's a good question there are some websites where you can type SQL query and then the browser will run everything for you and show the answer alternatively you can also use SQL on a database that is installed somewhere on a server and then use the browser as a as a console to to get into your queries I can't really tell you what these are simply because I don't remember any of them but there are websites so if anybody knows such things where our friend can practice SQL without installing stuff post them in the comments in cookie says hello from Croatia chandu I have a job because of your tutorial so big thanks and carry on with the great work wow this is an awesome thing cookie thank you so much thanks for the love and congratulations on your job all right let's see what people are drinking uh Sameer is having black coffee and ranganath is having water and Jeff is having coffee for you three am en and tasif just finished dinner that's cool and a lot of people are having hot water Randy wall is having hot water oh what's with all this how I'm also having hot water it just has some coffee mixed in it and Rob is having toothpaste all right I hope it's not a regular thing Rob and monster says why don't you use Health Data everyone is using business data I love to use Health Data uh if you have got some good health data sets that are applicable and easy to obtain and not to like you know 700 columns kind of stuff let me know give me a sample later or if you work in health yourself and you you can part with some dummy data uh for for me to explore and share stories of how to use this on the channel send it to me or you know point me in the direction um M3 says can I then we use ssms instead of MySQL yes you can absolutely you can use any uh any kind of database the SQL file that I am providing as a sample data set is just uh a SQL file it can be used and installed on any kind of database I suppose it works on anything I have not personally tested it on others but I have people telling me that they have used it in others and you have got your own databases you can use the concepts on those as well and especially if you're using any SQL Server ones unlike misql or something power bi integration works even better because power bi and SQL Server are both from Microsoft so they play together very well whereas MySQL or Oracle or anything else because they're not part of the Microsoft ecosystem the integration doesn't feel like very tight hope that makes sense uh and Graham Bell is having Martini or awesome man Chase to you all right uh let's uh there's there's lots of questions already but because we haven't even started anything I don't want to kind of uh you know go and talk about all these questions so we will get into the topic we'll show some things and then I'll I'll hopefully uh it all makes sense um [Music] so let's uh I'm gonna use this kind of a mode where my face is there for a while but you know I'll eventually once we get into the thick of things I'll take out my face so that we can look more at the data so this is my MySQL workbench this is basically a console to access my database the database is running on my Local Host here that means it's on my own computer so I'm gonna connect to this and this is gonna just open up everything and you know we can we can write some queries here uh if we have done any other videos on my channel in the recent past you would have come across the awesome chocolates data set it's a made up company that sells chocolates in the data set contains of chocolate shipment data along with some Dimension information the dimensions are who our sales people are that is one dimension what countries we ship to what products we handle so those are the dimensions so here is the database structure that you can see on the corner here let me just so these are the tables that that we have here we have got sales which is the main or the fact table that contains all the shipment information and then the sales is basically in along with these other tables you can kind of explain so this is my underlying table structure now let's do a quick kind of like a 101 of SQL where will understand what queries we need to write to access the data so here is my simple query I think I'm gonna use a different table just because that way we can actually see all the results sales table has three thousand plus rows so it kind of looks busy so I'm gonna use select star from jio it simply means get me all the data from the table that is named jio right right now I'm using awesome chocolates database so you can see that this is actually uh this guy here is in the Bold format which means it is the database that we are using in the same same MySQL installation I do have other databases but none of them are being used so when I say jio automatically it will go to the jio table here but if you want to be more specific you can mention the full database name dot table name Convention as well anyhow when we type this and then run this using the execute command here it's gonna I thought that was the output one it's gonna execute that and it will tell me that these are the tables that I have in my geography table the jio table or so we have got ID geography and then the region so India USA Canada New Zealand Australia UK those are the six countries to which we ship our chocolate they categorized into these regions and this is uh those are the codes now let's see instead of jio I want to see uh all our sales in whenever you write an SQL query you know usually you terminate it with the semicolon and and then again you run it that will give you all the data now what happens is usually any kind of a console whether you are using workbench or SQL Server management server service or something else they are primarily used to play with the data they are not the final places where the data ends up they are used to usually explore the data so any query you run it will not show whole picture like if you if your data has 6 million rows it won't show all the 6 million rows because there is no value in that it will run it and usually it will limit to a number of rows so here I am limiting it to 500 rows here you can see this limit option there but you can change this to a different number I think I'm gonna take out some of these things now and then I just so this is uh the simple query now let's say I want to look at all our sales that happened that have a certain quality for example I want to look at all the sales that are in G4 so select start from sales and then we can use a where Clause where jio ID is equal to G4 because G4 is a text you can put that in single quotes or double quotes and then run that what this will do is it will give you a filtered version of G Sales where it's only limited to G4 so you can see that in the jio ID column here uh it will always be G4 irrespective of what we are looking at so this is where we are writing a query the query is Select star from sales and then introduce a clause or a condition through the where where geoid is G4 you can add multiple things here you can say geoid is G4 and and then you can say amount greater than 5000 so what that would do is it will take out some of these 427 2345 these guys will go off 11155 because they are not more than 5000. so if I run like this I'm gonna get all the transactions that uh all the shipments way that are sent to the geography 4 with amount in this many times when you are writing these kind of queries you may not know what G4 really stands for what you do know is if I go back to our original query select star from jio G4 stands for New Zealand so I don't really know what the code is for G4 but I do know that we are currently interested in the New Zealand data or we are interested in India data so that is the question that I would have in my mind not the specifics about is it G4 or is it G7 or P94 like that very hard to remember those things the real questions that you or your audience would be asking are more along the lines of give me all the India data or show me all the shipments that went to New Zealand kind of thing but within the sales table we only have G4 we don't have the concept of whether that is India or New Zealand whereas what G4 really stands for is held in the geography table or jio table so this is where we need to use a concept called joins so the way to think about joins is uh you know imagine you have two tables sales shipment sales table here and jio table here each holding one part of the puzzle sales table tells me that I have got G4 here G4 G1 G to G4 like that and jio table tells me G1 stands for India G2 stands for USA like that so then we take the jio column on both tables and then join them this is kind of like doing a lookup in Excel or within power bi if you have done data modeling you know you connect one column to another it's it's the kind of like the same concept and you can use SQL to do a join as well so we're gonna do a join and that's where we will conclude our SQL Journey because you can talk about SQL for any amount of time this is as I said it's not meant to be like an SQL session it's meant to be using SQL with power bi so we will I'll introduce the necessary Concepts but nothing more feel free to learn more using either the other videos on the channel or somewhere else whatever is your favorite way of learning so we'll go back here and now let's uh try connecting both of these so instead of this what we want to say is we want to write the condition based on the geography that we have in our mind so we will start by saying select and now you need to think a little bit better about these queries so from sales s so here I'm giving an alias to the table as s and then here I'm going to say s dot star it means everything in this table is so select s dot star from sales s and then we can say join jio G on g dot go ID is equal to s dot go ID this kind of looks funny but you know you you don't need to use these aliases and you can still write the whole thing but it is helpful especially if you have got long table names or whatever to use a simple Alias like this so that that comes up so now that the tables are merged here I can write aware condition where g dot jio is equal to Canada I hope it got this right let's try so we are looking for G3 and it will give you all the G3 data so here you can see that this is only showing me G3 information now that we have merged the tables and looking condition on to Canada so this is a very simple join where you take two tables and you merge them together to get what we want [Music] all right let's see if there is any questions uh sham says screen is blurred can you please check um I hope it's only blurring to you or for a few people as well I'm gonna quickly check if my uh it shouldn't be blurred because I'm not doing anything strange I'm gonna check here for the stream quality to my YouTube thing unfortunately I can't see anything here that the stream health yeah YouTube tells me the stream health is excellent so it could be something else that you're doing uh what I suggest is on YouTube where you're watching you have got the the quality or the gear icon at the bottom of the video click on that and make sure you set it to the highest which is 720p for this and if you're watching it on like a very giant screen or something probably it will be blurred anyway yeah but it's not blur for others so it's it's uh it's okay all right so that's that uh and there are some other questions I think those questions are better addressed once I jump into Power bi so I'll wait for them um cool yeah other people are saying there's nothing blurry about that so this is all good uh let's uh I hope the that introduction to SQL was kind of enough this is more than enough for what we are gonna do with our data from a SQL perspective but if you do want to learn more SQL feel free to check other videos or follow up more on this because SQL is a very powerful language that you can use to manipulate data talk to data update data insert data and all of that and it kind of becomes uh a good skill to have I'm not saying it's an essential skill but it's a really good skill to have I happen to learn SQL many many years ago and it has come in handy uh many times throughout my life for example um like back in college days I did a lot of SQL and at one point in my life I thought finally I'm done with SQL I don't have to write any more queries fast forward 15 years and again I'm using SQL and today after more five more years I'm still using a skills so it's a good language and it's quite easy to pick up and and keep on adding more things to your SQL [Music] um proficiency all right let's go into Power bi now and I thought I opened power bi open PowerPoint foreign I did actually prepare a sample file but I think it might be better if we kind of start from scratch and build everything so you can see the whole journey um and I'm gonna close out this before I do anything what I want to do is I want to show you what happens the very first time you connect because what power bi does is anytime you connect to a database and if this is the first time you're connecting it's going to ask you for login credentials or how to connect to that and then it's going to store that information so that the next time you go there it won't even ask you so because I've already connected to mySQL through my my my power bi a few times it has stored the whole stuff so it's it's not going to show you those additional screens so I'm going to quickly figure out how to clear away those uh connection settings so that yeah data source settings and so these ones I'm gonna clear these permissions and then we are going to start again uh and so we'll say get data more and and then search for the SQL that you have you can type SQL here it will show you all the SQL databases that it can hook up to my thing is in MySQL but you could use anything if you have got something else like uh Oracle or whatever you know you can use those uh those things as well so search for your your database and then connect uh I'm gonna use MySQL and can cut to this and then this will ask you where is your server what database you want to hook up to my server is on localhost and if you want you can specify the port number and everything so it's on 3306 that's usually the default Port but again about 9 out of 10 times you will not hook up to a local host you will look up to a network server or or a Cloud Server so whatever it is you will mention that and then awesome chocolates is the database and we can use the advanced options we are going to come back and use this Advanced a little later in the day to parameterize our query but for now this is NF and then I'm gonna say okay and because this is the first time I'm hooking up to this this database I will it will ask me how I want to get into this database do I want to use my windows credentials or a username and date password for the for the database so whatever it is you can use that I'm just gonna uh use my connection and connect to that and then eventually once you connect it's gonna show you what is there in the database so these are the four tables notice that it's adding that awesome chocolates dot at the beginning but the table names are same it has people products sales and then also geography so it will show you what it can fetch from that thing and then if you want you can select everything if you have got uh some sort of actual connection set up in the database with constraints and whatnot you can use the set select related tables I have not bothered setting up them like that so if I pick one it won't pick up the other so it's easier for me to just hook up everything and then straight away load the data because there is almost no need for you to do any transformations because the data is coming from a database it's usually in a clean and nice format the only thing that you may want to try is try and transform it so that you can take out some columns or whatever but we are going to do all of that through SQL later so we are just going to load it and and then this just basically flickers the screen connects to the data brings it and then loads it into Power bi foreign my tables are all loaded they they all show up here in the corner um and if I go to the model view power bi has done a fairly decent job of even connecting the tables and building my star screamer so it has done all these four tables are related it has identified that it has connected them nicely everything works beautifully I'm just wondering you know if there is a way for us to get rid of the awesome chocolates part and just get into the jio thing I'm gonna go and edit the query If someone knows what to do let me know because I I don't really uh do this that much so sometimes all right looks like there is nowhere it is parameterizing that okay while I'm here I'm just gonna take out the thing and edit the names we could have done this from the UI UI itself without jumping into Power query by simply double clicking on the table and renaming it there but it doesn't matter where you rename it you know we have now kind of shortened the names otherwise the names are looking quite clumsy and long while while we are there let me look at some of the questions to understand you know if there is anything that you are confused or not not clear let me know I'll I'll be happy to help with that um so I don't want to mispronounce this but I want to say hung if I use SQL workbench what can I connect to uh you can connect to I mean it doesn't if you are using SQL workbench I guess that sounds quite generic what SQL is this but whatever it is whatever is the owner of the provider of the database you will be able to connect to that yeah try try it out um and this is one question that I have seen a few people are asked not related to anything that we are doing here but kind of comes up every now and then which is will you will this be available for me to watch later on on YouTube or whatever in all likelihood yes this is uh something that I I don't make any promises on but yeah it should be on on there unless something terrible happens in the Stream and then I feel like oh this is really bad quality I don't want to put it out there then I'll take it out but yeah it it might be there it would be there uh and uh oh sorry this is uh Malik says can you share the latest oh okay this is not related at all oh so it is asking you to connect install a connector there um it's possible that you may not have the connector installed if some custom installation is done and somebody unchecked that or whatever so yeah maybe install it see if you search on internet with that exact message you will find what else is required to install and then install because then then you can hook it up and Girish says is there any restriction to store the number of Records in power bi while connecting to SQL tables technically there is no restriction I mean probably there is we don't really know usually you can build very very large data models and and work with it it just all boils down to how much your computer can handle uh or maybe what is some of the internal variables they have used but yeah I personally I haven't had any issues I I was able to hook up to very large data sets and uh and use them to do analysis within power bi and I want to give a shout out to Imran uh for for the super sticker there thanks Imran and uh and Shivani keeps asking this question even though it's kind of not related at all this is what I mean please don't repeat your questions because it's just a completely derails says what does lock and unlock mean in SQL I mean you can just search on the internet but basically lock means lock unlock means let go so because sometimes when you are doing an operation on a database you don't want others to come in and do something else while this data is being changed so you lock a table you finish your thing and then you unlock but there's probably more other more variations as well so Paula says do you need username passwords and other parameters to connect they are required but they are not stored into the connection so if I see edit query here and then go to the source step it's not going to show me what password it has used here it simply says mySQL database server name database name written returning single database is true because we we point to a single database so it's saying give you only want that database so that's all there is but the actual connection parameters are stored as part of your power bi environment so they're not exposed into the MySQL sorry query code but it is part of my my power bi settings so it stores somewhere but I've shown you that bit as well like we need to enter something either you have to say what is the password and user username and password or you can also link it up to your Windows settings so if you have got a a database that that you just connect with your Windows ID and then it just responds many many organizations set it up like that then you can use that so yeah that's that uh and uh Bartram sends super sticker as well thank you Bartram for that um this is funny I'm not gonna answer that uh prasanna say something uh we will come back to some of these other questions at a later point but the data is into Power bi so that that is Step number one and two done one is introduction to SQL two is bring the data into Power bi we got the data here uh now let's uh let's build something simple here so that we can see uh how this data can be analyzed into Power bi and then we will figure out how to parameterize the queries uh that is right now what we are doing is we are not even writing any SQL we just said data is here go and get it so all the data comes through but sometimes you don't want all the data you want to bring only latest sales or you want to bring only shipments where more than 20 boxes of chocolate went out or something like that or all the uh all the orders that are fulfilled or something like that so whatever may be that query we will need to write that query into the connection so how do we do that that is the after this so whatever I'm going to do now then that comes up so here let's just build a very simple sales report one thing that I [Music] um I want to kind of add some extra information here rather than just make a bunch of things is we'll make the sales report look nice and good as well so that it's easy on ice when it is presented so I'm gonna start by introducing some background you can use one of the colors if you want um you know like this in fact this is something new so not Penny would have noticed but with February release of power bi they have actually added a few more color themes these are called accessible themes which is quite helpful especially if you're building reports where you want to make sure that these reports are readable to audience who have some accessibility issues so have you any of you tried out these these new themes they look quite interesting uh but also you can change the theme to quickly switch colors I'm not going to use the color option I'm gonna uh use the image option here instead what this does is it will get me here let me just quickly bring a background that I have and you know bring the background so that there is a background on my report that way the report kind of looks better rather than the white background and then here what we want to do is we want to do a very simple sales report so I want to build some measures right click new measure everything else from here onwards is exactly how we we normally do it so new measure and then within here we're gonna say total sales is equal to sum of sales table amount column and and then so that measure is here now one more measure this measure would be total boxes is equal to sum of boxes column in the thing so we have got both of these uh I'm noticing something funny when I look at what is being played do you see my mouse pointer all right when it is streamed so let me know in the comments because I think something funny is happening anyhow so we got two measures okay I think it's just my browser playing something funny so we got total boxes and total sales and then let's add one more measure which is shipment count each row in the sales table is one shipment so we just count rows of the shipments sales table and we will get that these are really simple measures nothing complicated here but if you want you can add more complex measures as well I'm going to introduce one extra measure just for the sake of adding a little bit of complexity which is in awesome chocolates we have a measure on how many shipments have a very low number of boxes so for this I think we can go back to here and then do a query if I do a select start from sales order by boxes what order by does is it arranges the output in ascending or descending order and then run this so we can see that quite a few shipments have zero one boxes three boxes like that the highest we would have done is again this data is only cut off at 500 rows but at that point we have reached 34 boxes so there's many times we sh we send these sample chocolate where it's just a box of chocolate because the customer is not really sure if this is the product that they would they or their shop would enjoy so they'll ask you know hey send us five boxes of Albert choco we want to try it out so we shipped that out but we'd want to keep a handle on such box uh low box shipments because every shipment costs us some money fixed amount of money we don't want to waste too much money on this so this is a this is something that we would like to keep track of so for example what we want to do is we want to see uh select star from sales where boxes is less than 50. so these are all the all the shipments where we have less than 50 boxes go out and then we want to see what proportion of our overall shipments are these kind of shipments so we call these as low box shipments and we want to see what is that proportion looking like so we could build a Dax measure to do that this is nothing but a kind of shipment count so the shipment counts everything what the what the low box shipment tells us is it is a kind of shipment count but it just counts the shipments where the number of boxes is under 50. and we could do this kind of thing with calculate so [Music] low box shipments is equal to calculate shipment count sales boxes is less than 50. notice that you know this is kind of like SQL now here the conditions that we write are the same conditions that we write in SQL in SQL you're showing the detail but here you are doing an aggregation and telling you how much is the count so we will have that finally we could create another measure the actual kpi for low box shipments so we call this as lbs percentage is equal to divide low box shipments with all shipments and then apply a percentage notation to this so that that comes up so now that these things are there we can kind of pop them into the screen I'm gonna put one card here to kind of show you what the process is total sales if I put that there comes up there it looks uh just like a number so I'm gonna apply some currency formatting and then that that will look like that so once this is done I'm just gonna apply some quick formatting to make sure that it looks nice because we don't want to spend too much time building the report uh but we'll do a little bit of formatting so again you can kind of get familiar with some of the things that are available so I'll go to the format and from here change the font to the the Bold one let's make this uh 24 points if it's too small maybe let's just make it 26. and and then yeah that looks all right and from effects I'm gonna because we have got a nice gradient background there I want to make the actual card background itself 50 transparent so that it kind of Blends into my output there and if you want you can add some additional effects as well like you know I can add a shadow effect or visual border and whatnot I'm not going to bother with any of those but that's that so once that is there I can copy paste these for for each of our metrics so this one would be total boxes in again total boxes I'll make it a whole number and then this one will be shipment count which will be a whole number and then this is going to be lbs percentage so this is my overall information for the all the sales that we have pulled through and then here we could show some additional graphs or or visuals to break this down by for example geography or salesperson or product or category or something like that so let's make a a visual here that tells us what's happening by our geographies we operate in six geographies so again we could use one of these visuals to tell that story I'm gonna use a uh let's go with the bar chart and let's make it this big and here in this bar chart we're gonna put the Geo onto y-axis so one geography per here and then let's just see what's the total sales looking like so this is how it looks it looks nice in clear and then you know if you want you can add some additional detailing here uh based on what you want to say um I'm just trying to make up my mind how much we need to do in this and then we'll copy paste this and here in this I want to show this overall sales Trend as well we haven't got a calendar table or anything here again we could either use SQL to make it or you could use a power query to generate it or you can bring it from some other source and merge them together but I'm just gonna be lazy and I'll turn this into a line graph and use the sales state to to do this and then take out that and you know that that's the sales Trend um it kind of looks at too too much noise so I'm gonna take out the day level and quarter so we look at the monthly sales Trend so this is my total sales Trend and that is how it gets broken by geography let's apply the same kind of formatting principles on these guys you can spend a lot of time formatting them but what I will do is I'll do some quick adjustments starting off by so x axis doesn't have a title anymore y-axis will take out the title on that and then line itself let's just uh just that we go here effects 50 percent 50 transparent now I see that with our orange color background the blue color may not be in the best one so I'm gonna go and uh search for color and that looks better Parker and [Music] it almost looks like Christmas colors there I wonder how we did what this what it was okay let's just go with that and then this uh I want to go and again do 50 percent and change the colors one good idea that you can take and apply for any reporting that you do in power bi is if you are using a color like for example total sales here is in the black color then it's a good idea to stick with that same color throughout your report so that people when they see black they associate that with total sales I'm not saying black alone you can apply the same concept but with blue as well so pick a color and then use the same color for the same measure wherever it is reported so that you don't have to over Legend your report and still get the same thing so because we went with black I'm gonna go here and apply the black color it might look too strong uh that that kind of looks like ah so I'm just gonna tone it down a bit and kind of go with that but if you think this looks too much like a monochrome thing you can try out different colors but some of the other easy wins are the title stem cells the default title power bi puts is kind of clumsy so I'm just gonna type my own title or you can generate the title through conditional formatting as well this goes a little bit too much so I'm just gonna type the title here I will just say sales trend and again synchronize these fonts let's go with some a bold and all right so this looks uh kind of decent not the greatest but at least you know it kind of tells me what's happening and here we could make another something that tells me something else we'll keep it quick and simple with a table visual that shows us who our top 10 people are so here I can bring my people sales person goes into the columns and then total sales total boxes lbs percentage and then quickly get rid of the total rows go to grid and options and let's just change the row padding to three pixels and then make this maybe 12 so that it's readable and and then once these are done we can kind of clean it up a little bit more for example again same ideas as earlier make it 50 transparent but it's not enough that you do it you have to do the colors as well so so gonna expand everything and then from here okay I think that looks enough foreign and finally we'll go here and then say where sales person is all we can set this to stop and top 10 sales people by total sales apply so that this comes up here I think at this point you can kind of make the thing more spaced out that looks all right uh you know we could probably polish it even more but let's just call it a date there and we have got this nice report let's slap a title on the top and sales report for awesome chocolates okay so we got a report nothing too fancy but at the same time nothing too simple either you know you can interact you can see what's going on you can get into a specific month look at all the patterns and whatnot um now that we have all done this you you think oh this is good I'm Gonna Save this and um and then email the store boss just as you are about to go and share this file with your boss she comes up in and says oh this looks good can you make this but just for uh you know our bars category of products or our specific product like we got all these products I'll show you what products we have we have um all of these products here let me just take out my face and so we have all of these products let's say she wants to know how that report would look like for eclairs okay so that exact report just for eclairs he'll be like oh man I just spent the whole afternoon building this now I have to create the whole thing again well you don't have to because the data is coming through my SQL we could kind of change the query and then build a new query that gives me just the eclairs data so let's see how we could kind of change that and and build it all out so remember when we were connecting to this data so we said get data and then MySQL we just use it server and database but there is an advanced option when you expand you can actually write a query here okay we didn't write the query but we could have written a query so all we need to do is write a query that will give me the data for eclairs so for example I could say localhost tingle support number [Music] and then select star from sales where PID okay let's just use PID directly we could also write a join and do all of that but I'll be lazy here and and then let's use uh p04 so if I write a query there I'm gonna get all the data that corresponds to just p04 you can see from here uh this is the only data that I'm getting none of the other products data comes through so I could potentially load this I'm just going to load it anyway it's it's not necessary this data and it will complicate the data model as well because they know the p01 that that query needs to be connected and all of that but you can see that the query one is now in my system here but what we want is we don't want to hard code anything because in that query if I edit it you'll see that the source step here has the query but it also has the hard-coded product value p04 so we don't want to do anything like that instead what we would like to do is parameterize it so that if our boss changes the mind we can quickly change this and produce the report for something else so that is our next next mission in this in this challenge how to parameterize this there are a bunch of fun ways to parameterize things within power bi before I do that I'm just going to see if there is any uh questions or anything that I missed out because I feel like I haven't looked at this screen let's see read what Girish says Greece says can I store the SQL tables in one power bi report and second power B report to connect to these tables the idea is to store visuals and SQL tables in two different reports uh not really sure what your end goal is but what you could do is you could create a data set so you can generate a power bi data set that contains all your necessary data queries and you know model and everything and then publish that data set and then you can then use the same data set from multiple reports so that will give you a better control because what the data set holds is it holds the necessary query logic all of that and it is centralized so then not only you but anybody else in the organization can also subscribe to this data set and build reports on top so that that is one option if anyone else has a better idea or something feel free to share saurabh says any change in sqldb I'm assuming that that data will be refreshed in power bi it kind of is so if my SQL changes I just have to trigger a refresh from Power bi either manually or schedule it so every night it refreshes or something and the the reports will be updated um or Moto milola sensing a super sticker as well thank you so much for that and narcos says awesome thanks Sensai chandu your contribution to the community are very much appreciated thank you so much I'm really glad you enjoy it and uh and he asks why are you doing this why don't we use a page or report filter we could but the whole idea is to introduce you to the concept that we can parameterize the queries that way what happens is if I put a page or a report filter the data still has to come to power bi so we are unnecessarily fetching the data that we no longer need so it is to avoid bringing anything here so that you can speed up things here so it's a good idea to take only what you need not anything extra and put it through per query power pivot and all of those additional layers simply because that just slows down the whole thing I mean it's fairly optimized but it's a good practice to not take any excess data than you need so that it is minimized all through the network so yeah and good thing to learn as well um Vishnu says how do you actually pick the people visualization pane okay so this is a a different thing altogether I talk about this in other videos too so I'm not going to go into that but I will uh probably have a separate video on this topic of how do we pick the visualizations in a future video Paula says if you share this with your boss won't she need the connection of course she needs the connection here this is like a made-up scenario so everybody is on my computer but in in your world you wouldn't be connecting to localhost to begin with you will connect to a database in the database would be reachable by your boss as well so you don't need to worry about the connection issues all right so those are the comments but if you do have anything else feel free to put that uh in so here uh going back to where we were before I started looking at the currencies how do we know make this a a parametric thing so you can build parameters in any number of fun ways I'll show you kind of like the semi easy option it's not the easiest but it's also not the hardest which is within power query itself you can create a parameter so here I have got parameter options in the home screen of power query manage parameters and we could use this to bring in arbitrary parameters like product code or client number or invoice IDs or whatever and then use them in our our Transformations so I'm going to click on this and make a new parameter this will be product code and then set the current value to p04 okay so the parameter name is product code notice that there is a space and everything in there and then add that so here it will add to your queries panel product code it kind of looks different to this uh this has a separate kind of a logo compared to the table logo that we have here and what we want to now do is use this parameter as part of the query that we are building so query one has p04 hard coded where it says it is hard coded we would like to bring the product code into that at this point you do require you the power query formula bar if you don't have the formula bar then it kind of becomes hard to do this so to enable this you need to go to the view ribbon of power query and then check the formula bar option here this is a one time thing you do it and then it will be part of your power bi every time you get into Power query you will see the formula bar so once you have the formula bar and once you have got a parameter that is set to p04 you can go to the editor here and and then from here where it is hard coded to p04 I'm just gonna take out that and then leave the two single quotes two double quotes Ampersand Ampersand and bring the parameter into the middle so the parameter is product code if you type hash it will give you the parameter you don't need to type hash if there is no space in the parameter name so if it is just called PID you can directly type it it will work but because there is a space I'm just going to use hash product code like that so I'll just expand this whole thing out so we can kind of read this that's how my query now becomes so it is saying select start from sales uh hashtag LF which is next line where PID single quote is my parameter and then close that so at this point what you are doing if you pay careful attention is you're taking a value from your power query and then you are passing it to the Square SQL or any other SQL Server there so this is called crossing the Privacy borders so imagine you know back in code time so governments and local authorities had this word called Bubble stay in your bubble so your bubble is you your wife your kids your family that is your bubble your neighbor's bubble is their family so you don't mix the bubbles so think of this like a bubble MySQL tables through the connection that you are making are all in one bubble they can talk to themselves it's all fine your local Excel files are in one bubble your text files are in one bubble your internet connections are in one bubble but anytime you go from one place to another like the parameteries in in its own bubble MySQL is an in its own bubble you're taking the parameter from here and going there power query doesn't like this so it will give you a privacy warning saying you know you're crossing the threshold of this and going there and you can bypass that you can say yeah I know what I'm doing let me do this and then it will work once you say I know what I'm doing power query will not nag you about this again so I have already said yes to this yesterday when I was testing this out so today I'm not gonna get a warning message I'll simply it will it will take and it will work but if it is the first time you're doing you will see a privacy warning message pop on the screen you just enable the thing and then it will work so now it worked it kind of shows me p04 let's test this out by changing to p05 and then come back here again permission is required so I'll edit the permission I think it's because I deleted that uh the connection settings so now now it shows for p05 see what happens p06 uh and every time it is asking this is interesting Behavior anyhow it will it will give you for whatever product that you you set up I sus I suppose we could kind of avoid those kind of questions all together so once this query is is set up I'm gonna copy this whole query that entire step code and go to my sales table and go to the source step source and navigation these two steps navigation takes me to the thing instead I'll delete this and then here I'll put that line entire line [Music] so this sales table is now whatever the parameter says p06 is what I'm getting and then close and apply and and then I'll get a limited report just for p06 Anytime my boss says oh I don't want p06 I want p17 all I have to do is go and go to the power query and change this parameter to p07 close and apply and and run these things and then it will run it here so this is one option you could if you don't like the parameter route you can set up a list of where Clause variables in a spreadsheet or a text file or you could even have the query in somewhere and then bring that query as a whole thing and then combine that but it will it will run that so this is the crucial bit that that happens in the background see if there is any so p2j says how can we put a measure on our canvas that says data refreshed on dmmddy it should be the date when the data gets refreshed um you can do that we'll probably come back to this later if possible can you make in Rashid says can we pass value from Power bi report so this is a question that I was anticipating I'm not gonna lie when I when I was thinking like okay this is the presentation that I'll do I thought okay what are some of the questions that people are going to ask and this is one question and I have a funny joke as well as an answer you can do it you know there's Solutions where you can you can click a button here on the screen and then it will go and subtly back and then come back and do some weird stuff uh but imagine you're getting dressed for work you you put on your underpants and then you wear your trousers on top but what we try to do when we try to say can I take something on the power bi screen and then push it back to the query layer is similar to saying can I wear my pants and then wear somehow Underpants inside you might succeed but it's very hard and it's not necessary you same thing like that so the bottom line here is you need to do things in the order they are supposed to happen so that everything works smoothly and you don't end up doing a lot of extra work so any queries that you're writing you need to think about them first and then build the query then build the report once the report is done if you have a change of mind good place to do that would be go back and change the query not not like uh we'll make this report like this and somehow figure out another way to go to the report I mean I understand that sometimes you want it but it's a good idea to not get into that temptation of putting Underpants after you wear your pants all right and then sorrow says can I also display p07 p04 on the title dynamically this is an awesome question so and we can do this because the the information is available to us one way or another again there's lots of different ways to do this I'll show you the easiest way which is we know that within the sales table the only product that is available is p04 no other products are available just p04 so if I go to the sales table right click and write a new measure and then write a measure that somehow gets me the p04 then that would be the answer so this is we can call this as selected product and you can use anything for example if you say first non-blank value and here say sales PID that's going to give you a measure it's a 12. oh something else is expected what is this and uh and then put that there this incorrect I think I wrote the wrong calculation there I need to sometimes I have not used this in a long time because there is non-degree that is evaluated for the column okay let's just try the selected value then I don't know if this is gonna actually work because there's multiple but Eid yeah so selected value of sales table PID column because technically there's there's other products but because through query we have limited it will only have p07 it will come up with that as the name and this is sitting in a measure uh called selected product so now what I want is I want the sales report for awesome chocolates far and then p07 there in the title so where it says for after the far you can space and then when you are editing the text boxes you have an option like when you're editing you have an option to add values so here is a plus value button it is grayed out so it looks like you can't do it but it is there so when you click on this you can actually either ask a question or bring a model variables that you model calculations that you have so here I'm gonna say selected uh product it's a it's not popping up so come on yeah finally so selected product that is the measure name that we have generated and then save this and it comes up its text value so you can apply the same rules of formatting that you have done uh and you know for example I'm gonna make that bold so that will be the title sales report for awesome chocolates for p07 it's fully integrated so if you change your parameter let's go and uh change this to P11 uh and then run this it will have that a better thing would be rather than P11 or p7 to give a product name I leave that to you as your homework you know everything is there so you just have to change the query so it works like a join query remember the query that I showed earlier where we did the sales for Canada same thing but instead of Canada it will be the name of the product and the table will be different motivation says Dear Chen do you offer any courses precisely power bi developer I do have a course for power bi developers which is exactly like report building data analysis understanding power query power provide storytelling and all of those things so it's called Power bi course not confusing name and if you go to my website you will see that the course is there so feel free to check that out and sign up if interested so yeah that is uh that is it for the presentation I hope you enjoyed the the Journey of going from SQL to power bi to creating a report and then making it parameterized and customizable and all of that um and I hope you learned something new it was fun sharing this all with you all I'll now move on to the Q a part so if you have got questions feel free to put them there I'll answer them as much as possible but yeah if you are here for the main show that's it this is done uh yeah if you've got a busy day ahead or you need to go back and finish your hot water or or you know go back and catch some sleep good night or good evening or good good afternoon to you all hey one more Super sticker from omoto milola thank you so much for that lovely gesture and uh bittu says do you also have SQL learning courses from scratch if yes please share a link so that I can sign up unfortunately not this is something that I am thinking like you know maybe it'll be fun to teach a little bit of SQL I don't consider myself an expert in Sequel but I do know my way around it and I am yeah I'm not like great or anything but I can if you give me any database and explain what is there I can write queries without getting confused so maybe I could make a course but if you are yeah if you that will probably be like many months so if you are in a hurry check out the download link this is the link where you could download the sample file there is a one hour video on my YouTube channel called SQL full course so watch that it's a free video uh practice queries are there homework problems are also there and then if you still want more check out my course recommendations here and sign up for one of those courses Keegan says how do I parameterize dates you you can the the thing with dates and other stuff is it will get a little bit tricky to follow the convention so I'll show you the steps required okay let's say here I want to limit the sales that happened within let's say January of 2022. so select star from sales where sale date is greater than or equal to and then you can see the date format is year month day so 2021 0101 and and sale date is less than or equal to less than 2 3 21 0201 let's try this because you know it's always a tricky thing to write queries by hand and then run them because sometimes you may get the syntax wrong or the dates wrong or whatever but yeah it seems to have worked we can see all the January data here coming in nicely so only those dates are coming so now that we have to parameterize the dates what we need is you need two Edge points or you need a parameter that tells the month name and year month and year combination so Jan 2021 is the parameter but the problem is if I have got a parameter that simply says Jan 2021 I need to then turn that into a format that MySQL or whatever SQL you are using can understand the format that usually SQL understands is Year date month format whereas if I go to Power query here and transform and then create a parameter so we'll make a new parameter this is called my report we'll just call this as start date and the type would be date and I'm going to type a value here we'll see what happens so one Jam 20 21. okay so you can see the current value is formatted as 1 1 20 21. so when I try to take this parameter and now make it part of my SQL I'm gonna have some issues mainly because one this is a date data type not text value whereas product code is a text value in power query is a strongly typed language what it means is it likes to have data types for everything and one data type cannot easily go into another data type unless you convert it so in order for me to parameterize that and bring the date here I need to take that date and then convert that into a a text format that is correct for my my situation so this is where you need to learn a little bit more about the way M language works and all of that but for example here where PID so we're gonna take out those bits bear sale date greater than or equal to and then bring in that date so and then here like that I'll Zoom this bit later once I finish typing because it can be a little tricky to do both in one go so sale rate greater than or equal to in a single quote and here I have got my start date but I can't just use the start date so I need to use [Music] text from start date and then see that edit permission and you can see that when I see when I do text from the format that power query converts is 1 1 20 21. now we can kind of wing it and see if MySQL is happy to run this but if it comes back say it comes back and complains that this is not the format that I'm expecting then you need to turn this into the right format before you could actually run this so here this is the error that I'm now getting incorrect date time format because it was expecting 2021 1 format whereas this is what we have given so you'll need to turn the start date into a date format that is correct for what is readable by MySQL it it can it can be a little tricky to do all of that with direct M coding so what you could do is you can make a new blank query initiate this with start date so it comes up here and then [Music] from there make it a table and convert this to this gets a little bit too technical so if you are bored or tuning out and that is all fine but yeah so you have this and then once this is there it's as a date I can then kind of add columns for these so one is here one means month and another is day so that I have these three and then I can take these three and then introduce a final column which will merge everything with the Customs operator of hyphen and then yeah we'll just call this as merged so we'll end up with the merge date this is that and then right click and then say remove other columns and then right click on this and then say drill down so that we have a new query which is equal to exactly what we want so then say report date as the query name go back to the query and instead of start date use report date and then run that and boom you get your answer so it's a it's like jumping through the Hoops but that's because of the data type conversion issues there is probably other ways better ways to do this but you can take your parameters and then bring that so here we are only checking greater than if you have got another end date you can use less than and bring everything together hopefully that was helpful hunk says I have an error occurred during the pre-login handshake I'm not really sure maybe something else is going on I never had that error but probably search if you really have the answer hopefully it's resolved by now because that was like eight minutes ago uh Shiva says can we use stored procedures or temporary tables you could do all I have not done the stored procedure route or something else but you could you can definitely try you can if you have got a view built as well in your database that gives you what you want like especially you might have a view that tells you latest sales data so last 30 days sales will be a view uh you know it gets updated every you know and then or whatever you can directly query The View rather than raw tables like the way I have done so whatever works for you I have uh and then B2 says can you address this one so this is a good one it's just I feel like it's not gonna be foolproof I'll probably need to research a bit more but I'll show you how I would do it I'll be very careful to over rely on this simply because sometimes a part of this changes and rather doesn't change again it doesn't make sense in abstract so I'll show you what I mean by this so we want to have a last refresh date shown on the power bi report but it should only update if and when we refresh so here what we could do is we could create a new query blank query and then start this with date time local now or you know you could also do a UTC now if you are doing it on the server and the problem with power query times and when it goes to the service or platform and all of that is local now will be my computer's time but once I publish it will be the local of the power bi server which you're located set so it can get a little bit complicated uh so you need to be quite careful with this so I'll get the correct time here and then I'm gonna we can't really do it this with dates because then you will have to wait until tomorrow to see what happens so I'm gonna leave it with the date and time uh and you know I'll turn this into a table transform change type to date time so that will will get to this okay we'll say refresh date so we have now a table within our model that says refresh date I'm going to right click on the report dating and then uncheck enable load for that so we don't want that thing go there this one needs to go close and apply in refresh date table comes into my data model here it is a refresh state is a single row table that just has the date and time calculated within power query and technically technically this thing should only be calculated whenever you refresh but I say technically simply because what I could do within power bi is I could selectively refresh only the refresh date table and not look at other tables so I can right click on this and then say refresh this is only going to refresh this guy but not the other table so then what happens is the refresh date is changing but none of my other tables are so it's very hard to get get it correct but it kind of Works in a way so once this date is there you can then write a measure sorry I didn't mean to click that you can write a a measure which is last refreshed on is equal to selected value of refresh State this table will ever have only one row so it will it will just work this measure and then once that is there you know we can just put it a card add that there and put it at the bottom of our report and then that will be what it says 640203 so now it's already 6 43 on my computer what I'm gonna do is I'm just gonna uh where is the refresh refresh thing and it will be 64356. oh I didn't realize your comment was sitting on top so yeah 643 56 is what it will say this is pretty much where every time you refresh all this will change but like I said I can only refresh that table so I could technically keep updating this data even though none of my other things have changed but it is not foolproof but it kind of works nobody ever does that kind of a thing so if you build a process around it it should be fine all right uh Ramesh says awesome session thank you Ramesh I'm glad you enjoyed it I had fun as well [Music] and Adam Saxton is here hey Adam how are you and Arjun says how about creating views within MySQL I'll connect it to power bi engine to display graphs that we want instead of using Dax because not everyone would be comfortable using Dax that's a good question you can you can build the view that gives you the summarized data and then bring that and and create the visuals it might actually be faster than doing all the work within power bi anyway depends on what what that work is and how big your data is and all of that so yeah try it now if it works you comfortably with SQL you you can build that if you are comfortable with something else let's say python or r or whatever else you could use those visuals as well to do the data analysis for you you don't have to use Dax and even if you don't like Dax or you feel like Dax is hard power bi offers other ways to build that so you could use the quick measure option here and power bi now has a q a kind of a feature where you can talk to Dax ask what you want and it will build the measures for you and it's not that hard I mean if you learned SQL and you you're able to master it then you already have that that learning Gene you can you can figure it out so you you can now know that you you already know SQL you don't need to learn more of it you can go and learn back so lots of different ways whatever works for you Jackie says thanks for the tutorial how can you keep your power bi Dynamic enough to make requested changes but it will not include back dated information such as a salesperson ads passed sales after close it's very specific but you could probably build some logic to figure out what you what you're trying to do I'm not really sure if that is helpful but yeah you could use power query to limit what data you're fetching you could use SQL like what I have shown you could modify your Dax so that it doesn't pay attention to changes past the date or whatever and then do all of that and of course Adam Gibson with the hybrid tables you could catch archived data and then near real time for the current data or flip it so yeah that is a brilliant solution as well [Music] says thanks for this I have an Excel formula question unrelated to this where can I ask uh probably stack Overflow Mr XL Forum my Forum lots of places where you can post such questions uh and uh yeah hopefully you'll get some help there sirisha says can you share any resources to learn R from scratch please uh it's been many years since I learned R and used it it's been many years that I could even confidently say I forgot our I can't even write an R script suddenly I'll have to brush up a lot of Concepts but uh I read a book called R for data science it's by I can't read the names of the others from here it's on the bookshelf there but yeah probably get the book or or watch some tutorials on YouTube the r creators have like it's a very open source community so there's like hundreds of probably thousands or even more websites and resources and free books and courses and all sorts of stuff available online and if you have done any programming R would probably be easier to just to pick up and continue writing code so yeah good luck ramakrishna asked a question which is how to track if someone made changes to your report and Adam gives the answer so even I didn't know this so I'm gonna see what's the desktop files represent SharePoint Library you can see history there for the service the audit log is where you can see it so yeah uh that's pretty much it and if the file is in your computer and somebody is changing it without you knowing it then you have a problem my friend but in all other cases if it's shared then you can see the history like Adam points all right I feel like we have reached a natural point where we've seen what we could do with SQL and power power bi uh in you know like I said I hope you all enjoyed this it was fun talking to you all and sharing some of these ideas do feel free to put more questions in the comments below once the thing is over like using the chat feature or the comment feature on the replay mode and download the SQL database and practice this I'll try to post a copy of this file but I don't think this will work when you when you download because it will require the MySQL connection and all of that so maybe not the file but the database is there and you can practice it thanks again and remember I do a live stream on the last Friday of every month so next month uh I'll come back again with some other topic if you have got any suggestions for a topic put them in the comments so that I can consider them when I plan for my live streams and yeah as always stay awesome thank you

Channel: Chandoo

Views: 83,023

Keywords: power bi, microsoft power bi, power bi tutorial, data analysis, sql, how to use sql with power bi?, how to use SQL query as data source in power bi?, parameterize sql powerbi, query parameters powerbi, sql variables powerbi, mysql powerbi, sql with power bi live example, power bi realtime example, power bi dashboard

Id: UQJAHUUFK-o

Length: 111min 37sec (6697 seconds)

Published: Fri Feb 24 2023