2: Importing and transforming data using Power Query Editor
2.2 Explore data sources
2: Importing and transforming data using Power Query Editor
2.2 Explore data sources - Video Tutorials & Practice Problems
Video duration:
6m
Play a video:
<v Instructor>In this sublesson,</v> we're going to explore data sources. So we will first start off by showing the data source categories that are available in the desktop. We'll explore some of the individual sources. We'll learn about beta and preview sources. And we'll discuss custom connectors. So what we're gonna do is get ourselves a blank Power BI PBIX file open. And what we're gonna do here, first, is we're gonna work on getting data into Power BI. So we can get data from many different sources. And just some examples here are Dynamics 365, Google Analytics, if we choose connect to that, Azure Synapse, Excel Spreadsheets, a very common source, SQL Server Database is also a very common source, and SharePoint. So what we can basically see here, is we can get data from all over the place, and use Power BI as the aggregator to pull that data together, blend it together, clean it, cleanse it, and put it into a data model. And that's largely the subject of lesson number two. Okay, so I have a blank Power BI Desktop file open right now. And at a very high level, there's two different ways to get data into Power BI. In fact, there's really many different ways. But we wanna focus on getting data from the Power BI Desktop, if we're on the screen here. Or getting data, if we're over in the Power Query Editor itself. So let's first show you how to get data if you're on the Power BI Desktop. So what we wanna do here, is we're gonna go to the Home tab over into the Data group, and we're gonna go to the Get Data command. If I click this little dropdown box, we're gonna see that right away we're listed with a bunch of common data sources. So if the data source we wanna connect in is here, we can choose it, and make a connection to that and bring it in. Alternatively, if it isn't on this initial list, we can click on More, and it will take us over to a Get Data box. Where we can go ahead and make a connection into whatever data source that we want to connect into. But I'm gonna cancel out here, 'cause we're gonna go and get ourselves into the Power Query Editor instead, to actually go through this process. So the way that we're gonna do that, if you recall from previous sublessons, is we are gonna go into the Queries group on the Home tab and click on Transform Data, which is gonna launch us into the Power Query Editor. So let the Power Query Editor go ahead and open up, and then we'll carry on with our lesson. Okay, so now that I've got the Power Query Editor open, what I'm a gonna do here now is go to the Home tab, under the New Query group, and click on this New Source. So, once again, if I click on that, I'm gonna see a series of connectors we can connect into that are listed as the most common. So, once again, if it's on the list here, I can go ahead and click on it. Otherwise, I can click on More, and it will take us over to a very similar Get Data dialog box. Now once we come in here, you have a number of different ways to search for the connectors that you are trying to get at. If you feel like, you can scroll through the individual list of the connectors on the right-hand side here. Or, what you can do is use these categories here. So Power BI has categorized it into All, so here's all the different sources we can connect into. File-based connectors, Database-based connectors, Power Platform connectors, all the way down to Other. So we can get at the connectors by, once again, going through the right-hand side individually, we can use the categories. Or, optionally, we can go ahead and search. So if I know I wanna get at Excel, I can go ahead and type Excel into the search box, and there is our connector to connect into. So that works quite nicely, if the connector we wanna connect into is listed in Get Data. But what if it isn't? You actually have a couple other options. And they're a little bit more technical in nature, but I do wanna point them out to you. You have the ability to go in and get a certified connector. So this is a connector that somebody has developed for you, and that you can bring into Power BI itself. If you don't have a certified connector you can go obtain, you can actually go ahead and write your own connectors as an organization. Now this is a little bit more of a technical feat that you need to have some technical skills for, so I just wanna point out that exists. If you then go ahead and build a connector for your organization and you decide to get it certified, you can go through that process as well. So it's really up to you based on your use case is, is the connector in your list that we have right here? If it is, great, go ahead and connect into it. If it isn't here, you can optionally go out and try and find a certified connector that allows you to do that. Or, you can then go off and build your own connectors and use it on your own individually, and then potentially make that a certified connector, if you choose to go down that path. Another note about the different connectors, is that they are added to on a monthly basis, or a periodic basis. So Power BI Desktop does get a release roughly every month. So you will see more connectors coming into the fold on a periodic basis. So that brings me to the next thing I wanna discuss, and that is beta connectors. So we can see down here, at the bottom of the screen, that we've got an IBM Informix database beta connector. And what that is, is a connector that is undergoing testing and has not yet officially been released. So this is where the product team is asking you to use these things, or making it available for you to use these things, and provide comments as to, how well it's working, is it performing? And things like that. So it can eventually move from the beta testing over into general release. So you'll see a lot of different connectors with that beta bracket around things as it's working its way through the process. All right, so you'll see lots of different data connectors in here. Like say, for the most part, as a business user or new user to Power BI, the things you're gonna try and connect to into are already listed here. So a lot of the things I was mentioning around the certified connectors and building your own connectors, isn't really necessary. But it's just good for general awareness that they are available. So let's go ahead and move ourselves into the next sublesson, where we're gonna actually start connecting into files.