How Power BI Empowers Collaboration

Richie Cotton: Welcome to Data Framed. This is Richie. 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 outta the software by doing a little bit of program.

In fact, Power BI has two programming languages built in. Namely M and Dax. Explaining when and why you'd want to use these languages is Ginger Grant, Principal Consultant at the Desert Isle Group. Ginger is a Microsoft data platform mvp, and frankly an all-around expert on machine learning, 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 gonna be talking about programming with Power BI, and I guess a lot of people, they're gonna think, well, isn't Power BI a point and click platform? If I want to learn programming, I would've learned some Python. So can you just maybe talk us through why you'd wanted to go and learn programming for Power BI.

Ginger Grant: 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 at scale and in 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, cuz 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, Use that rather than filter this on that

Richie Cotton: So it's gonna give you the opportunity to reuse your code and therefore do less work, I suppose, is the goal.

Ginger Grant: More control. And it's one of those things that's when are you gonna do the work? Are you gonna do it every single time you wanna do something or you're gonna 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.

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

Ginger Grant: So there's a lot of different ways you can do programming in Power bi embedded into Power BI. There are two different languages. There is M, which is primarily used for e tl, 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 in Python visuals within Power BI as well. If you want to. There's some limit. If you're doing R you wanna make sure that you look at Microsoft's lists of the 2000 or what some odd libraries that they have to make sure that the one that you want is gonna be included and you can do Python as well.

And that includes Anaconda, including, you know, map, plotly, and Seaborn, which are the ones most people use.

Richie Cotton: 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 D is for the data analysis, and then I guess if you wanna do some fancy machine learning or database or whatever, then you are using Python or R, is that correct?

Ginger Grant: If you just have some R visuals or Python visuals that you really like and you're like, you know what, I wanna 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.

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

Ginger Grant: Well, let's start at the backend for M, 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 and tax, 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 wanna 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 Tyler 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 wanna 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 gonna get rid. Now, if I could do that at the source, great.

What 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.

Richie Cotton: 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 gonna be. So I guess your suggestion is that you should be using M upfront and just. Really sorting your data out there. Are there any other sort of use cases for M beyond this sort of like cleaning up reporting data? Have you seen any good uses of it?

Ginger Grant: Yeah, it's got a couple of other like key tricks that you wanna do. If you wanna 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 gonna do a what's called a trunk and load.

So every single record is gonna be deleted, and then every single record is gonna be added again. Now you got 2 million records that's gonna take some time, whereas maybe you only have 15 records that were changed from yesterday. So if you wanna set that up and make it so that you're just getting the data from yesterday, you need to do that in Emma as.

And that's also a place where you can create variables that you can use for connecting to 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

Richie Cotton: so that seems like a really useful performance thing rather than trying to work on the whole latest set every time. Especially if it's getting bigger and bigger. You're just working on the latest bit,

Ginger Grant: makes it much faster.

Richie Cotton: So kind of curious, for people who are new to M, where do you get started? Like what are the sort of first things you need to learn about?

Ginger Grant: The one thing that's great about m is it's very, very forgiving. So when you do anything in M, 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 cuz I was writing some Python yesterday.

I'm like, shouldn't have done that. Can't go back. You know, I just wanted, 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 Core is you can do that. 

Richie Cotton: So, 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?

Ginger Grant: Well, what's interesting about Emma 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 gonna 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 gonna show you what is in the code. Behind the editor on it is not very forgiving. Shall we sign?

Unlike the rest of M. So the first thing, if you're gonna 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 can cancel everything out or you can just look at no pet. I recommend that people do with that, and then it's really kind of picky. One of the things that the advanced center you see is that references the previous step every single time. So if your previous step was renamed 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 cuz I need to put that hashtag previous stuff name in there.

And then I.

Richie Cotton: Okay, so it sounds like you got this balance where, First time you write something, it's gonna be done using pointing and clicking. And then if you wanna reuse that, then you've got the code there that makes it easy to just click run and you, 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 there, right? They're all either really pointing 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. Before, and 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 differences between Power Query and m then?

Ginger Grant: 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 decks 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, 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.

Richie Cotton: Alright, fair enough. And are the tools within Power Query beyond this um, program?

Ginger Grant: Nope. That's another thing you, when I talked about writing, you know, R or Python or Dax, you can't do any of that in Power Query. Can only do M.

Richie Cotton: And in terms of the tooling for this, so is this just something you would use as like a standalone tool or the different ides that you can use for power?

Ginger Grant: Well, power Pickering 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's, 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 goes with people off.

Richie Cotton: 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.

Ginger Grant: Anybody can really get started using it. One thing that that's really important to know is, 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 could 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. This is what's really kinda nice about playing with it.

Richie Cotton: Alright. Now we'll get to the fun part where we do data analysis. So let's talk about Dax. Can you give. Overview of what Dex is.

Ginger Grant: 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 gonna wanna start, making some calculations that are not something that you can easily, you know, click and drag your way to. Like, for example, let's say that you get, it's time to write reports and somebody tells . You that I wanna make sure that I can look at previous.

and I wanna look at the 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 wanna see 2021. But if I pick 2021, I wanna see 2020 make that happen. So for that, you're gonna 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 wanna slice and dice. What year they pick. I always wanna show the previous year and I could do the same thing for a previous month, a previous quarter as well. So actually the DAX command is previous period, so any timeframe that somebody has up there, show me the previous one.

I can do that. And that you're probably gonna wanna write code for, and that's a great use of Dax.

Richie Cotton: Okay, so this seems pretty cool. It also sounds like a lot of the decks functionality is based around business. 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?

Ginger Grant: there's a whole bunch of 'em. Like there's a lot of things that you might also wanna do in Dax, because I've also done it 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 months gross sales. And so that's a specific business calculation that they have that, 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 wanna do something like, let's say that I'm doing a sales call or I wonder how many calls that they have done, and I may or may not have that. You know, data's 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 wanna count all the rows in that table, and I can do that too. So if I wanna 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.

Richie Cotton: 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?

Ginger Grant: So a lot of times when I'm doing setting up Power BI for companies to do consulting with, a lot of people know exactly what they want on a report. They can tell you, you know, who is it supposed to go to, what kind of things that they wanna 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 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.

Richie Cotton: Okay. Can you give an example of like something that's a tricky thing to do? What's the sort of the advanced use case?

Ginger Grant: 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 profit, what I wanna do is I wanna 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 wanna do is you wanna do that at the line level. So like with Excel, you don't wanna do it, add it all up, and then do it.

You wanna 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 stacks. So knowing when to use a row versus column summarization basically.

Richie Cotton: And with M, 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?

Ginger Grant: 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 ds. 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.

Richie Cotton: 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.

Ginger Grant: 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 wanna 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, that is what it will ask you.

Richie Cotton: Hopefully you would. Know the names of the columns, things in your tables if you're doing some sort of analysis. I mean, I guess that's not always the case.

Ginger Grant: 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.

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

Ginger Grant: Yes, well, so generally speak, 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 and starts falling down, because yeah, you can get a report to work, you can get two reports to work, but if you start doing 5, 6, 7, you're like, I'm doing the same thing over and over again, and this is slow.

I wonder why. That's when it starts to matter how you're doing it and wanting to do it using. And getting things more organized and 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.

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

Ginger Grant: 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.

Richie Cotton: 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. Alright, so we've not talked about Python and R yet, and certainly my background was primarily in our 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 programming language.

Why do I need to learn this new tool?

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

Richie Cotton: That's very interesting.

Ginger Grant: 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 wanna do that is for one, you can distribute it using Power bi. You know, 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.

Richie Cotton: 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 d or pointing. Can give in and some people can use our or Python or whatever they're comfortable with, and you've got this common dashboarding format.

Ginger Grant: Right? And you can combo it because like for example, let's, you know, power BIS got a lot of built-in visualizations and a lot of 'em do like what you want. Hey, you want a bar chart, column chart? I mean, nobody needs to write our Python code for that. But you know, maybe you wanna 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 wanna incorporate it or it's, you know, something that you do some data manipulation, you get the data, but you do some things to it to make it a little. Better for showing a certain thing. I mean, 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 with R one Visual. It's Python and the rest of it could be Native Power BI visualizations that you can just drag on.

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

Ginger Grant: 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 gonna wanna distribute it using the Power BI service, and to do that you need to make sure that your administrator hasn't turned that off cuz they have the ability to do that. They can turn off the ability to do our python. And generally speaking, they don't. But no, it's, 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.

Richie Cotton: So that all sounds fairly straightforward. Maybe the hard part is just keeping friendly with the person who's administering the software, so or always a challenge in any organization.

Ginger Grant: 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. . Microsoft has a website where they list our 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 redx because the idea is, is that when you move it to the Power BI service, that's the our environment that it's using, and they don't have every single solitary library included.

So just keep that in.

Richie Cotton: Okay, so perhaps using internal packages at your own company does then won't be available.

Ginger Grant: Those will not be available. No, cuz it's just cran. They're cran R packages exclusively.

Richie Cotton: 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. Kran 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?

Ginger Grant: 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. It's not, it won't work.

Richie Cotton: Okay, so there's two package distributions with Pythons. You've got Anaconda and you've got pip. So you've gotta 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?

Ginger Grant: One of the things about hi bi is the interactivity. So if I have, you know, a column chart and a bar chart and a slice, 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.

Richie Cotton: You've not got the connectivity between several different plots or widgets in your d.

Ginger Grant: Yeah, so it's controlled by Power BI visuals. Our Python visuals do not control any power BI visuals, so they don't filter.

Richie Cotton: So can you tell me a little bit more about how this is gonna 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 R and things like that. So who else would need to have access to Power bi?

Ginger Grant: 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, you know, 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 gonna hurt you because you can't really tweak it if you have it set up so that they're just using the model.

Richie Cotton: So you mentioned something about a power BI security model. Heard this before, so can you tell us about what that.

Ginger Grant: 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 plate, 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 kind of not that clean. So we really don't have a way of doing that. I mean, it's a great idea, right? But nobody has it set up, right. So what I end up doing is creating tables that basically say, uh, the list of things that people can.

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, cuz 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.

Richie Cotton: 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.

Ginger Grant: Right, so it uses your Power BI ID that you use to log in. So just a little caveat, it's designed at, you know, 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 writes, if you give somebody more than reader writes, 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 gonna take your user IDs 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. And they also have versions so they can see who changed it when. And that person or persons is then in charge of maintaining that list cuz I don't wanna 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, what? You tweet on this and SharePoint? I'm like, yeah cuz it's easy and somebody's gotta 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.

Richie Cotton: 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.

Ginger Grant: 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 your 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 'em into Power BI that has the data normalize in the way that you need it.

Richie Cotton: 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.

Ginger Grant: Yeah. The other benefit you get with that is that you, 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 the 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.

Richie Cotton: Alright, before you're talking about people make a massive active directory and things like that, I'm kind of curious as to what are the mistakes. Make when they're trying to roll out how bi.

Ginger Grant: They don't think about security till it's too late than 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. So if I'm doing that, I'm gonna create not only a data model and then they, with what they've told me that they need and it'll change over time, but so that they, 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 gonna create a template so that the colors and the formatting are all fixed as. 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 to so you don't have to think about it. So that's 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 J S O N file containing all of the colors and then your data model. , it has all the security and Ds that people need to write the reports that they need to write.

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

Ginger Grant: You think I don't like that color green. I don't really care. Marketing gave it to.

Richie Cotton: Yeah, I've had that conversation many times. Alright, so we've talked a bit about the sort of corporate usage of Power bi and I wanna 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 the Power BI as part.

Ginger Grant: If you were 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 'em, but I definitely Dax because it's the 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.

Richie Cotton: Okay, so Dax is probably the place to start then if you want to like start programming with Power bi.

Ginger Grant: Yeah, and if you wanna practice in it on a personal level, the most elaborate power BI reports that I have seen people create are done for fantasy leagues.

Richie Cotton: Oh, these like the, pretend you own a sports team kind of league.

Ginger Grant: 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, you know, E S P or whatever, and you can import those directly into Power BI and then refresh them so that you always have the latest in stats. So like 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 track. So that they know, you know, like if you keep all of your like 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 as kind of a, I need to teach myself how to do this, that kind of thing.

Richie Cotton: Okay. These seem like really great ideas. If you wanna kind of build out a data portfolio to show off when you're job seeking, just talk about your hobbies and build it into some dashboard. I have to say, if I was doing like a gym one, that'd be far too many. 8 0 0 reps missed that day. Maybe don't show off your worst features, but if you've got a hobby you're interested in, then that seems like a really cool way to make yourself No.

Ginger Grant: 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, 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 that, you know, versus the US, you could easily create a data set to do that and show somebody that they might be actually, you know, find kind of in.

Richie Cotton: Absolutely all sorts of amazing public data sets around these days, particularly like government data sets, like. I to sort of publish what they've been researching. Oh yes. Gap Manager is a classic at this point for sure. Yeah, so many, many portfolio opportunities certainly get those into your Power BI dashboards. Alright, before we wrap up, do you have any final advice for people wanting to start programming with Power bi?

Ginger Grant: Well, one thing about Power BI is that it is, 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 interactivity, you know, right in front of 'em.

In addition to say, putting all of your P B I X. 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 enter where they have the parameter and, 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.

Richie Cotton: Alright. 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'll have to get started doing some programming with Power bi. Thank you very much.