 Welcome to our webinar, Power Up Your Data with Microsoft's Power BI. Thank you so much for joining us today. I'm Susan Hope-R, the Training and Education Manager here at TechSoup. This webinar is supported by a generous grant from Microsoft. And we do want our webinar content to be relevant to the important work you do as nonprofits and libraries. We thank you in advance for answering our survey questions, which will come at the end of the event. This will help us better understand your organization's needs. Before we get started, I do want to talk a little bit about our webinar platform, and I want to make sure everyone feels comfortable using the platform. On the left-hand side of your screen, there's a chat box. At any time during the event, you should feel free to chat your questions. You can also chat any technical difficulties you're having. So if you can't hear or see, please chat us and we will try to help you. When you do chat in a question, we'll be flagging your questions and queuing them up for later review during various Q&A sessions. So don't be alarmed if we don't answer your question right away in the chat box. We'll be queuing those for Jordan to answer periodically. If you lose your Internet connection, you can always reconnect using the link in your registration or one of the reminder emails that was sent to you. If you're hearing an echo through your computer speakers or having issues with the audio, your option is to dial in using the toll free line listed in your registration email or the one that Becky is chatting out right now. We are recording this event, and you'll be able to find this recording at TechSoup's webinar in about a week. This is where we share all of our webinar recordings and also announce upcoming webinars. We encourage you to check it out, www.techsoup.org, slash community, slash events, dash webinars. You can also view our recorded webinars and videos on our YouTube channel, and that's www.youtube.com slash TechSoup video. In a few days, you will receive a follow-up email from this event, and the email will have a link to the recorded presentation, the PowerPoint, a link to the PowerPoint presentation, and any resources that we share today. You can access the PowerPoint presentation. It was included in the reminder email that was sent about an hour ago, and that can be found on the right side of the reminder email under downloadable files. But you don't need to worry over much because we will be sharing that out again. And Jordan is also going to be doing some live demo in Power BI so that you don't need so much to worry about the PowerPoint. Also, if you're following along with Twitter, you can tweet us at TechSoup or use hashtag TSwebinars. I do want to talk a little bit about Jordan today. He is our presenter. He's a data analyst and storyteller at Tech Impact. His work is guided by the idea that information technologies can be an invaluable resource for supporting global social progress if their design and implementation reflects that goal. Jordan has 10 years of experience in systems and network administration, technical writing and education, and technology policy analysis. Jordan has volunteered in a number of schools and nonprofit organizations to help advance digital literacy and inclusion and continues to do that whenever he can. As I mentioned, I am Susan Hope Bard. I'm the Training and Education Manager here at TechSoup. And also joining us today is our wonderful webinar program manager Becky Weekend. And she's on the back end. She'll be chatting things out and also helping you if you have any technical difficulties. And another thing I wish to add is that Jordan has also been instrumental in helping us develop some articles and clips and tips about Power BI which will be coming out a little later this year. So a special thank you to Jordan. Our objectives for today, we're just going to be able to understand, we hope you walk away with an understanding of the basics of data visualization using Power BI. And also understand some of the basic steps to effectively prepare and import your data into Power BI. And of course we do want to answer your questions. A little bit about TechSoup. TechSoup is located in beautiful San Francisco, California. And I'd love to know where you're joining us from today. So please take a minute to chat your location in the chat box. And while you're doing that, I'll talk a little bit more about TechSoup. We're a 501-C3 nonprofit like many of you joining us today. And what we do is we work to empower organizations around the world to help them get the latest tools, skills, and resources to help them achieve their mission. And you can see from our map here we serve almost every country in the world. And we have 62 partner NGOs. The need is global. And we've helped organizations from all over the world get more than $5.4 billion in technology products and grants. And these tech products and grants come from more than 100 corporate and foundation partners. So I'm going to be turning this over to Jordan in a moment. And I just want to remind everyone that we are recording this. So if you miss something while he's doing the demonstration in Power BI, we may not be able to go back and do that exact thing again, but you will be able to watch it in the recording and be able to pause it at your convenience once this is produced. So I want to welcome Jordan. And thank you very much Jordan for joining us today. The floor is yours. Jordan Thank you very much, Susan, for that really kind introduction. And I'm very happy to be here today. I just want to thank all of you about Power BI. I'm going to do the obligatory thing and talk a little bit more about my organization, Tech Impact, before we go into the substance of today's conversation. So Tech Impact is a 501c3 nonprofit that exists to help other nonprofits negotiate the increasingly mandatory and also increasingly complicated world of information technology. And we do that in a wide variety of ways. We provide various IT services. And the goal of most of those services is to help nonprofits remove as many technical barriers as possible from their day-to-day work and offload as much of the technical complexity associated with their day-to-day work onto systems that are in the cloud managed by someone else. So you just don't need to worry about that anymore. Get rid of that old clunky server gathering dust in your back closet that dies every other week. That's sort of our unofficial model I think. But we also noticed over the past year especially a very pronounced spike in the need in the nonprofit space for data-related services and support. Because increasingly data is the common language of our world and funders and constituents and policymakers and your boards often want to see metrics showing just how much of an impact you're having. And that can be really hard if you don't have a centralized database system set up, ready to go, and good visualization tools for taking the data from that system and making meaning out of it. And so we can help with all of that, getting systems in place, data collection, analysis, etc. And we love the unique sorts of presentations because again our goal is to increase the technical capacity of the nonprofit community and we see these presentations as a great way to do that. One other thing I want to mention about who we are before I move on is just that we also have a fantastic workforce development program for at-risk young people in Wilmington, Philadelphia, and Las Vegas which brings young people from underprivileged backgrounds into a really robust training program and then allows them to bring their skills back into the local community. So a really fantastic program. Okay, in case you're wondering exactly who you are speaking to, this is a bit redundant especially given that Susan said many more nice things about me than I probably justified. But anyway, this is me. And the bottom line is what my title means aside from being the best title I've ever had is that I work with nonprofits all along with data maturity spectrum from nonprofits who are just getting started with data analysis. And in fact, they've never really tried to collect data at all before all the way through organizations that have really robust data systems that are already in the cloud and connected to like eight different online sites like let's say MailChimp, Eventbrite, FormAssembly. You get the idea of various online services that help to collect data. And that's a great thing to have but it requires care and feeding to keep such a complex system working. So what I find is something like I'm doing. But the thing that I enjoy doing the most honestly is the data visualization and analysis because collecting data is great, but data is really just a pile of numbers unless you actually take it and make meaning with it. And that brings us to the first slide of our official presentation which I suspect I don't need to spend that much time on because if you're here you probably already care about pretty charts and graphs. But I nonetheless wanted to take a moment and make the case so that in part you have some talking points to take back to your organization. If anyone ever comes up to you and says why are we wasting our time and resources on data visualization, we should really be focusing on service delivery. And that's a very fair thing to say. We're all in nonprofit work because we care about the mission of our respective organizations. We're not necessarily here to spend all of our time visualizing data. However, data visualization is a field of practice and something that has become increasingly common and seen as almost obligatory in today's world is a very powerful tool that can help you to actually achieve your mission better. More efficiently in a smarter way because the whole core of data visualization, the whole point of data visualization is that it makes information more accessible. When data visualization and its heart is the art and science, it's kind of half of each, of taking a bunch of information that would otherwise be very difficult to process, to extract meaning from and helping people really quickly zoom in on relevant takeaways, on insights that help them understand how past decisions may have impacted the performance of the organization, whether it regards to fundraising or service delivery. And so really the whole point of visualization is in fact to make it easier to use information to improve whatever elements of your work you are looking at at the moment. And this goes for you internally. So visualization can be a very powerful operational tool to help you improve the precision of how you target your service delivery or the precision of how you target donors in your development office. Also the same general principle is invaluable for speaking, for helping you to speak to your donors or constituents or local policy makers or boards about the work that you're doing to explain to them in more concrete visual terms why your work is so important, why it deserves their attention, why it might deserve even more attention. So visualization and storytelling really are two parts of the same thing. The whole point is to tell people a story that they can relate to very easily and it gets them excited about your work and it helps them see more dimensions of your work. And again it can be either a tool for you to help you see more dimensions of your work and see how you could perhaps adjust different ways that you do things to be more effective and a way for you to do outreach so that other people can see some of the same things and join you in your enthusiasm for the work that you do. So I hope that sort of makes the case which again is probably unnecessary in this context but I want to make it anyway. So having laid out the case for visualization, what is Power BI and how does it relate to visualization? It doesn't really sound like a visualization tool at all but in fact it is. For those of you who may not know this and I certainly didn't until I went to a Power BI conference, BI stands for Business Intelligence which is honestly a very formal or corporate way of saying visualization. Well, not quite. Business Intelligence is more than that. Business Intelligence is really boiled down to data analysis and presentation. So it's part visualization and part everything that comes before that. And Power BI is a immensely powerful tool in fact for handling a large number of the very critical steps on the path towards creating very compelling visualizations. And what I mean by that is that Power BI actually is several things in one package. It is a tool that helps you pull your data together. If you have data in multiple places, you can consolidate in Power BI without actually having to do anything like copying and pasting tons of rows into the same Excel file. Power BI is very, very, very good at pulling data from multiple sources and allowing you to blend it together in ways that help you extract meaning. Power BI is also a very powerful data modeling engine which means that it allows you to establish relationships among different pieces of your data that can be used to help with advanced filtering and to help you see how different subsets of your data buy, maybe different demographic characteristics, or really anything, any variable you might have. I need to mention that you might be looking at the location that someone is in, what kinds of services they've received. Again, things like age and ethnicity and so forth, how those things translate into the experience of, rather, how those things translate into outcomes. If you're an organization that does human services delivery of some kind and you're interested in, well, what is your impact like? How are you transforming the lives of the people you serve? Are you disproportionately doing a good job with one group but maybe not doing quite as good a job with another? That's exactly the kind of connection that Power BI makes it not necessarily easy to establish, but it does it very, very well. Power BI is also good at reshaping data, meaning taking data that comes in strange formats or not with particularly good column headings and refining it, making it into something that is much more easy to use, and doing that in such a way so that going forward, even new data when it comes in the same old strange format is automatically reshaped and ready for you to engage with immediately without you ever having to redo the same work. And then as you probably expected to see in a presentation about visualization, indeed Power BI is a very, very powerful, well, lack of a better word, visualization toolkit. In fact, it's also very expandable in that there is a very active community of folks who work with Power BI and are constantly developing new visualization formats for the tool, and that is a very, very powerful thing. So you can go online and get a ton of different visualization options from various people who have tried to do different things and just experimented to see what looked best. And then finally, the last thing I want to say about Power BI is that it is a very powerful interactive dashboard platform, meaning that not only can you display data, but you can also link the graphs and charts that you produce together in such a way that the graphs can be used to filter each other and show you different subsets of your data in a very interactive way. So let's say you have a graph of the various maps, rather, even a better example of all of the various locations that you provide services. In Power BI, you can have a map that does this right next to a bar chart that shows, I don't know, number of services delivered or something over time. And you can click on a particular region and the bar graph will automatically adjust to show you just the data from that region. That's dashboarding, and it's a very, very powerful technique. Okay, so what does that actually look like in real terms? Well, here's an example from actually Microsoft itself. So this is a pretty well-built dashboard, I would say. It's a bit contrived because it's part of their official marketing materials, but the bottom line is that it does a lot of different things all at once. And I can tell you that each one of these graphs is highly interactive, and it's interactive, again, with the entire sheet. So if you click on one of these bars, all the rest of the, they're called tiles on the page, will adjust to show you, for example, just the booked businesses that came in from BandCats, if we were to click on the BandCats bar. So again, dashboarding is a very powerful thing, and we'll see more about how that works in a second. Another example, same general idea. Again, these examples are fairly corporate because Power BI was built around the notion of business intelligence, but you can imagine the same principle applying to donation tracking and or service delivery tracking, and probably a number of other kinds of data analysis that I want to do. So again, same general idea, and each one of these graphs is interactive. We could click on a particular location or company name here to see the specific revenue that came in from that company. You get the idea. Okay. And then here finally is an example which is not nearly so polished, but it is in some ways more authentic because this example is currently displayed on the screen right behind me in the Tech Impact Office. It shows the metrics for our own service desk. As I mentioned earlier, we provide a variety of IT services, and we have a help desk that folks can call into to get tech support. And this gives us a sense of how well we're doing in providing that tech support. So we have here a graph of satisfaction survey results as they come in. This is a graph of results over time for the past month, I believe. And we can see that for some reason there's been a little bit of a drop-off recently, so we need to try to step up our game a little bit. This graph shows us all the cases that are currently pending are open. What categories do they fall into? Are we getting a huge influx of cases that we haven't responded to yet? Or are all of them sort of in progress, but we're just waiting for feedback? And it seems like it's about 50-50 actually. Half of them are in progress, half of them are pending. And then we have some summary statistics here that are pulled together that are basically just overarching snapshots of some of these graphs. So this graph up here shows us how good of a job we're doing at responding to cases within a one-hour window, which is our commitment. And the average for today is 90% of the time we're doing that. So all of this data was pulled from various places in our central case management database, and synthesized, and analyzed, and then visualized in Power BI. So hopefully that gives you a sense. I know it's still probably not that relevant to most of you, but hopefully it gives you a sense of what's possible, at least a little bit more in the nonprofit context. Okay, so how do you get Power BI? Actually, let me pause for a second and just ask if there are any questions so far since we're about to enter a new section. I think we haven't gotten any questions other than how much does it cost? Perfect. Okay, that's a great question, and I think we'll get to it right now. So fantastic, thank you. So how do you get Power BI? Power BI, it turns out, is believe it or not free. The quote, basic version is actually the full version of Power BI. It can do pretty much everything that you would ever need to do unless you have a colossal amount of data that you want to pull in and also store in the Power BI online interface, which is kind of an advanced topic anyway. We may not even get to an internet presentation because it is, again, fairly far afield from the core work that Power BI does. But you do get all of the data blending, emerging, and analysis and visualization capabilities in the free desktop version. You can get that version just simply by going to this link on the slide here, and thank you Becky for putting it in the chat as well. You'll have to sort of click around a little bit. It's not immediately obvious how to get the free download, but about two clicks in you will be presented with the option to download. I think they even say free trial, which is not true. The whole thing is actually free. And as it says here, you get most of the same functionality. In fact, all of the same functionality that you get with the pro version, all the pro version gets you in addition to more storage space is more features on the online version. So if you have loaded your PowerBand visualization into a portal that allows other people to interact with it, then you get more collaboration options, more permission management options. You get the idea. Also, if you have a data set that automatically queries other online data systems, like for example, our service desk database is actually stored in Salesforce, there are some limits on how many queries you can run per hour in the free version. And the paid version dramatically expands the limits, but that's not going to apply to most folks, I think, particularly not if you're pulling in data from Excel. So hopefully that puts everyone's mind at ease about how to get it, because it's very easy. And again, to do pretty much everything you need to do costs nothing. So once you have it, how do you use it? This is actually much broader than a Power BI workflow. This is really the way that I think about visualization in general, whatever tool you might be using to do it, whether it be Power BI or Tableau or Microsoft Excel, this is the general approach that I take. So the first step, well actually I wanted to lay this out first. We'll talk about each step in detail, but just quickly to summarize, you first have to connect to your data, get it from somewhere, define and refine the exact way in which you're fetching that data and sort of holding it on the pieces of it that you want, because if you're blindly pulling in everything from a big data set that can take a long time. You want to then get even more specific and choose which specific variables you want to work with. In some cases you may even want to create variables from your data sets like summary variables about like donations over time or some total donations rather. Maybe that column doesn't quite exist yet. Power BI can help you make those summary values very easily. And then the next thing you would want to do is figure out which visualization formats work best with that data. So there are a variety of visualization formats that Power BI offers out of the box, but then again there are dozens if not hundreds of others that also are available online that people have put together just based on their experiences in Power BI. And so that can be a really, really powerful way to expand the kinds of visualizations that you do if you're interested in going beyond the standard set of bar charts, line graphs, that kind of thing. As you try out different options, which you should definitely do, that's where the most fun and groundbreaking parts of visualization is finding a new way to present information that is even clearer than before. You want to look for emergent data quality issues as you go. Because each time you change how you're looking at data you might encounter new parts of the data that are maybe not complete. And therefore are sort of messing up your analysis or values that should really be the same but were entered in slightly different ways. And you're going to have to adjust for that if you want to get a really robust analysis. Probably again Power BI makes that really easily. It makes that very easy. Same general idea as you go. You may also have to adjust your scales because if you have data that spans a very wide range of values you may find that certain really large values are drowning out certain small values so that you just can't see any difference at all between small values even though the difference may be meaningful and important. In such cases you may need to adjust scales and sometimes where the large values also known as outliers fall off the edge of the scale temporarily so that you can focus on the differences in the smaller values. Finally, and this isn't really the most important part, you want to make sure that you do something with the visualizations and analysis that you've created. You don't want to just create them and then feel good about yourself. I'm going to move on. You want to make sure that the work that you put in to learn this tool and how to interact with your data has an impact. That means taking what you've done and sharing it out with folks. Power BI makes that really easy too. Again Power BI does have this link to Power BI Online which allows you to publish the data visualizations you produce almost immediately and then make them available to others to interact with but not necessarily even exposing the data that they came from which is great for privacy and security reasons. This is the overall flow that we're going to be talking about. Let's dive into it. First step, connecting to data. Don't worry about this subtitle. This is a very fake old style database query. Happily you don't need to write one of those anymore because Power BI will walk you through the process of connecting to things. I'm ambiently sort of watching the Q&A window and I see someone was asking what kinds of data can Power BI pull from. Here you go. Power BI is I think somewhat unique in that the Power BI team is really dedicated to constantly adding new types of online data that Power BI can pull in automatically without you having to do much of anything at all except enter login information for whatever system we're talking about. So this is actually a small subset of the various online services that Power BI can talk to out of the box. And you can see probably things that seem somewhat familiar like MailChimp, Salesforce, QuickBooks, Facebook even. So again, this is not all of them by a long shot. Power BI also supports a variety of generic database connectors. Like you can see, it supports connections to databases that are running on MySQL or Oracle or Postgres or there's a bunch of others as well. And of course it connects to data in flat files like Excel and CSV files. So bottom line is there are a lot of different tools and services and file types that Power BI can connect to. And the Power BI team is adding new ones I am not exaggerating every month. I think on average they add maybe three or four every month. And you can specifically request new connectors at any time by going to their feedback site. I forget exactly what it's called. But if you Google for Power BI feedback you'll find what I'm talking about. Okay, so I just talked your ear off about how great Power BI is connecting to things. Let's take a step back and talk for a minute about some challenges that you will face likely in connecting especially to online data sources. So you are a winner that you may face these challenges. The number one challenge for connecting to online data sources is firewalls and other database security controls. For cloud-based systems like actually most of the one on that page, Salesforce, MailChamp, Facebook, they all have authentication figured out. I mean Power BI had to develop a connector for those systems in such a way that it would be more or less the same as logging in from a web browser. However, if you are trying to get access to a custom built database or one that is running on a server in your back closet like the one we mentioned way back when, that could be a bigger issue actually because the MySQL connector and the other generic database connectors assume that you have a very clean, easy connection to the server and that you just need to enter a unit and password and that's it. VIP departments offer and enforce security controls that do that allow people to randomly connect to your database because that would actually be kind of bad, right? Even if they are still using a password barrier, if there were people who were able to just guess username and password combinations constantly with no other restrictions put in place, that would be not a great thing. So you may need to talk to your department about getting around any firewalls or other security controls that are on your server if you find that you have difficulty connecting. So that's one of the big challenges. The other challenges have to do with the structure of the data itself. And this is pretty much something you'll say no matter what, no matter how good your data collection repository is, no matter how much effort you put into maintaining good practices about collecting data, you will very likely run into data consistency issues. Meaning you would have preferred people to always enter a road name with RD at the end and some people enter RD, some people enter road, some people enter RD with a period. And that might not sound like a problem, but in some cases it can be. Particularly imagine a less, or imagine an ADA, just simple Excel spreadsheet which has no kinds of rules at all for how data is entered. And imagine that you have a column that should contain either a yes or a no value and nothing else. That's the only thing that column should contain. But that Excel spreadsheet has been passed down from one person to the next for quite a while. And each person who has been in charge of it has had a different standard for how they enter yes and no. So person one enters yes and no as the full words written out, but all lowercase. Person two wrote the same words out but capitalized the first letter. Person three just wrote y and n. And person four for some reason decided to express yes and no as ones on zeros. Instead of a universe of two possible values in that column, you don't have a universe of eight possible values. And when you try to analyze and visualize that data, what you will get is instead of a nice side-by-side bar chart of two columns, you will get a bar chart of eight columns. Unless you go to, it's an effort to consolidate those values back down to what they really should be which is just one of two yes or no values. That's data consistency. And again, it will almost always be present, or rather data consistency problems will almost always be present in any real-world dataset. It's just sort of a fact of dealing with data. Same thing for data completeness, which is a relatively simple idea but in some ways an even harder one to deal with because while there's a lot you can do to clean up inconsistent data, there's very little you can do to recover data that was never collected in the first place. And so in some cases there may be. In some cases you may be able to synthesize, let's say congressional district or zip code from just a mailing address. If for some reason you didn't collect zip code as part of that. But it would be a lot better obviously and make it a lot easier for you to work with your data later if you collected that data in the first place. So this is just a plug in the data analysis space for thinking about these things as early as possible and putting in place rules that force people who are doing data entry to adhere to whatever standard you might have for particular fields in terms of what values they can hold and enforcing rules that make sure that people can't even save records unless they're complete, unless they have all the information that you actually will need later on filled out. Data structure is also sometimes an issue. It's actually not so much of a problem in Power BI though because Power BI is built to restructure data to make it more useful. So what I mean by data structure is imagine that you have tables that are, if you add new information to the table, the table becomes wider and wider and wider. Like imagine you had a table of, I don't know, donation figures by month. That's all it is. And each time you add a new month you add that month as a new column tacked on to the right side of the page. Most data analysis engines really don't like that kind of data. They want what's called long form data where each time you add a new data point it actually gets added as a new row growing down the page. And for instance Tableau would have a really hard time dealing with that particular problem. Power BI, you may not do it automatically, but it is very good at allowing you to reshape your data into whatever structure would be more useful for you. Okay, I see there's a few questions. So I'm going to pause here and see if there's anything we should address. Thanks, Jordan. We have lots of folks chatting in question now, some of which you may be addressing as you move forward. But I think there have been a couple of people asking about preferred data sources. And I know you talked about the connectors or data sources and you showed a screenshot of all of those different data sources. But could you perhaps go over that in a little bit more detail? Sure. So Power BI, like I said, it's always changing. It's always adding new features. And I wouldn't say this is really a preferred data source at all. I know that almost is me refusing to answer the question. I'm really not trying to be difficult though. Power BI and the Power BI team rather is trying really hard to make Power BI the go-to place to make meaning out of data from all sorts of sources. And so I would say first and foremost, look to see if there is a – let's say you have data in something like Constant Contact because we didn't actually see that on the list. First and foremost, check to see if there is an official connector to Constant Contact in the Power BI list of connections. If there is, that connector will make it incredibly easy to access pretty much everything about your data. Or at least it probably will. Some of these connectors are actually somewhat limited, surprisingly so, just because it was easier to get basic data but not everything. So you definitely want to take a look at what each connector allows you to do. And there should be a little bit of documentation somewhere on the Power BI site about each connector and what kind of information it gives you. If that doesn't work, then you might need to download a CSV version of your data to work with using the file connector. And I would say in general CSV data is excellent. It's a default standard if you have no other considerations because it's consistent. It's universal. It's pretty clean. There's not extra weird formatting and formulas that you often can get in Excel which Power BI can actually handle. In fact, one of the Power BI teams main focuses for this next cycle of development is to make Power BI and Excel talk to each other in a much more advanced way. But in general, I would still say CSV files are a great way to go if you're looking for sort of how to get started. And there's nothing about CSV files if they're understood to be a common data format. So you should be able to export from any online system, even one that doesn't have a clear connector for Power BI, to a CSV file pretty easily. I hope that answers the question. Thanks. Yes. And one more, is it required that it be an API interface to access the SQL database? Definitely not. The MySQL connector I believe just assumes that you have direct login access to the MySQL server. And that's why I talked about you potentially having to get through firewalls because that sort of connection is often seen as pretty sensitive and therefore made very restricted in terms of who can establish such a connection and from where. But no, because I can MySQL, you definitely do not need like an API sitting in front of the MySQL server, not at all. Great. I think some of the other questions we have you will address in your next two steps. Fantastic. Okay. Once you have connected to your data, the next step is to bring in the data and figure out exactly how you want to bring in the data to be much useful. So the first place you should go when you bring in data is this place called the Query Editor. And it looks a little bit intimidating, but the good news is generally speaking you won't need to do much here unless you want to. And the other piece of good news is there's a lot you can do here if you want to. The reason why you want to go here first is that one thing that is just hard to do, and that Power BI sometimes has a great job of and sometimes just can't quite do for you is detect what kind of data there are in your data set. This is very important for any kind of numeric data or any kind of date data or any kind of location data. Because if any of that kind of data is detected as text, Power BI won't know how to do some of the more advanced stuff that it can do with numbers and times and dates. And for instance, if a date is detected as text, it will actually be not sorted as a linear progression of time, like if you have a column with a bunch of dates. Instead, it will be sorted alphabetically, which is actually not even numerically, it will be sorted alphabetically. And in alphabetical sorting, one always comes before nine. So you're going to end up in a very bizarre situation where December values actually show up before September values because one, the one in 12 is before the nine in September. So you get the idea of why this might be important. So you want to make sure that any column that contains date information is correctly flagged as containing date information. And all you need to do, I haven't shown this here, but we'll do this in a second, or in a few minutes rather, is click on that column and say, please treat this as time information. Same thing goes for numeric information. If numeric information is detected as text, you won't be able to do math on it. And that's one of the really most fundamental and powerful features of Power BI is its ability to do all sorts of summarizing and mathematical transformations for you. So you want to make sure that any columns that contain numbers are detected as numeric. You can do other things as well. One funny thing that Power BI for some reason doesn't do automatically, it doesn't automatically detect the first row of a Excel workbook or a CSV file. Is in fact the header row that should be used as the name of each column, or that contains the name of each column. So there's an entire button in Power BI in this data query area that you generally have to click. To tell Power BI, yes, the first row in the file does contain headers that should be used as the titles of the column going forward. It's a very funny button because you think that would happen automatically, but because in some cases you pull in data it doesn't have headers at all. So it is nice that it's there. You can do much, much more advanced things here. I don't want to go into too much depth because we're already pushing time and we have a lot more to get through. But here's an example of the kind of thing you can do in the query editor. You can do duplicate values. You can replace values. You can define groups of values, particularly useful when you're trying to consolidate inconsistently entered data. Groups are one way to combine all the yeses and noes into two distinct categories. You can filter out values you don't want to work with. You can split and merge columns here, tacking information from two columns together, or if a column, like a date column contains multiple pieces of information like year, month, and day, you can split out all of those into three different columns if that's useful to you. Pivoting and unpivoting is complicated, so let's not go into that too much. And you can perform arbitrarily complex transformations including highly advanced math on every single row. And that will be done once you write once across the entire dataset. Next step, having defined your, or figured out exactly what you want to pull in, you want to choose some variables, which is obviously the most exciting part of this entire process, right? Ooh, we get to choose variables, yay. But this is actually pretty important. And to see why, let's go ahead and actually take a brief break. I go into Power BI so you can see what some of this actually looks like. Here we go. I'm sharing my screen one second. I'm going to wait for a moment for that to load. There we go. So hopefully everyone can see Power BI now open in front of us. So actually, I wish I had started from the very beginning, but that's alright. So I've gone ahead and for the sake of time, I have already pulled in some data. And just to show you what that looks like, I'm going to go back into the query editor. Obviously, excuse me, sorry, I didn't get the query editor right now. Hopefully what you're seeing now looks kind of like an Excel spreadsheet and it has a bunch of columns and so forth. And I just got this by pulling from a CSE file. And just so you have a sense of the options, if I wanted to pull in more data, I could do that very easily by clicking this new source button. And if I click on more, here's the full list of all of the data sources that Power BI currently supports. And as you can see, it is extensive. So like I said, all I did was to get to the point where we are right now, click on a particular CSE file that I happened to have on hand and click to connect. And that brings us to this which is the data query editor you saw before. You can see the use first row as the headers button which I believe I've already clicked. And I think the other buttons you saw before. So very quickly, just because it's important, we want to make sure that any time data in the data set is detected as time. And to do that I can right-click on the column and go down to change, type, and make sure that it is explicitly set to date and time. And there we go. And in fact, that actually had an effect. You might have noticed before I did that, in fact I can go back one step. You notice this was detected as, well, actually it's not really sure. It says it's either text or numbers or something. And when I change the type, all of a sudden this icon changes to a calendar icon indicating that now this is going to be treated as time information which is again critically important. There's a lot of other stuff here that I might want to reclassify. The most important is donation total because each one of these lines represents a single donation made at some point in time by an individual. And so to be able to do math on this data to ensure that I can get summary statistics, I'm going to want to reclassify this as well explicitly as a decimal number. And you can see, again, it wasn't quite sure what I did as well. I just knew it was Alpha Numeric. And now by doing this type change, I have ensured that it will be treated as numbers that can be aggregated. So that is all I want to do here for now. But know that you can do a great deal more in this interface. And again, there's a lot of different things that one could do in terms of splitting things, reshaping things, generally making them more useful, and removing information that's just not useful to you in a particular context. So once this query is complete, and by query I really just mean you building out visually the kind of table you want to work with, you want to click the close and apply button. And it's going to take a second when you do this for pretty much datasets of any particular size. This dataset, for instance, is only 10 megabytes in size. And still, you're going to see it going to take about 20 to 30 seconds to fully pull everything in. Obviously, bigger datasets will take even longer. So this is perhaps the one place where Power BI can be a little bit slow. Otherwise, it's quite speedy. And we're almost there. There we go. There are actually several places in Power BI where you can do far more advanced manipulations than we've even talked about so far. There are two little icons here on the left-hand side, one which allows you to, for instance, duplicate and manipulate your tables in even greater detail. And there's another section over here, Relationships, which allows you to create and or modify relationships between multiple data sources if you have them. In this case, that's obviously not relevant. That is advanced stuff, so we're going to ignore that for now. The most fun and in many ways important area of Power BI is the visualization canvas. And here we do, in fact, have a blank canvas. We have a bunch of visualization options here and an import option to import a custom visual from the Power BI repository online. What we have underneath here is a place to put various variables. We might want to work with a place to put variables whose values we want to summarize, a place to filter out data that we might not want to include, or if we want to selectively include some data, we can do that too. You can see there's actually not much going on here yet. That's because this area changes depending on what kind of variable you drag in. And that brings us to that slide that we were just on, Choosing Variables. And the reason why it's so important, even though it sounds kind of boring and simple, to choose variables correctly is that you want to choose variables that can help tell the story you want to tell and give you the kind of information that you want. So for instance, if I want to get a sense of our biggest donors, I could do that in a couple of different ways. I have a variable that has a nation total, which is great. I have another variable that turns out that, where'd it go? Let's expand this. It could ban this interface to make it easier to see, which I'm going to do. Where is it? There's another variable that indicates the rough amount of a donation. I think it's this one. So if you just wanted a rough overview of how many of your donations fell into the $10 to $100 category, how many fell into the $100 and up category, and how many fell into the $100 category, you could use this variable to help you tell that story. But if you want a more precise account thing of what's going on, you might want to use the donation total variable. Excuse me. Yes, that's right. And we can actually do both. So for instance, I can drag donation total onto the same card. And now when I did that, I automatically got a little table showing me by category how much came in. So I see that in the $10 to $100 category about $100,000 came in, $100 and up, $300,000, under $10,000, $3,000. Well, that makes sense. I mean, you know, just in terms of the amounts that we're bringing in. But let's go one step further and ask, well, how many of these donations are we bringing in? I can do that by dragging in donation total again, except this time I'm going to change how donation total is being calculated. So the donation total ordinarily by default acts as a sum, which is how we got those numbers. But what I really wanted to do is give me a count. And that will give me the count of the number of donations that came in from each place. And so now we see interestingly that even though the $100 and up category brought in more money, we had far more donors in the $10 and up category, which is kind of interesting. Let's quickly build another visualization. Let's build, oh, I don't know, how about a map? So for a map, we need geographic information, right? That's going to be really important. So I'm going to first say that I want a map and it goes ahead and creates a new tile for me that's a blank map. I'm going to go ahead and drag in donor state on top of this. It's going to be a little bit of magic and figure out that, oh, hey, I have data from all 50 states. And as you can see, you can expand these tiles to be whatever signs you want, and zoom in on them, which I'm just doing by scrolling my mouse wheel. And there's a nice map showing us that we do in fact donations from all 50 states. That's useful. But perhaps I want to actually shade things according to how much money came in. So I can drag that on top of the graph again. And now you'll see that states are lighter or darker. Sometimes they'll get random errors in Power BI, but usually you can proceed without worrying about it too much. But you'll see that states that gave more money are darker and states that gave less money are lighter. But even better though is that I can click on a state. And as this table up here automatically adjusts to show me just the donations from that state. You'll notice we're no longer talking about hundreds of thousands of dollars. Instead we're down to tens of thousands and our donation total has changed because we're just looking at the data from California. And I can do that for any state. I believe there are ways to choose multiple states at once, but you have to use a different map type for that. I can also do the same thing up here I believe. Actually, I have to choose a different relation type for that as well. But had we chosen a bar graph today, let's actually see if we can do that quickly. This will show you what it's like to convert one data type to another. And there we go. So here's a bar graph showing us by category how much money came in. Great. So now we have a bar graph. I can actually click on the 100 and up data. And it's hard to see, but it turns out that this map color range has actually changed so that now we're only looking at the data that was classified as 100 and up. We can do the same thing for 10 and up. And unfortunately this data isn't that interesting in that the trends appear to bear out across all of the states, excuse me, across all the categories. But if there were big differences in terms of where our big donors lived that would be reflected as we clicked on each one of these categories and different states would get darker as a result. Hopefully that gives you a sense of the tremendous interactive power of Power BI and why it's important to get back to the slide to choose variables that are actually well suited to the visualization formats that you want to work with and the story you want to tell. In particular, if you want to do any kind of slicing and dicing like you saw here, bar graphs are generally a good bet to allow you to click on things and then have the other graphs respond. Maps are a really great way to do that too. Let's quickly go back to the slide and I know we're not going to be able to get through all of them, but that's completely fine because what I really wanted to get to was to show you what it's like to work in Power BI. And actually while I'm getting back, are there any questions so far that I can get back? Yes, we have quite a few questions. And as you're converting back to sharing, to the ReadyTalk, someone is asking about security. So after you publish something to Power BI, they want some content to be available to anyone and other content to be available via invitation only. Is that possible? It certainly is. And the Power BI online suite can interact with your Office 365 account if you have one such that permissions can be managed centrally. And people can be granted very granular access to some things and not any access at all to others. So that's a pretty good way to do it. It's a little bit harder if you don't have an Office 365 implementation, but it is still doable. And at the end of the day, you can treat Power BI files because what you create when you create one of these things is just a file. You can treat them like any other file and put them in some sort of password protected container or make sure that they're only shared with if you only should be shared with. So there's a lot of different ways to control access. Great. One other thing, Kerry is asking about a set of examples that use Power BI to aggregate and display county-wide or statewide population data around goals. So I think just resources that show examples of how other nonprofits use this. Okay. I will definitely look into that. I know of a few different options, but there's also a lot of resources online and a very active user community for Power BI. So you might want to inquire there as well. Again, go to the website and find the forums or I think it may even be called user community. And again, the second you post there, you'll probably have people interested in what you're doing and wanting to help. It's a very, very active group. So I'll look into that as well. Okay, let me try to get to a little bit more before we have to close. I know we're almost at time. So important takeaways. It is very, very, very valuable in any type of visualization work to look at data through different lenses using different visualization options just as I sort of was playing around with that table and turning it into a bar chart and then realizing, maybe it's not perfect as a bar chart, but it does get me part of what I wanted. Maybe we can refine it further. There are no right or wrong approaches in data visualization except ones that interfere with clarity or accuracy of presentation. And again, if none of the visualization options are given in Power BI quite fit, there are hundreds more options online. We talked about this already. It is important as you go to address emergent data quality issues. And you can do that in a variety of places. You can do that in the graphic area by creating filters there. You can filter out data in the query editor which is often a cleaner way to do it. And you do want to look for outliers to make sure that scales actually had to not liars in a way on that last example that I showed because I tried to simultaneously graph both sum total of donations and the count of donations on the same set of axes, which was a mistake because the sum total of donations is much higher than the count of the number of donations. And so you saw that there was one set of bars you couldn't see at all because they were being drowned out by the sum total bars. And so that means I just need to split up those two graphs for the two separate graphs, in fact, so that you can see each one. It's all right. We talked about how to deal with data quality issues. You get the idea of adjusting scales, same idea. Again, when you're facing a legitimate outlier problem, you want to adjust your scales but also make note of the fact that you have done so so that you're not hiding parts of the data that are real and important. We talked about this. Good scales are a balance of meaningful visual differentiation so you can actually see differences among things and also accurately representing things so that you don't hide the fact that you do have a really big value somewhere or maybe a handful of really big values. And then finally, again, it's really important to publish and share your work. I didn't actually want to get into this in any depth because publishing the Power BI online is an advanced topic. So I'm going to skip a few slides here and skip straight to the end. I want to note that Power BI is very complicated and acknowledge that it can be intimidating but the way you get more comfortable with Power BI is by diving in and just trying things out. Don't be afraid to break things because you inevitably will and it's fine because you can always go back. Just make sure to save a copy of what you're doing regularly so it's very easy to go back. Don't worry about knowing what every button does. Don't be afraid to go do tons of research online. That's the best way to figure out some things. And note, this is very important. No matter what you do, you are not actually making changes to your real data in any way. Power BI is pulling an extraction or it is operating in read-only mode. There is no way for Power BI to actually change your real data. So if you delete something or you're filtering something out, you are in no way running any risk of doing any harm to the data source from when your data is coming. So hopefully that frees you up a little bit to explore more and not be too worried. I just wanted to note that a lot of what is possible in Power BI is also possible in Excel. That's not that relevant because Power BI in many cases is actually cheaper than Excel. But if you are more familiar with Excel, you want to try something out in Excel to get a sense of how to look first and then try to replicate it in Power BI for more general consumption, that is a great strategy for sort of easing into the platform. Some final important takeaways. Again, I think I mentioned this already, but data are not static, which means visualizations of them shouldn't be either. You want to constantly revisit your data and make sure that you are, you know, you're making familiar with how to work with it and doing so on a ongoing basis so that you can gain more insights about the choices you're making and how your organization is doing and adjust rapidly and see what impact those adjustments have rapidly. Always be on the lookout for emergent data quality issues always, period. Don't be intimidated. When in doubt about anything, Google it. And you cannot do anything wrong except do something that interferes with clarity or accuracy. Finally, a quick list of resources that I mentioned, the Power BI Discussion Forum, the Power BI Blog, the Index of Custom Visuals, and that is what it was called, the Power BI User Voice Suggestion Box, where you can submit requests for additional functionality or additional connectors or really anything at all. They love to hear from some end users about what they want. And then if you would like a sample data set to play with just to get a sense of how to do some things in Power BI, there's a great one that I just would be working with actually in our demo available from this link. And that, I believe, is it. So thank you so much, and I apologize for going over a little bit. That's completely fine, Jordan. Everyone I could tell was really engaged because we continuously – we still have questions that we may not be able to get to every single one, but I do want to remind folks that this was recorded as well. And the PowerPoint that you weren't able to look at every single slide, but everyone will receive a copy of that PowerPoint, and we'll also make sure to list all of the links to the resources on the archive page that we send out and also a way to connect with you at Tech Impact for additional, more specific questions about each individual nonprofit that may have questions. And I am really grateful for your in-depth coverage of that. It was amazing, and you went at such a great rate. I could tell everyone was following along. One more quick question, and as you're answering that question, I'm going to ask everyone to chat in one thing they learned, and I'm sure they probably learned dozens of things. Just pick one. While you're chatting that in, learners, Jordan, talk a little bit about the Power BI produced visualizations. Are they dynamic or static? So if they're connected to like a SQL Server or some other type of database that is continuously updated, would the visualization continuously be updated as well? Yes, it would indeed. And it really depends on how you've chosen to publish it, how dynamic it is. So that example I showed at the beginning from our own dashboards is updated on an hourly basis from our live Salesforce data. And part of what makes that doable is the fact that that pretty quick dashboard is in fact hosted on Power BI online. So we've configured that system to automatically pull in data. It has the login credentials for our Salesforce and stored, and we don't have to do anything actually to get it to update, which is not lovely. If you're pulling on into the desktop version of Tableau, you may not see new data come in without you having to do anything at all. You may have to click a button saying please refresh the data source, but that's all it'll take. So if you want an absolutely hands-off automatic approach, you probably want the data to be in Power BI online, but otherwise the latest data is only on mouse click away. Thank you. Very quickly, I'm just going to wrap this up. I do appreciate everyone hanging on. We do know you're most valuable as it is your time. So thank you for all of your time today, everyone on this call. A huge thank you to Jordan and Tech and Beck for being such amazing partners. And again, a huge thank you to Microsoft who supported this webinar through grants. We do have an online learning system called TechSoup courses where you can access free courses. We also do have some courses that you have to place an order or pay for, but there are some amazing courses that you can access in our TechSoup courses online. We have other upcoming webinars this week, tomorrow and Thursday. We've got two wonderful webinars. One is primarily for libraries, and the other is how to successfully promote your year-end fundraising campaign. And then just before Thanksgiving, we'll have five things you didn't know about TechSoup's donation programs. The secret is there's probably a lot more than five things, but we're going to cover at least five. So we hope you join us for one of those. Again, Jordan, thank you very much for your time and your expertise in working on the back end to get this presentation just perfect. Thank you very much. And a huge thank you to Becky for doing all the chatting and the tech support. All of you that are still hanging in there, please do complete the survey. I think there are about 10 questions. It does provide us with valuable information. We could even put it in Power BI, and then we'd be able to visualize how well we're meeting your expectations and your goals. So the only way we can continue to do this and to get better is by getting your feedback. So please be honest in your feedback, and I would appreciate that. So we're going to thank everyone. Thank you for your time, and hope to see you tomorrow or Thursday on our next webinar. And have a wonderful day. Bye-bye.