#118 How Power BI Empowers Collaboration

Hi, I'm Maggie, VP of Curriculum at datacamp, and I know firsthand how overwhelming it can be to keep up with the rapid changes in data and AI. But staying ahead doesn't have to be hard. That's why our curriculum team has curated the best learning content to help you and your team stay ahead. Covering everything from data and AI literacy for busy executives to cutting edge generative AI for developers, and everything in between. Whether you're upskilling yourself or your team, we make learning practical, engaging and flexible. It just works. Power your data and AI transformation journey on datacamp.com. You're listening to Data Framed, a podcast by Data Camp. In this show, you'll hear all the latest trends and insights in data science. Whether you're just getting started in your data career or you're a data leader looking to scale data driven decisions in your organization, join us for in depth discussions with data and analytics leaders at the forefront of the data revolution. Let's dive right in.

Welcome to Data Framed, this is Ritchie. Happy holidays everyone. Today we've got an exciting episode on the business intelligence platform, Power bi. Power BI is a low code tool, which means that you can do a lot in it without writing any code. However, there are occasions where you can get more out of the software by doing a little bit of programming. In fact, Power BI has two programming languages built in, namely EM and dax. Explaining when and why you'd want to use these languages is Ginger Grant, the principal consultant at Desert Isle Group. Ginger is a Microsoft Data Platform MVP and frankly an all round expert on machine learning technologies, data warehousing and all things data with the Microsoft Tech stack. Not to mention being a datacamp instructor. I'm looking forward to finding out more of the insights around programming in Power bi. Hi there Ginger. So thank you for joining us. Real pleasure to have you on the show. So today we're going to be talking about programming with Power bi and I guess a lot of people, they're going to think, well, isn't Power BI a point and click platform? If I want to learn programming, I would have learned some Python. So can you just maybe talk us through why you'd wanted to go and learn some programming for Power bi?

Yeah, you know, it's funny, I was talking to somebody recently and they're like, you know, Microsoft says that you can just do point and click and you don't have to know anything. And I said, well that is true. It is better if you do because you know you can get it to work and then there's getting it to work well and it's scale and larger installations. And for that you're probably better off in going away from just like the clicky draggy stuff to actually writing some code to get it to do more of what you want. It's also easier if you're trying to help people who don't know what they're doing, because, like, you have to set all this stuff sometimes, or you can just basically save it as a variable, which is really what code is, and say, here, use that rather than filter this and that.

So it's going to give you the opportunity to reuse your code and therefore do less work, I suppose is the goal, more control.

And it's one of those things. It's when are you going to do the work? Are you going to do it every single time you want to do something, or you're going to spend the time so that you do it once and then it's available every other time you want to use it, which is like anything else with programming.

Okay. And so I know Power BI has quite a wide range of users, so can you tell me who might be more interested in the programming side of power bi vs point and click?

So there's a lot of different ways you can do programming in Power bi. Embedded into Power bi, there's two different languages. There is M, which is primarily used for etl, kind of transforming your data, and that's in Power Query, which is part of Power bi. And then there is dax, which is the language that's used for creating measures. But if you are an R developer or a Python developer, you can include R and Python visuals within Power BI as well if you want to. There's some limitations. If you're doing R, you want to make sure that you look at Microsoft's list of the 2000 or what some odd libraries that they have to make sure that the one that you want is going to be included. And you can do Python as well. And that includes Anaconda, including, you know, matplotlib and Seaborn, which is the ones most people use.

Okay, so we've actually got like four different choices at least there. So you've got the M and Dax and Python and R. And just to make sure everyone said this correctly, so M is the bit you do at the beginning when you're sort of importing and cleaning up your data set, and DAX is for the data analysis. And then I guess if you want to do some fancy machine learning or database or whatever, then you're using Python or R. Is that Correct.

If you just have some R visuals or Python visuals that you really like, you're like, you know what, I want to include those in here. But you can do fancy, you know, machine learning as well. I mean, you're not wrong about that at all. But if you've got a visual in R that you like, you can use it.

Okay, so that seems pretty powerful. The fact you've got access to all these different languages certainly gives us a lot to talk about today. So which language do you want to start with?

Well, let's start at the back end for em, because I had a question last week for a guy that I've been working with who's just starting out in Power Bi, and he's like, hey, I can't do any manipulation of my data at the source. I was thinking about doing all of my data manipulation in DAX and I wrote him back and said, that's a bad idea. You should do it in M, because the way that the data is used within memory, it's more efficient if you can do it in M than if you do it later on in dax. So things like, I want to create a table, I want to create a new column, you know, out of existing columns. And of course, if this data is just a mess, I need to clean it up. I've been doing some work with Tableau recently, and I know that they have a separate program that you need to do if your data needs to be cleaned up. And recently I've been working with a client and they give me Excel files that they get from the state government that they work with that they want to use as part of the reporting. And it has like a header on the top of it and a couple of things ever care about. So I need to get rid of the top four lines every time I load it in. And that's the kind of thing that you can really do well in M. And also, I don't need that column. I'm going to get rid of it now. If I could do that at the source, great. But sometimes you can't. Like somebody gives you a flat file or the database administrator says, and this is what you're connecting to, and that's it.

So you get a table that seems really useful. And that's certainly something I've found when doing data analysis. Like, the quicker you get the data in the right format, the more efficient your analysis is going to be. So I guess your suggestion is that you should be using EM upfront and just really sorting your data out there. Are there any Other sort of use cases for EM, beyond this, sort of like cleaning up, reporting data, have you seen any good uses of it?

Yeah, it's got a couple of other, like, key tricks that you want to do. If you want to do incremental refresh, you have to set that up in M. So let's say that you are connecting to a database to get your data and you refresh that every day. If you don't set up incremental refresh, you're going to do what's called a trunk and load. So every single record is going to be deleted and then every single record is going to be added again. You got 2 million records, that's going to take some time, whereas maybe you only have 15 records that were changed from yesterday. So if you want to set that up and make it so that you're just getting the data from yesterday, you need to do that in M as well. And that's also a place where you can create variables that you can use for connecting two data sources so that you don't have to enter them all the time. So it's the global variable as much as it is not location for power bi.

So that seems like a really useful performance thing. Rather than trying to work on the whole data set every time, especially if it's getting bigger and bigger, you're just working on the latest bit makes it much faster. So, kind of curious, for people who are new to em, where do you get started? Like, what are the sort of first things you need to learn about?

Well, the one thing that's great about EM is it's very, very forgiving. So when you do anything in em, there's this thing on the right hand side that says Applied steps. And if you do something that totally breaks everything, you can just delete that step, which is the best part about it. Like, I wish a lot of code was like that because I was writing some Python yesterday, I'm like, shouldn't have done that. Can't go back, you know, I just want to just delete that last thing that I did that screwed all my code up. Can't do it. But that's what's nice about Power Query is you can do that.

So you've got that undo step that seems pretty useful. And in terms of the language itself, can you give an example of like, some of the important functions or important bits of code that people need to know about?

Well, what's interesting about EM is it's designed to be something that you don't necessarily have to write in that you just select menu items and do things and it writes the code for you. So you don't have to know the M language per se to, you know, do a lot of work. You know, if I wanted to say split columns, get rid of that funky character, get rid of all the blank lines, you know, I don't have to know. Write any code for that. Those are menu options. However, there are times that you're going to actually want to look at what got generated when you wrote it. And that's in the advanced editor and that's when it's going to show you what is in the code behind the editor on it is not very forgiving, shall we say, unlike the rest of em. So the first thing, if you're going to be messing around with that advanced editor, copy everything in a notepad. So if you screw it up, you can go back because you can cancel out of it so that, you know, you don't change anything. But if you wanted to like kind of see what was like there before you started tweaking it, you could cancel everything out. Or you could just look at notepad. I recommend that people do with that. And then it's really kind of picky. One of the things that the advanced header you see is that references the previous step every single time. So if your previous step was rename columns in the next line, it'll have a hashtag, rename columns and you have to put that. So if you try to delete something out of the middle, it won't work because you don't have a reference to the previous. So if you just take a look at it when it's working, the thing about it, that applied steps thing, deleting things in the middle can be problematic. But if you look at the M, you can figure out, oh, well, that's just because I need to put that hashtag previous stepname in there and then I'm good.

Okay. So it sounds like you've got this balance where maybe the first time you write something it's going to be done using pointing and clicking. And then if you want to reuse that, then you've got the code there that makes it easy to just click run and you've got the same transformations going on over and over again. That's kind of a cool thing. Not a lot of platforms get that right. They're all either really point and click focused and you've got no code, or they're very code focused and you're not pointing and clicking. So it sounds promising that you can do both with M. So beforehand you mentioned that M is part of a sort of larger suite of tools called Power Query. Can you just talk a bit about, like, what the difference is between Power Query and M, then?

What Power Query does is it opens up a separate window and it's completely separate for Power Bi in that how it functions. You can't create reports in it, you can't write Dax in it. It just basically is for data manipulation. You can't do relationships with it. And once you're done, you say Close and Apply and that window closes and you're back into Power bi. So you can actually have a Power BI window open and do completely separate things that don't impact one another until you hit that Close and Apply button, though. It's very separate, but it's still part of Power bi.

All right, fair enough. And other tools within Power Query beyond this M programming?

Nope. That's another thing. When I talked about writing R or Python or Dax, you can't do any of that in Power Query. You can only do Microsoft.

And in terms of the tooling for this. So is this just something you would use as like a standalone tool? Are the different ides that you can use for Power Query?

Well, Power Picker is part of the Power BI desktop application. While there's many things that you can do in the online version of Power bi, Query is not one of them. So you have to use the Power BI desktop and then, you know, go to Power Query to be able to do this, which is called transform data, not Power Query, which kind of throws people off.

Would you suggest that this is something that, like, anyone can get started with? What do you need to know before you start using Power Query or M?

Anybody can really get started using it. One thing that's really important to know is that you don't have to keep anything that you do in there. So if you do something, you're like, I like this better. You don't have to load it to Power bi. And if you don't load it, it's effectively gone. You can make it go away. So it's completely transparent. And like I said, you can go back on different steps. So it's very, very forgiving is what's really kind of nice about playing with it.

All right, now we get to the fun part where we do data analysis. So let's talk about dax. Can you give us an overview of what Dax is?

Sure. So once you've got all of your data clean using Power Query and you've got the relationships made now, you're probably going to want to start making some calculations that are not something that you can easily click and drag your way to. Like what? For example, let's say that you get it's time to write reports and somebody tells you that I want to make sure that I can look at previous year, and I want to look at previous year when I've got this region selected, the salesperson selected. And oh, by the way, I wanted to do previous year even if I pick a different year. So if I have 2022, I want to see 2021, but if I pick 2021, I want to see 2020. Make that happen. So for that, you're going to need a DAX calculation to give me previous year, no matter what. I don't care what somebody has selected on the screen, how they want to slice and dice it, what year they pick. I always want to show the previous year. And I could do the same thing for previous month, a previous quarter as well. So actually the DAX command is previous period. So any time frame that somebody has up there, show me the previous one, I can do that. And that you're probably going to want to write code for. That's a great use of dax.

Okay, so this seems pretty cool. It also sounds like a lot of the DAX functionality is based around business analysis type tasks where you're looking at, like, how do metrics change over time? Or I guess, yeah, calculations for different sort of business periods. Yeah, can you maybe talk us through, like, what are the range of, like, different functions available within dax?

There's a whole bunch of them. Like, there's a lot of things that you might also want to do in dax, because I've also done it for, for calculating different goals. Like, for example, I have a customer now where their target goal is 85% of the total of the number of users for the previous month's gross sales. And so that's a specific business calculation that they have that they want that for their goal. Well, that would be something else that I could do with dax. The other thing is maybe you want to do something like, let's say that I'm doing a sales call where I want to know how many calls that they have done. And I may or may not have that organized. You know, data is how it is, so that I can count exactly everything that they did in a particular table. So I can just say, give me all of the rows. I want to count all the rows in that table. And I can do that too. So if I want to filter it out, you know, just for this sales guy, you know, make sure that that count works. So making sure all of that works, make sure that it's really contextual or let's say that you don't want it contextual. Let's say that you are creating a Power BI calculation and you don't want it to change. You always want it to show a certain value. You can do that with a DAX calculation. You can do this with a couple different ways in Power bi. But one thing that you can do is make it so that certain filters don't apply if you don't want them to.

So when you were talking about it seemed like a lot of the use cases very much around, like efficiently doing like the same kind of analysis over and over again. Is that also the case with dax or is it more about just giving you flexibility for different kinds of analyses?

So a lot of times when I'm doing setting up Power BI for companies that I do consulting with, a lot of people know exactly what they want on a report. They can tell you who is it supposed to go to, what kind of things that they want to see. But sometimes the way to do this is not exactly easy. So like telling people how to write a previous year calculation or calculating all of the goals. I don't expect everybody who writes reports to necessarily know everything about it. So one of the things that I try to set up is self service reporting. So if I have people tell me what kind of calculations that they use all the time, then we can put that in the Power BI model and somebody who doesn't know anything about DAX can just use that and say, oh, that's what I want and use that as a calculation without having to know how to write it. Because some of this can get tricky.

Okay, can you give an example of something that's a tricky thing to do? What's the sort of the advanced use case?

Then there's a function that's very, very powerful in DAX called calculate. And the way that it filters things out is not necessarily something that people understand. Like for example, let's say that I'm calculating a profit. So basically with Prof. Profit, what I want to do is I want to take what I sold it for minus the cost. Well, you can't just sum those two columns up and then subtract it at the end because it doesn't give you the same value. If you are looking at it maybe just for the eastern region, what you really want to do is you want to do that at the line level. So like With Excel, you don't want to do it. Add it all up and then do it. You want to do it for each row. Well, that's something that a lot of people wouldn't necessarily get. They'd get that their calculation was wrong. And so you're like, oh no, no, no, that's a row level calculation. I need to use an iterative function to make sure that your profit will be correctly calculated no matter how you have it sliced. And that's not something that a lot of users really understand if they haven't done much dax. So knowing when to use a row versus column summarization, basically.

And with em, you were talking about a good way where you can sort of do point and click the first time and then you've got the code available. Is there a similar sort of functionality for DAX where maybe if you're not confident writing the code the first time, there's a way to do it with pointing and clicking.

Yes, there is. There is a function called quick measures and you can have power bi write the code for you and you basically there's a list of things that you want and then it can do it for you. Microsoft also has in beta what they're calling natural language dax. This is still a preview feature, but the idea being you just tell it what you want and it'll write the DAX for you. So they're trying to make it easier and easier for people who don't understand how to write DAX to have power bi do it for you.

So this sounds sort of a little bit like when you do a search query on the Internet and it sort of just figures out what you're trying to say and then gives you the results. Is that the idea?

It's kind of like that. But you, you need to know a little bit. Like for example, it'll write all sorts of really cool like time based calculations or doing rolling counts or rolling averages. You just need to know a couple of things. You need to have a date table and you need to know what the date value is. And that's like the trickiest part of it. And I want to summarize this and then pick that. But that's the hardest part is knowing. And that's not exactly a gut buster. There is knowing the name of your date table and the name of the date column. But that is what it will ask you.

Hopefully you would sort of know the names of the columns and things in your tables if you're doing some sort of analysis. I mean, I guess that's not always.

The case and they're not. It's not like a hidden name, it's the name that's over there on the right hand side. But you will be prompted for that.

So have you seen any sort of success stories where organizations have gone from like just using Power BI in a point and click way to actually implementing sort of more programming strategies and then things have changed for them?

Yes. Well, so generally speaking, you can get away with a lot until you are worried about scaling and reusability. And that's when if you don't have the basics down, it starts falling down. Because yeah, you can get a report to work, you can get two reports to work, but if you start doing five, six, seven, you're like, I'm doing the same thing over and over again. And this is kind of slow. I wonder why. That's when it starts to matter how you're doing it and wanting to do it. Using DAX and getting things more organized in M starts getting to be more and more important to how the organization is operating. I was talking to a corporation and they said it takes weeks to develop a Power BI report. I'm like, why? And then I found out that every single time they were doing a Power BI report, they were doing a brand new model and they were writing everything in it. And I'm like, it's a lot of work. I'm too lazy to do that. Don't do that.

That does sound like there's opportunity for like a really huge just performance improvement there rather than doing everything from scratch every time.

I will say that the report performed really well, but it took two weeks to make it and then the maintenance on that was ridiculous.

There's a big trade off there then in terms of what you're getting out of it for the effort you're putting in. All right, so we've not talked about Python and R yet and certainly my background was primarily in R programming. So one thing that sort of put me off getting really into Power BI is like, well, I was like, well, I already know a programming language, why do I need to learn this new tool?

There's a lot of reasons. This is like perfect. One thing I thought was like, well, people like you, they're like, well, you already know R, you know how to do visuals in R. But distributing that's not so easy. If you want to show somebody all of your R code, how do you make that happen? How do you distribute it?

That's very true.

So this was designed to make that part of it easier because you can do a number of different things in R. But one of the things that you can do with R is you can use the existing data model and then use it to create your own data frames and then make those visualizations off of those data frames that you already know how to do. Well, the reason you might want to do that is for one, you can distribute it using Power BI's distributed on the cloud, where you put it to the Power BI service and people have access to it and then that provides a security mechanism. But you can also use, if somebody's built it into the data model, the data security within that model so that you could create an R visual and only the people who've got rights to see that data can see that visual and you didn't have to write all of that.

Okay, that does sound really useful for collaboration, especially when you've got different teams with different skill sets. So some people can create the Power BI dashboards like using M and Dax or pointing and clicking even, and some people can use R or Python or whatever they're comfortable with. And you've got this common dashboarding format, right?

And you can combo it because like, for example, let's, you know, Power BI's got a lot of built in visualizations and a lot of them do like what you want. Hey, you want a bar chart, column chart? I mean, nobody needs to write R Python code for that. But you know, maybe you want to do a clock visual or you've already got the code written and you don't just like, I don't feel like dealing with this. And so you just want to incorporate it or it's something that you do some data manipulation. You get the data, but you do some things to it to make it a little bit better for showing a certain thing. That's something that you can do with R and Python and you can incorporate it if you wanted to. You could do one visual R.1 visual is Python and the rest of it could be native Power BI visualizations that you can just drag on.

Okay, so you've kind of convinced me I should try this. What would I need to do to get my R code into Power bi?

Couple of things. R will not natively work in Power BI if you don't have an R environment installed. Obviously you do, so that's not a big deal. Other people don't have to have an R environment to run it, but they do to be able to if they wanted to modify what you're doing. Same with Python, you need to have a local Python environment installed. Also, I'm assuming that you are going to want to distribute it using the Power BI service. And to do that you need to make sure that your administrator hasn't turned that off. Because they have the ability to do that. They can turn off the ability to do RPython, and generally speaking, they don't. But no, it's possible. And you can test that by just creating a small visualization, loading it up. If it works, great. If you see a giant red X where your visual was, it's turned off.

So that all sounds fairly straightforward. Maybe the hard part is just keeping friendly with the person who's administering the software. Always a challenge in any organization.

You don't have to know who it is. You can figure out the giant X in the sky without even talking to them. But one thing is, if you're writing R, Microsoft has a website where they list R libraries that are supported. You need to make sure that what you're using is on that list. It's a pretty significant list. But if it's not on that list, you'll also get that giant red X, because the idea is that when you move it to the Power BI service, that's the R environment that it's using. And they don't have every single solitary library included. So just keep that in mind.

Okay, so perhaps using internal packages at your own company, those then won't be available.

Those will not be available, no, because it's just cran. They're CRANR packages exclusively.

Okay, well, I suppose maybe that's an excuse to try and get your own work published on cran, make it a little bit more famous. CRAN is the online package distribution place. So is the same true of Python? What's the sort of flow for working with Python and Power bi?

It's very similar, except the fact that they said we do Anaconda. So anything in Anaconda they have, and they have not changed that list of libraries. So you're using Anaconda libraries. If it's in Anaconda, you're good. If it's not, it won't work.

Okay, so there's two package distributions with Python. So you've got Anaconda and you've got pip. So you've got to make sure that using the Anaconda distribution. That's a useful tip there. And are there any sort of limitations on what you can do with Python beyond just the limited number of packages?

One of the things about Power BI is interactivity. So if I have a column chart and a bar chart and a slicer if I click on them, they're fully interactive. One impacts the other one with R and Python visualizations that you write, it's one way filtering. So I could click on a slicer, it would impact what is shown in my Python visual. But clicking on my Python visual wouldn't affect any other visuals that I have in Power bi. So it's one way.

You've not got the connectivity between several different plots or widgets in your dashboard.

Yeah, so it's controlled by Power BI visuals. R and Python visuals do not control any Power BI visuals. So they don't filter them.

So can you tell a little bit more about how this is going to play out in a corporate setup? So perhaps you've got like your data analysts doing sort of native Power BI and you've got your data scientists using Python or R and things like that. So who else would need to have access to Power bi?

That's basically you have people that are writing reports to any degree. Your data analyst needs to make sure that they need to work within a Power BI model. So they can't just like, here's my R code, you have to make it work within Power BI so that, for example, the data can be refreshed. And if you're going to incorporate like a Power BI security model, you have to include that within your data model. Your data model has to be something that is supported in Power bi, which can be as much as a table. But like if you wanted the security, you have to add the security, the tables, et cetera, that are, that are loaded within it. If you have created Python code or R code in Power bi and let's say I've got somebody else who's using that same data model, they don't have to use it. So it's one of those things that if you know what you're doing, great. If you don't know what you're doing, it's not going to hurt you because you can't really tweak it if you have it set up so that they just using the model.

So you mentioned something about a Power BI security model. I've not heard of this before. So can you tell us about what that is?

So that's basically something like people create. So let's say that, for example, I had a client like this, they bought a company in Hawaii and as part of their agreement with the people that they purchased the company from, they limited the number of people who could have access to the data in Hawaii. Well, that meant that you needed to have a list of users that could see Hawaii and basically everybody else could not. How you're going to do that is you're going to need to associate a list of users with permissions. Now, every place you're fully able to do this, integrating your active directory, and we put that in Power BI Azure Active Directory, assuming that you've got your networking locally and on prem, generally speaking, it's all the same. However, everywhere I've gone, they're like, yeah, our active directory is not that clean, so we really don't have a way of doing it. It's a great idea, but nobody has it set up, right? So what I end up doing is creating tables that basically say the list of things that people can see. And I use that to filter what Power BI users then have access to. So I create a security model within Power BI that has a list of users and what they can do and that filters the data model so that they don't see things that they can't see. So if I had Hawaii access and you didn't, you'd never even know that Hawaii was there because when you log in, you wouldn't see that state. You would have a list of everything you'd see. You'd never see Hawaii because I filtered that out for you.

Interesting. And so because it's done in Power BI, right next to the dashboard, there's basically no way you sort of passed it. It's like an end layer security, right?

So it uses your Power BI ID that you use to log in. So just a little caveat, it's designed for when you deploy this to the Power BI service, When you deploy to the Power BI service, generally speaking, I hope that people are using applications instead of workspaces. If you use a workspace, then you only can give people reader rights. If you give somebody more than reader rights, then the security doesn't work anymore. If you're using an app, which is what the preferred best practice method is for distributing, then yeah, it works fine. But it's going to take your user ID and the data that you have in tables and basically saying, okay, well we're filtering this out. So what I find interesting is where this data comes from, right? So I do not know who gets to see who, why and who doesn't. So what I see most often is Somebody's created a SharePoint site, they have limited number of people have access to and they also have versions, they can see who changed it when. And that person or persons is then in charge of maintaining that list. Because I don't want to do it, and then I pull it from SharePoint. And that's the most common way I've seen of doing it. And people are like, you tweet all this in SharePoint? I'm like, yeah, because it's easy and somebody's got to maintain that list. I also have companies that create applications that have databases, and that's really nice and easy too, but that's the exception, not the rule.

Actually, that's an interesting thing to talk about is if you are working with databases, is programming with SQL, then also an option for working with Power bi?

Well, ideally, if you are bringing data in from a database, and I don't care if it's SQL or Oracle or Redshift, I hope that you do as much data cleansing and cleaning and fixing in their source systems because then Power BI doesn't have to do it, which makes it faster. So while you can write SQL to get data, it's not necessarily recommended. What the best practices is to create views on your database and then use those views and read them into Power BI that has the data normalized in the way that you need it.

Okay, so rather fix the data once in your database server where you've got plenty of computing power, rather than doing every time on your data visualization or dashboarding server.

Yeah. The other benefit you get with that is that if you create a view, then it's really obvious to your database administrator especially you can write materialized views so that people don't like change database underneath you. And like, I didn't know you were using it. Well, I have this view right here that will not let you do it like delete a column so you have higher visibility with that as well. So it's another reason to do it that way.

All right. Before you're talking about people making mess of Active Directory and things like that, I'm kind of curious as to what are the mistakes organizations make when they're trying to roll out Power bi.

They don't think about security until it's too late, then it's harder to do. They don't understand what's involved in getting business users to use reports, because I'd like to set it up so that they're very, very successful. If I'm doing that, I'm going to create not only a data model with what they've told me that they need and it'll change over time, but so that somebody can just pull the data that they need off, put it on the Power BI canvas and make reports off of it and everything. Good. But I'm also going to create a template so that the colors and the formatting are all fixed as well. Because it just saves so much time if you know every single time that you need to create a Power BI chart, you need to go in and change the colors and change the font and change the header. It just gets tedious. And I can create a formatting file that has all of that in there. And oh, by the way, I'll give you a template that has a company logo and then where it needs to be too so you don't have to think about it. So that just makes it easier to do report development and that's people who don't necessarily have to know a lot about Power BI to be successful at it. So ideally I think you should have a template. You should have a Power BI report template, a JSON file containing all of the colors and then your data model separate, that has all the security and DAX that people need to write the reports that they need to write.

Brilliant. Yeah, certainly like adhering to corporate color schemes and things like that. It's one of those like big sticking points in a lot of analysis cases. The analyst like, well, I don't really care about this. And then some of the marketing's like, no, you must have it in the right colors. So having that automated seems like a huge time saver and certainly like saves a lot of cross department arguments.

You think I don't like that color green? I don't really care. Marketing gave it to me.

Yeah, I've had that conversation many times. All right, so we've talked a bit about the sort of corporate usage of Power BI and I want to sort of talk about the personal usage now as well. So there's so many like job adverts saying, okay, we want some Power BI skills. So when a job description says, okay, we want you to know Power bi, what sort of programming skills do you need with Power BI as part of that?

If you are doing an interview for Power bi, I would anticipate that they will ask you some questions for how to write DAX and probably a little bit of M. But I definitely DAX because it's a skill that a lot of people haven't invested a lot of time in. So that's something I can't imagine. You wouldn't be asked that in an interview.

Okay, so DAX is probably the place to start then, if you want to start programming with Power bi.

Yeah. And if you want to practice it on a personal level, the most elaborate Power BI reports that I have Seen people create are done for fantasy leagues.

Oh, is these like their pretend you own a sports team kind of leagues?

Yes. I know somebody who does a whole bunch of stuff with fantasy football. And you can download all of the stats from wherever it is you get stats, ESPN or whatever. And you can import those directly into Power Bi and then refresh them so that you always have the latest in stats of who did what when. And you could incorporate that into tables and write reports off of it. I've also seen people who do that with their personal fitness trackers so that they know if you keep all of your workout data. I know somebody, their gym had a little barcode and they would enter how many reps that they did so that they had all of this stuff about their personal workouts that they've collected and put into Power Bi is kind of a I need to teach myself how to do this kind of thing.

Okay, these seem like really great ideas if you want to kind of build out a data portfolio to show off when you're job seeking. Just talk about your hobbies and build that into some kind of dashboard. I have to say, if I was doing like a gym one, there'd be far too many, oh, zero reps missed that day. Maybe don't show off your worst features, but if you got a hobby you're interested in, then that seems like a really cool way to make yourself.

Yeah, because we've talked about this stuff about connecting to databases, but you can also scrape from websites too. I've got a demo that I do where I do a whole bunch of scraping from box office Mojo and I've written some M so that I can use like however many pages it is for like the highest rated movies of all times. And I've done various reports off of that. So there's a lot of things that you can look at if you're looking to build your portfolio. The stuff that people be really interested in. Like, you know, what movies were most successful in India if you wanted to do do that, you know, versus the U.S. you could easily create a data set to do that and show somebody that they might be actually, you know, find kind of interesting.

Absolutely. All sorts of amazing public data sets around these days, particularly like government data sets like the required to sort of publish what they've been researching.

Gapminder.

Oh yes. Gapminder is a classic at this point for sure. Yeah. So many, many portfolio opportunities. Certainly get those into your Power Bi dashboards. All right, before we wrap up, do you have any final advice for people wanting to start programming with Power bi.

Well, one thing about Power Bi is that it is free. The only limitation you have on free stuff is sharing. But if you're trying to build a portfolio, you can definitely do that. And then when it comes to interview, you can't share out what you have distributed. But you can log into Power BI and then show somebody the interaction activity right in front of them. In addition to, say, putting all of your pbix files into GitHub, I will tell you one trick. I don't know where this is documented. I should blog this, but let's say, for example, that you want to give somebody your Power BI report and you give them some data files and you want them to be able to access it on their computer. If you go into M and make the location for all of those files a parameter, they can just enter where they have the parameter, and if you save it as a template file, they'll be prompted immediately for where do you have all the files? And so that you can instantly show somebody, hey, put the files wherever you want, and then they can load it and play around with it. Works better than if you just have the data model.

All right, that's a really great tip. Very useful to know. So thank you very much for your time, Ginger. This has been really interesting. I think I have to get started doing some programming with Power bi. Thank you very much.

Thank you. You've been listening to Data Framed, a podcast by Data Camp. Keep connected with us by subscribing to the show in your favorite podcast player. Please give us a rating, leave a comment and share episodes you love. That helps us keep delivering insights into all things data. Thanks for listening. Until next.