All language subtitles for 003 Types of Data Connectors_en

Instructor: Power BI has a really

robust connector library

that continues to grow

and allows you to access data from virtually anywhere.

So, when you open up Power BI,

you can click on this get data button

in the home tab to access the get data dialogue box.

And this is where you'll see some

of the sources that are available to you.

Bottom line, it's a lot.

You can connect to just about any type of source data,

including flat files like Excel workbooks and folders,

databases like SQL, Access, Oracle, and IBM,

Power Platform, datasets, datamarts, and dataflows,

and online services,

which are a bit more specialized

like GitHub and Salesforce and other sources

like web feeds, R scripts, Hadoop, and more.

So, just tons of options for connecting to data.

Now, I'm not gonna cover each and every data connector,

but let's open up Power BI

and we'll take a look at what this library looks like.

All right, so I've opened up

our saved AdventureWorks report.

And because Power BI is a heck of a lot more fun with data,

Power BI provides a bunch of different prompts

and ways to get data.

So we can see here in the main report Canvas

we have this prompt to add data to your report, right?

And they give us a couple of quick links here

for Excel data or SQL Server,

you could paste data from a blank table.

You could click get data here from another source.

You could also head over to the right hand pane

and start the get data process from here.

You could come over and click transform data.

And then this would launch the query editor

like we saw with Chris.

And you could actually connect to data here

or you could select the get data menu here, right?

The larger point here is

that regardless of where you click data,

you'll end up in the exact same spot,

which is the get data menu.

So now that we're in the get data menu,

let's say you're looking for something specific like SQL,

you could type this into the search bar

and it's going to limit those search results

or the connection options here

to only those different SQL connections, right?

So if you knew exactly what you were looking for,

you could use that type of an option.

Otherwise, you'll just default to this all list,

which shows you all of the different data connectors

available to you.

And like we said, it's a ton.

Now looking at the file options here,

there are some pretty common ones

like Excel, text, or csv, JSON.

And then we've got this folder option here,

which is actually a pretty interesting one

and we're gonna do a demo on this a little bit later.

But basically, connecting to a folder allows you to connect

to all of the file contents contained within that folder

and then automatically append

and blend those new files together.

And what's really cool is that any files that you add

to that folder over time

will be added into that query as well.

So, it's a really, really great automation technique

which again, I'll show you in a little bit.

So next up, we've got some database connectors here

and they pretty much speak for themselves.

So you've got options like SQL Server databases,

Oracle databases,

MySQL, Postgres,

BigQuery, Snowflake,

and many, many more options here.

If we head over to the power platform options

these are also pretty straightforward

and allow you to connect to datasets, datamarts,

the dataverse, and dataflows.

So again, anything that has been created

within Power BI Service

or another power platform application

should be accessible through here.

And if we head over to Azure,

this contains all of the Azure specific connections

like Azure SQL databases,

Azure Analysis Services, Databricks, and more.

Online services has some interesting ones as well.

You can see here we've got things

like SharePoint lists, Dynamics 365, Salesforce objects,

GitHub, Mix Panel, Marketo, QuickBooks.

Again, we've just got a ton of different options here

and this is a place

where it's definitely worth looking through some

of the connectors just to see what's available to you.

And then last but not least,

we've got this other tab

and here you've got some interesting ones as well.

So at the top here, we have this webpage connector

and here you can type in a web address

and then scrape data from a webpage.

And I'm gonna show you a demo on this a little bit later.

We've got some other great options here

like R scripts, Python, ODDC connectors, Google Sheets.

We'll scroll down to the bottom here.

And we've got this one option here

at the bottom called Blank Query.

And this is a super powerful option,

especially if you know how to write M code.

So this allows you to generate a custom connection yourself.

And we're also going to do a very simple demo

on this later in the section using a blank query

to create a dynamic rolling calendar

that updates based on the current date.

So, the important thing to note here is

that I'm not gonna walk through

all of these different connectors.

The biggest reason for this

is that Power BI has done such a great job standardizing

the connection process

and it's honestly remarkably consistent

and user friendly across the board,

regardless of the connection type.

So whether you're connecting to a database,

a file, an online service,

once you've correctly entered the connection details

and authenticated the credentials,

you'll always arrive at the same end result,

which is the query editor.

So with that said,

for the purposes of this course,

we're really only going to be using CSV files

because they're simple, ubiquitous,

and can be downloaded straight from the course resources.

Just keep in mind that all of the concepts

that we cover in the query editor

really apply to everything here

regardless of the original source.

So with that, let's quickly connect to our first CSV file,

our territory lookup.

All right, so for this course,

I have downloaded all of the course resources

into a folder on my desktop.

And so what I'm gonna do is I'm gonna connect

to all of the data right from here, all right?

And we want to connect

to our AdventureWorks Territory Lookup.

And when we open this,

Power BI is going to show us this preview window.

And the preview window is pretty straightforward, right?

Gives us the name of the file, file origin, the delimiter.

Because this is a CSV file,

the delimiter defaults to comma.

You can see we've got some other options here

for different file types.

Gives us the data type detection.

Power BI basically takes its best guess

at the data types based on a sampling of rows.

So here it's set to 200 by default.

You could choose to sample based on the entire dataset

or not detect the data types at all.

And then from here at the bottom,

we've got two options,

load or transform data.

And load is going to load this data directly

into the data model,

but transform data is actually gonna bring us

into the query editor,

and that's what we want to do.

All right, and as the query editor is loading here,

we kind of see a familiar interface,

but we've got some kind of new options.

It's a little bit of a different layout.

And we're gonna expand this window.

So, kind of starting at the top here,

we're gonna go over this

in much more detail in a later lecture.

But we've got some different tabs and ribbon options here.

You can see that we've got queries over here.

This is the table that we just connected to,

that data source.

We have our kind of preview window here as well.

And then on the right hand side

we've got this query settings pane

that has properties and applied steps, right?

And again, we're gonna dig into all of this in more detail.

So for now,

let's update this table name to just Territory Lookup.

So we're gonna delete the AdventureWorks portion.

We're gonna come up here

and we are going to save our workbook,

so we can click apply or apply later.

What I wanna do is I want to actually apply these changes,

so Power BI goes through

and saves and applies those changes

and we stay right here in the query editor.

Okay, great.

So at this point

we've loaded our first data source into Power BI.

We've saved our workbook.

We're in this brand new Power Query interface

that's a little bit unfamiliar.

Up next, let's cover some of the Power Query options

and tools that are available to us.