Power BI - Connecting files in a folder

Hello friends and welcome to tutorials point

In this segment of the video we are going to see connecting files in a folder in Power BI desktop

So far what we have seen is that we can import a single CSV file, an Excel file or an Access Database file

At that time we used to just import one single file. But let's take for an example that in a folder I have a bunch of files and I want to import them at one time

So what we can do? Yes, Power BI BI gives you an opportunity of importing this file at one go

But we have to make sure that all the files in this folder should have the same schema or the same format

Then only it will be able to import those files or else it will cause an error

Now PowerBi desktop can deal with this multiple files in a very efficient way

in such a way that it understands its extension and it understands the column reordering

and gets me the file in such a way that all the file in that folder will be aligned and will be imported in a very easy manner

So how to import this file in a folder, how to import it in the Power BI desktop, we will have a better understanding of it in our practical approach

Hello friends and welcome. In this video we are going to see how to connect to a folder in Power BI

Alright, so let's get started off and see how it is done practice

as well okay now as you can see this is my power bi window onto my right

hand side of my screen you see all the fields that we have imported using our

access database so this is my different fields as you can see I have the

product field I have the manufacturing field I have the date field as well

you can see all the deleted tables with that particular fields okay now onto the

left hand side of my screen you see there are three buttons now the very first

button is the report button and the moment you click on here you can see there is a

blank screen the reason being we have not created any of the reports as of now

we'll be creating reports in the later part of our videos using these fields

okay moving on to the next one is the data button if you click on this

particular button you can see the actual data which is related to this

feels so if you click on the date field you can see the date table which is

being shown up and as of now this is showing you six two zero nine rows that is six thousand rows almost okay so you can see all the data that we are going to work upon in our power BI in this data tab so if you have to check the data if you want to make any kind of changes to

the data data I can see from the data tab and you can make the changes using the power

query editor all right so this data tab will show you the actual data that will be working on

in power BI the very third button which you can see on the screen is the relationship

button if you click on it you can see there are all the different fields that are

being pulled off using our access database and you can see there are some

lines so these line are the relationship basically it is showing me that these

two tables are interconnected with some relationship now we have not created

this relationship this is being imported in the access data with the access

database that we have imported our fields from that are these fields so

Basically, these are predefined relationships from the access database. We'll be learning about how to create a relationship and how to manage relationship in Power

BI as well in our later part of the videos. But as of now, we will focus on how to connect to a folder

So we have to go under the Power Query editor. So in order to go there, I need to go to Edit Queries

The moment I click on Edit Queries, the new window will come up all right so our power query editor is into picture as of now and we have to go

to the new source because we have to get or import a folder so I will click on

new source and click on more the moment I click on more another window will pop up

and I will simply select folder and hit connect it's very simple so this is

basically asking me the folder path which you want to connect to I will simply

select browse so we have to go to that particular location from where you have to

import the folder so I will simply go under this power BI which we have created

on the desktop okay and I will simply hit on this international and hit okay

all right so basically this is the path that we will be working on or we will be

importing our folder from so I simply hit on okay okay so as you can see these are

are the different files that is CSA's FR sales and MX sales now it can as you can see this

is a dot CSV connection in Power BI as we have already discussed that it is compatible with every other extension it can be an ODBC it can be a text file it can be a word file it can be

anything and this extension is as of now is a dot CSV extension that is a

CSV based file and this can be seen in this extension column it is also giving me

some other information that when it has been accessed when it has been modified

the attributes and the folder path we'll be not needing all these things so I

will simply hit on the edit button and as you can see the moment I click on this

edit button you can see all the different columns that has been created which

was there in the previous window okay as of now I'll be just needing the content

of this table so I will remove all this unnecessary columns so you simply have to

right click on this column and simply click on remove all the

columns all right now as you can see there is a content file and just next to

this content you see there is a drop-down button I will simply click on

combined files and let's see what happens the moment I click on this

combined files another window will pop up right in front of you and it is

asking you about the first file that is my ccassus.s.c.sv file and I'll

simply hit on okay. Okay. Now as you can see this is being created this is my particular database that we have to work on work upon all right

So this is simply the same database which is similar to our sales fact as well

Which has the same number of call almost the same column and

Same database at all right now simply click on international you can see all the related database it has loaded as of now

There are more than 999 columns you can see at the bottom of your screen and

Okay, now we what we have done we have made some changes to this particular database and now we will go ahead and

We can either merge the queries or we can append the query so as of now we will go ahead and append queries

So I will simply append this query with the sales fact so I'll simply click on sales fact and then go on

Append queries the moment I click on append queries it is asking me which table to refer to so I will click on this drop-down menu and I will get to this international

query which can be seen out here I want to append these two queries together I will

simply hit on okay now the moment you click on okay okay as you can see now it is the same table but the only difference is that I have this country column and in this country column I have

some null set as well okay so these are null so basically I would like to mention

this and I have to rectify this changes so in order to do that I will go under

add column and I will go to custom column itself the moment I click on custom

column now you have to what you say when I'm creating a custom column it's basically a column of my own

condition or my own desire so in order to do do that we have to use a formula

so let's go ahead and use a formula so I say if my country equals to as you can

see our country has some null into it so I want to give it a notation so I'm

saying that if you can see I say if it is equals to null then show me USA else it should be the country name

So this is the condition that we have given to it. So if I say that if it is the country it's null, so

show me the result as USA or else return me the name of that particular country

I'll simply hit okay the moment I hit okay another new column has been created and

this is showing me the country as well so basically it's very simple if I want to

amend or merge two queries we can simply go to this home tab and under the home

tab you can simply click on append or merge queries we'll be learning about merging queries in our later part of the videos but as of now we learned how

how to connect to a folder and how to make the changes according to our own need

I will simply remove this country column and remove it and we will be changing the name of this

with the country name and simply hit enter so I have this country name right in front of me

and I have this table which has have this folder which has been connected okay so this is very simple guys

You simply have to go under the new source and simply hit others and connect to a folder and this is how it is done in Power BI

All right. Hopefully you guys understood this video. See you until next time