3 Hacks to Work Faster in Power BI

developing powerbi reports can take a lot of time you have to set up the data model create the measures the visualizations make sure that the design and ux is amazing then of course the report needs to be optimized and not even speaking about what needs to happen in power query now of course at some point you're going to feel completely overwhelmed but what if I tell you that you can work much faster I'm going to show you three hacks that save me personally many hours every single week let's dive in hack number one collecting store all useful Dex manases and M functions in a central location so that you can easily reuse them for future reports now you might say yeah but every report is unique how is that going to help me well that's true every report is unique however there are also many elements that repeat itself for every report think about highlighting the maximum and minimum point in a visual or highlighting the top three items now all of these kind of Dex measures you want to turn into templates that you can reuse so that you don't have to reinvent the wheel and that can save you a lot of time now the question is how now let's look at an example over here I have a column chart where we highlight the maximum and minimum using a DEX measure for conditional formatting now that Dex measure I could use as a template for next time so let's go to the Dax measure let's open it up now this Dax measure we can then copy over to a taex file to which we then also going to add other Dax manes that we want to reuse as templates for other reports and then that text file we can save in a central location now for the next report where we would then need that measure we would have to look for that file open it up copy it over and adjust it to the data model that we have in that report now that works at the beginning but over time the more measures that get added to that document well the massier it's going to be and therefore it's going to get more and more difficult to get the Dex measure template that you really need now that is one downside of going for this approach also you have to look for this file every single time and if you're not very well organized well then suddenly you start to have also copies of that file so different versions and what if you want to collaborate with other people that might want to add their measure templates to your file well then it's going to get very messy so is there a better solution yes there is I would recommend something like GitHub now if you have never used GitHub before let me show you it's really easy to get started with it so let's go to github.com now the first thing that you need to do is set up your account so this you can do here in the top right corner you have signup let's click on it and then you just have to follow all of the sign up process steps so enter your email then you have to confirm Etc and then you can log in now when you log in you get to the welcome screen and the first thing that you need to do when this is the first time is set up a repository now repository is just the place where you have all of the project files with vision history and collaborator discussion all right now we can give it a name so over here I'm going to call this one how to powerbi and we can choose is it going to be a public one or private one do you want everybody to be able to see that repository and whatever you're going to save in it or should it just be for you and the people that you share it with all right now I want to have it private all right so I'm going to click here on create a new repository and now we are ready to start adding files with our Dex smashes and M functions to this Repository now let's go back to that welcome screen you see now here on the left hand side we have a new repository how to power behind Perfect all right now I'm going to click on it and now we can create a new file by clicking here on create a new file now let's give it a name now this is for conditional formatting so I'm going to start with CF and then highlight and then we have Max Min okay now here we can copy over the tax measure from the Barbi file so I'm going to go back to Barbi let's copy it and then let's go back and paste it in there now you can leave the measure like this or instead of having here total sales you could also write something like base measure so that you make it more generic but for me it's actually fine as it is now let's now just put in still the extension MD all right and then commit the changes so this is going to save it and that's kind of it we can just keep on going like this right so I can go back then for example take here the above average Cal calculation so this one is also for conditional formatting let's copy it over I'm going to create again a new file so here in the top right corner create a new file paste it in there and then we have again conditional formatting highlight above average okay and also here we can click on Commit changes so now we have a central place that's going to have all of these files with our Dex templates that we can reuse for future report so next time when we need it we just log in to GitHub go to this repository look for the template that we need click on it to open it up and now it just shows the text let's click here on code and then we have the formatted code copy it over adjust it to the data model of that new report and that's it but what if you don't want to log in GitHub online every single time but just want to have it on your desktop well there's also GitHub desktop which you can install if you go here to the repository to powerbi then click here on code and then here you see open with GitHub desktop now of course this only works if GitHub desktop is installed which is sometimes a problem if you work on your company's laptop that they don't allow you but if they do allow you I would recommend set it up all right now if you click on it it brings you over here to the desktop version so you see over here the GitHub address and here the local bath on your laptop and then you can click on clone so that you have them always in sync another good idea is added for these files is to install Visual Studio code which you can nicely link over here with GitHub desktop so then you can say open in Visual Studio code then it opens up and then here on the left hand side you see our two measure templates above average conditional formatting and Maxim Min all right and then you can open it up make changes or add new templates to this repository and then make sure that it sings again with GitHub online well the best thing of all of this is that you're not the only one that's going to do this there are also many other people that are already doing this and have public repositories and you can just take their code and use it for your powerbi reports so if you simply go to Google let's say now I'm interested in custom M functions then I type in custom M functions library and here you see already two so let me just open the first two that I find now the first one that I open up is from MK Felman and the other one is from all right now here both of them have a lot of different M functions that they wrote that they shared with the public so let's have a look and over here this one looks quite interesting calculates the trend according to excel's Trend function but without the option to Define your own slope and intercept so over here you click on that function and you see there it is and now you can just simply copy it over and use it in your powerbi file if you need it heck number two is to create design assets for example background templates themes and general templates that you can leverage for future reports now let me give you some examples let's say you have a new report and you start creating some new visualizations then there are probably some formatting changes that you make every single time and that need to be applied to every single visual that takes a lot of time now instead of that you can work with themes but not just the standard themes you can completely customize them now how first of all you have the option to go here to the top view now over here we have an overview of all the different default themes and if we want to customize one well I would usually just pick one that's kind of close to what I want to have now let's say this one over here and then we can click on the drop down again and say customize current theme now here you have the theme name let's go for our own example theme so say example bus one and then here we can start changing the colors that we want to have let's say I want to have over here dark blue instead then for the text there I don't want to have suoi but I want to have sui uh semi bolts let's say and then for the visuals let's say the background color I want to have light gray and then I click you on apply all right now you see those changes get applied straight away and here we have now a new custom theme if I want to save it then I just click here on save current theme you see that the extension is a Json file which is just a structured text file which is quite easy to edit as you will see in a second now I'm going to click you on Save now if you have Visual Studio code then it's relatively easy to format that text you just right click and then say format document and then you see this is a little bit easier to read than what you have at the beginning when you export it okay now you see all different kinds of attributes that we have for that theme for example the name at the top the main column foreground background the accent colors the maximum the center the M minimum bad neutral good and then following after that also for the visual Styles we see that the background color is always that gray that I chose before now all of this we can customize and we can add new part to that theme now let me show you how that works and how you can figure out what you can type in here all right now let's go to the documentation first now how do you get there well just go back to view themes and then here how to create a theme and then it opens up your browser on the theme creation page now over here the beginning is not that interesting just scroll down down down down until you get to the custom theme part and there you go report theme Json file format okay now there are basically four parts it says theme color structural colors Tex classes visual Styles and then from here we can just take this as a starting point so I copy that go back to my text editor and then replace this with what it says in the documentation so this is going to be my theme Bus theme okay now I leave this as it is of course in reality I would customize it now let's scroll further down to the visual elements part so that's more down down this over here and here you see we have lines that talk about the Quit Line style also how the filter card should look like and over here specifically how a scatter chart should look like so here you see visual styles asteris that means this applies to all the visuals and here this is more specific just for the scatter chart okay so let's copy this over as well I'm going to go back here and I'm going to add it to my theme I have to make sure that it's in between those brackets so let's just add some space over here P this in there now of course this name part custom theme we already have there it's just about the visual Styles and here at the end there we need a comma so that continues all right then just make sure that the brackets match right so this starts and end there okay this one we don't need okay and with a shift tab you can pull it to the left with tab you can intend it further okay now how can we now build further on what is over here now this is just an example right so instead of dotted well we probably also have something like solid and here instead of a skatter chart well probably there's also a bar chart column chart line chart that we can add as well for which we can figure out the elements that we can format with this custom theme okay so let's give this a try I'm just going to copy this scatter chart part okay and I'm just going to paste it right above it place a comment there at the end all right and instead of scatter chart I'm going to try line chart okay so I'm going to get rid of this and then I just start typing line chart okay now line chart doesn't have Bubble so I guess this is not going to work but how can I then see what elements there are that we can adjust so over here I'm on the GitHub from Microsoft and here we have different report theme schemas now here you just have to look for the latest one or the one that corresponds to the Barbi version that you're working with now here for me that is the December 23 version so let's click on that one let's open it up in a new tab now these theme schemas can be used to figure out what elements can we adjust and what are the options now to do that we have to scroll down a little bit further now over here it explains how you can do that so let's go down down down we just have to reference it here below the name so we say schema and then the name now here if you do it like this you need to have it downloaded if you just want to reference the raw file so the raw file is when you click on the schema that you want to have so that is this one over here and then you click you on Roll okay then it gives you the link right so copy that go back over here to our theme and then here after the name there we want to have the schema that they wrote it like this so we have a dollar sign schema you see and then in between quotation marks there we put the link to that raw file that contains the schema all right now don't forget uh comma there at the end good now how does that help us well that helps us when we write a code right so over here if you do control space it gives you all of the possible options right so here you see Azure map bar chart what can we write there what are valid options now here we want to have a line chart and you see this intelligence is there which otherwise would not be there now what else can we do well over here these elements line chart doesn't have bubbles probably but it has different elements so here I can I can actually delete this part right do just control space and see what is it that I can adjust now for example I want to have something like pading you see patting pops up press Tab and it already fills it out just like this now and then again I can do control space ah I need curly brackets again control space now we have padding bottom left right top so let's start with the button zero and then the next part so over here probably we have to go to the next line so I put a comma there go to the next line control space and again the same thing control space and left so we just repeat this a few times so let me just do this also for bottom and top okay so after you've done that then just choose how many pixels you want so I always put it to 20 so over here 2020 okay good and I also want to have borders with a little bit of rounding so I continue so over here again control space and look for the Border element okay control space what do we have we have the color and the radius now the color I probably want to have white but I just let's focus on the radius now over here the radius we can put to 20 at the moment we just have the Bing and the Border radius for the line chart and it will work for the line chart but not for a column chart or any other type of charts so if you want that we can just copy this over there to the top part right so over here let's copy this and then I go there before that yellow closing bracket comma let's create a bit of extra space paste it in there okay and that's it so do I still need this part for the line chart no not really but I just leave it in for now and save the theme now I call it bus example one okay save that control s then I go back to powerbi and over here we can now import that theme so I browse for themes there it is and you see we do have padding but we don't have the rounding why is the rounding not there well that is because the border is not turned on probably so here I would still need to go to formatting you see here we have size and style and the visual border is not turned on if I turn it on you see ah now the rounding is there so I would still need to adjust it a little bit further so building a custom theme is a lot of trial error now if you want to figure out now what what changes can I make to border now don't do it from here but do it from here from the line chart because here it doesn't give me all of the options it just gives me too much because it can be for any visual right so therefore I go back over here under the line chart and then over here it was for the border so after the curly brackets control space we have show and that one let me undo this let me push that to the right we have control space show that needs to be true now let's then also add color so control space color okay and then the curly brackets again and then let's see what we have solid and then we have the option color and then we have to say which color so let's put that in between square brackets and this needs to be white so f f f f f that's so six fs and you see there's this squiggly line below it that means I didn't write it correctly there needs to be ashtag in front of it okay now you see this intelligence is very crucial if you cannot figure it out still with the intellisense either look here or another alternative is the GitHub from mat Rudy he has also some good stuff if you scroll down down down down per chart what you can change to for example line chart can open it up you see also all of the different elements all right now let's go back I just want to make sure that I have this also for all of the visuals so I'm just going to replace that here as well okay and again save it go back to your powerbi file if your theme Chang in the meantime and what you then you still need to import it again right so over here go again to browse for themes and import your theme got it okay perfect and as you see now we have the rounded Corners in white we have the padding all different kinds of changes that I would do anyways but now I have the theme for that but this investment is well worth it for all of the future reports that you're going to create because then you don't have to redo it every single time have good themes but also have good background templates that you can easily adjust for the design to make something look good now let's go to an example now over here I have a report that I built in the last video which looks kind of okay at this point but it's nothing compared to the version with a good background now let's have a look at the difference and you see the difference is huge but creating these backgrounds that look good can be quite time consuming so what can be helpful is if you just have a few templates that you can easily adjust for the report that you're working on let's say you get a new page right then well the background needs to look a little bit different right so for example if I go to one of the other pages and here you see a slight variation of the background to accommodate the visuals for this page and let me show you another page and again same background style but adjusted to the visuals now these themes you can create in PowerPoint but uh you can also use Adoba illustrator or figma whatever you prefer doesn't really matter whatever your preference is but what is important is that you store these assets somewhere these good background generator files so that you can easily get to them and make adjustments if necessary so here I just have a few variations of the placeholders for the visuals so let's say I go over here and I need suddenly an extra placeholder right next to this one right so then we can just easily you know make a duplicate of that rectangle and start playing around with these placeholders and then generate new SVG use that for the background but you see that saves me a lot of time and effort starting from scratch and then thinking of a good design because coming up with a good design can be quite timec consuming and difficult so in short build some design assets that you can leverage for future reports that is a huge timesaver as well and that brings us to hack number three which is using external add-ons which can help you with building and optimizing your data model or creating your measures now there are a lot of them out there let's have a look at two now the first one that I want to highlight is Bravo from sqlbi looks amazing and it can help you with analyzing your model so which columns take the most space and which ones are used and which ones aren't formatting your decks managing days dat tables and dat and time calculations and exporting your data so all things that you do regularly when you work in powerbi now let's see it in action now after you have installed an external addon you will always find them here on the external tools they are alphabetically sorted so Bravo is here on the left so let's click on it over here it gives you some summary information about your data model which columns are taking the most space and are they being used or not so maybe you can get rid of them to decrease the size of your data model and optimize it now here for example everything that is in yellow those are unreferenced columns but take a lot of space so you might want to consider taking them out of your model and then here on the left hand side we have an easy way to format our Dex measures now here I just have total sales so there's not much to format now then if you want to add a date table to your model and you don't have one just yet well building a date model can take quite a bit of time now here you can choose the template that you want to have for your dat table and then we have all different kinds of parameters that we can play with first year last year also here the regional format Etc now also important is time andal so here you can say I want to have for all measures time intelligence meaning year to date year over year these kind of things and if you have everything set up you just click on preview changes and then here we have time intelligence and you see here for FD sales we have only the total sales measure we have time intelligence then growth then the measure names or measures names and then here we have all of these different variations with very common time intelligence calculations now to write these measures yourself well if you have done it you know how much time it can take can easily take your day but with an exter internal tool like this just takes a few seconds now once you're happy you just click on apply changes and it goes back to your powerbi file so let's click on it so you see over here is my FC sales table we have there the folder time intelligence and here we see all of these measures let me just make this a little bit wider now same for the DAT table if I go here to the table view over here you see there's a template and and a DAT table that we can reference later on all right perfect now let's look at another external tool which ties back to hack number one which was to collect store and reuse your own code but you can also reuse other people's code right with for example their GitHub and then either take it from there or you use an external tool like this one over here from gr tackler so to get it you just have to type in Microsoft HS GRS quick measures all right and brings you to his GitHub from where you can download it so over here just download the zip and then run the uh the installer all right so that's it once you have done that you will find it here in the external tools as well so let's click on it and let's see what we can do so over here you see it is kind of like quick measures we have all of these different categories so here also data intelligence that it can help you with but let's scoll a little bit further let's say you're exploring SVG and you need a few templates now over here there's SVG category and say we want to have SVG heart all right sounds good then we have to choose measure or column now base value aggregation let's say we want to have the sum column and then we go here for total sales let's click here on okay I want to have the Dex boom there you have it nice okay and then we can just say create that was successfully created let's see I'm going to close over here the window and now the measure popped up over here my sales table total sales SVG column let's select it and you see it nicely gives me the SVG code and actually already filled in the base measure or column that you picked before all right now there's much more that you can do and many more external tools that also really really good that can save you tons of time so these are my free hacks so that you can save a lot of time every single day so that you can go for an extra coffee and don't have to stress about all of that powerbi development work that is waiting for you now let me know your hacks in the com section below if you want to know all of my tips and tricks and build reports together with me then check out my upcoming design transformation program over here thank you for watching I see you in the next video

Channel: How to Power BI

Views: 45,529

Keywords: powerbi, power bi, howtopowerbi, how to power bi, data training, datatraining, bas dohmen, power bi training, power bi tutorial, power bi design, report development, hacks, efficiency, faster, tips, tricks, dax, power query, github, git hub, visual studio, external tools, optimization, themes, templates, library, design assets, report design

Id: hvY6SJrq3Ys

Length: 25min 39sec (1539 seconds)

Published: Wed Jan 24 2024