ULTIMATE GUIDE TO STATIC TABLES in Power BI ROW, UNION, DATATABLE DAX Functions Guide

in this video I'm going to show you a couple different ways that you can create static tables in your PR airports we're going to go through them step by step together how you can create them using power query and also how you can create them using Dax all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solutions abroad YouTube channel where we go over tips tricks and best practices when working with powerbi I uploaded new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so static tables as the name suggests are tables that typically don't change when you refresh your parb airports it's a handy option to have if you need to have tables that for example you know won't need to change every time you refresh your reports the typical static tables you would need are let's say for mapping tables or Dimension tables that won't really change for the duration of your report so instead of importing them from files you can simply just create static tables for them that are excluded from the report refresh so that it doesn't add as another thing for your parb air reports to refresh every time the refresh is triggered so instead of importing this data from file for example like you typically would with your other data sources you can just create a static table that is excluded from your refreshes so that it's one less thing to be refreshed by your Port so today I'm going to show you a couple different ways that you can create static tables starting with the enter data so first here I want to show you this mapping table that I created for age grouping so I would typically create a mapping table that lets me group ages in certain groups and they most likely won't ever change so here we have the different ages that we could have part of the workforce in the UK and then I created a custom grouping for each of those different ages and then I have an order for kind of custom ordering them if I want to show them in parbi and let's say we want to create or have this static table in our report in our parbi desktop report so the first thing that you're going to do is we're going to copy this table contrl C and we're going to go to powerbi desktop here now instead of saying get data from Excel which you would typically do we can just click enter data here and this lets us copy and paste the table that we've just copied so if you hit contrl + V there we go so that's our mapping table created as a static table so we just simply need to you can just load it by itself so just name it age grouping but when you load it by itself they are not defined their data types aren't defined so I typically would go to edit to open up power query so this is the query that we've created the age group and then I would change these into the right data type so this would be whole number as well as this one so if you look at the step here instead of the source getting the data from you know from a file from an Excel workbook it instead decompresses the file from this binary data so the data that you have here is basically created here in your data model and is stored as binary text if you want to edit the table that you have created you can simply just click the Cog icon next to the source step and it will bring up that same view that you had initially and then this will let you just change the values here as you like and that's it now let's have a look at some of the options available for you if you want to use Dax and the one that I am pretty familiar with and that I I have used in the past is the row function which lets you define in the same parameters Define the column headers that you want as well as the values as static values and then if you want to create a table out of the rows you simply just use the Union function to stack these rows on top of each other so let's have a look at how we can do this so here we are in my parbi desktop we've just loaded up the age grouping here we we're actually going to ignore that we're going to go and and insert our under modeling create a new table here which will let us create a Dax table we're going to call this one totals and then what I'm going to do is I'm going to start typing row and here it gives me the option to add a name and expression so the name and the expression combination would be the column header and the value in that column so if we add let's say the name would be total sales and then I just add 10 let's just close that and then let me show you how that looks like so you see what it's done is it's added a new column called total sales and it in that column it's added a row which is the value of 10 so if we go back to that tax table we can now we can add more columns and expressions combination in this row function so let's say we want to add total orders let's add make it 15 and then let's say total discount 20 so as you expect if we go now to the table view it's created a one row with all of these three different columns with the Expressions that we have put in this row function then as I mentioned before if you wanted to create you know multiple rows within this table you just need to create more rows defining the column headers and the expressions and then combine them all using the Union function so the union basically just lets you add tables and then append them on top of each other which if you have multiple row functions will let you do exactly that so I'm going to paste the row function that we've just copied and then add the comma and then paste another row here and then just to show you as an example add another row here and if I hit enter go back to the table view as you can see it added three different rows with those three different row functions combined using the Union function so I use the row and Union function quite a lot or I had to use the union and the row functions recently for my workplace and that's because I needed the ability to change the expressions in these tables to be based on results from my measures now let's say for example we have some me measures here in my calculations measure table and I have a bunch of things here like total sales total orders and discounts and by themselves they don't I just put some random values in there but they could be different calculations that results in different things and what I wanted to do is that for every single time that I updates the reports the value in these Expressions within the row tables also change based on the results of these measures now what I need to do or the only thing that I need to do is just simply update these Expressions to just use the measures that I've created so total sales total orders total discounts and just like that you pretty much have a table that changes its values based on the measures that you kind of already set up now it doesn't really make this table a static table anymore because it changes based on the data that you have but I just wanted to show you like some of the reasons why you might want to use kind of the row function in this sense so using the combination of row and Union works pretty well however I just find it to be a little bit inefficient that's because for every row you have to always Define the column header and you have to keep saying that it's a row even though that we know that it's always going to be the same CU otherwise the union will break now there is another another option that you can use which is called data table which pretty much does the exact same thing except I feel like in terms of syntax it's a lot cleaner or it's a lot easier to sort of read so I'm going to show you how to write the data table function so I'm going to call this one totals 2 and then I'm going to use data table here so the first thing about the data table is you need to Define what the column headers that you you want so in this case let's just create like an example table right so let's define a few different columns that we want in this so maybe let's start with a category so you define your column and you define the type of that column so in this case we're going to use categories a string the next column we want product and this one is also a string and then the last one we'll call Total cells and then this one might be an integer let's just say and then now that you've defined that we now Define the values in those columns in the right order that we have put them up there so we're using a curly brackets to say that it's going to be an array and in this array there's going to be another array which is the array of all of the values in each row and then here we're going to just put the values for our column so First Column category we're just going to say condiments comma next thing is the product's name and let's say catchup and then the total sales could be 20 and then if and that's basically your row done now if you want to add another row you just need to add another comma another curly bracket and then just do the same sequence so let's say Dairy as your category and then the product would be milk and then the value would be 15 and then that's it so if you hit enter hopefully there are no errors and if you have a look as you can see it's created that table it's created the column headers you've just had to Define it once and then it created all those different rows for you now I find data table to be a lot cleaner in terms of creating static tables just because you only need to define the category headers once and then everything else that you add in your rows is you just add it in these curly brackets and and I feel like it's a lot cleaner especially if you need to create a static table with a lot more rows than the two that we've just created here so I feel like this will be a lot easier to manage and a lot easier to read than using the row and Union and that's really it for this video I hope you now know the kind of different options that you have when it comes to creating your own static tables in powerbi thanks for watching as usual give this video a like if you find it useful give it a dislike if you didn't so not to do better for next time I'll ask your questions in the comment section box below so I can help you and you can help others if you like this video we have a patron page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye-bye

Channel: Solutions Abroad

Views: 2,475

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, power bi get data, power bi static tables, power bi row, power bi dax, power bi union, power bi data table

Id: eV3fhP0rTcY

Length: 11min 45sec (705 seconds)

Published: Wed Nov 22 2023