0:00
Hello everyone and welcome back
0:02
In this video we'll see how to append multiple Excel files one below the other in Power BI
0:08
Till now we have seen how to append Excel tables, CSA files and now Excel files should be the topic here
0:14
Now let us see practically what I'm going to show you here. I have a folder where there are multiple files, not only Excel but I have PDF, ppt and notepad file
0:24
Out of all the files I'm only interested in one particular Excel files only
0:29
the four Excel files, North sales, South sales, East and West Sales
0:33
So before appending, I'll just open any one file and show you what the data is there
0:37
So let's say I'll open anyone, South Sales. And the data is, in each file I have three sheets
0:43
One, two, and three. You see, one, two and three. Plus each sheet is having one table
0:49
Okay, you see July sheet is having one table. And name of this table is, let's say July
0:55
Again, August table is having one table, that is August 2016. So like this, you see, you know
0:58
this one file is having three sheets and three tables. So I'll just click on close button here
1:05
So let me show you what I'm trying to convey the message here. So I have a folder kept here
1:11
In one folder I have multiple data sources. What are different data sources
1:16
There is Excel. Then I have PDF, CSV, and I have PPT
1:24
So that's a CSV file. That's an Excel file. Please allow me to write this
1:27
Now, one folder, multiple data sources. But I am only interested in Excel files
1:33
In that Excel files, again, there are different distributions. What are the distributions given here
1:38
I have different sheets. I have different tables. And there are different range
1:48
Name range are given in Excel. Okay, different name range. So first of all, I'll bring the folder in Power BI
1:55
I'll filter the Excel files in Excel. Excel files I filter only the tables and then I try to append the table So that a complete story about appending Excel files Let us do that So I just go back to this place and I just copy the path of this folder So I just right click copy the path of this folder and come to Power BI In Power BI I just click on Get Data Already there are some few queries which are there but this is a new query which I be adding So click on More and here I can just click on folder option and paste the path over there Folder connect
2:31
and I'll paste the path. Click on OK. So after I click on OK, and you see these are all the information about the files kept in that folder
2:41
So you see Excel, CSV, PDF, everything. So obviously the data is not clean
2:46
I have to just click on transform. If the data was clean, I would have clicked on combine and do the process
2:51
So transform data. And now you can understand out of all the columns in this place, out of all the columns
2:58
the only first three columns are important. case first three columns one is the data second is name of the file third one is the
3:05
extension the rest columns are not important so I can just remove the other
3:08
columns so I'll just select the first three columns right click remove other
3:15
columns now if I ask you if I ask you if I show you this ppd here this one out of
3:20
this folder if I only want Excel files not all the files so the method would be the
3:25
extension column I have to filter okay the other files so I'll just click on this drop-down
3:31
select everything and just keep Excel files and I'll just click on okay so you see only
3:35
Excel files are filtered so now if you put any file in that folder only Excel
3:39
files would be visible the steps are getting recorded on the right and side now
3:43
so out of this all the columns only the first column is having the data that is
3:48
the binary okay you see the contained column name of the column is contained column
3:52
so here is the actual data in this data we can find out how many tables how many
3:58
sheets how many ranges are there so here comes the important function which after write. So add column and you can just click on custom column
4:06
Add column and there an option custom column And here I write a M function This window is used for writing M language M function and I can write as Excel dot workbook and open bracket
4:19
Excel dot workbook and open the bracket do not try to write it just double click on
4:25
contained okay you can also write it but what happens is while writing you might make
4:29
a spelling mistake or case sensitive problem you can do it so now I've just
4:33
written Excel dot workbook contain e capital W capital M language is case sensitive
4:38
And you see there are no syntax errors. I can click on OK. Now, all the data has been extracted from this table or from this files and given into form of table
4:47
I don't know what the table is, but let's try to expand. If you want to expand, there's a double-headed arrow
4:53
I can just click on double-headed arrow. It will give you what is the information in that file
4:58
So I'll just click on OK. Now, you have got many rows
5:02
You see how many rows you have getting? 24 rows. Why 24 rows
5:06
Let me show you an example here. each file is having three sheets and three tables
5:11
Three sheets and three tables. So total eight, what I can say, six number of quantities in one file
5:19
So six into four, four Excel files, I'll be getting 24. See what I'm trying to say
5:24
There are total three tables in per file and three sheets per file
5:30
So there are like this kind of four files. So into four and into four
5:35
So I have 12 sheets and 12 tables But I'm only interested in this tables
5:40
So what I can do is I can just go back to this place Out of this 24 I can filter only tables
5:47
How do I filter table? Somewhere on the right and side If you see on the right hand side we'll be getting one column
5:53
Yeah, that's a custom kind column So from this custom kind column I can only say I only want tables
5:59
So I'll just deselect this sheet and click on okay So you see only tables are visible here
6:04
Now if you see total how many tables I expecting three tables per file Like this there are four files Three into four 12 Now if I go back you see how many records I have 12 That means I only filtering tables from those files Now out of all the data out of all the data only these two columns you can keep
6:24
Okay, which one? One is custom data and custom name. Name will have the name of the table
6:29
This data will have the actual table contained. Plus, if you want, you can also keep the file name
6:35
That is a north or you can say name. So I'll just select those three columns one by one using control click
6:43
Now I can right click and remove other columns. So that's the final output I'll be getting here
6:47
These are the three columns here. Now when I just click on expand button, if I click on expand
6:52
and I'll just click on deselect this checkbox. That's only for adding prefix
6:57
I can just deselect, click on okay. So these are the output
7:00
These are all the tables from that folder from those Excel files which have been upended together
7:06
So how many I have? More than 1,000. Okay. So more than 1,000 rows I have
7:11
It will not show you the number. So what you can do is you can just click on transform data and click on count rows
7:16
So total there are 1540 number of rows present in all the tables in those files
7:23
So let us cross-check now. When I go back to this folder and suppose next month or next time someone comes and put one more file here
7:31
Control C, control V. So if one more file comes here, that number 1540 should change
7:37
So if I go back to this place, this number 1540 should change. Let us see Home menu bar and if I click on refresh button and you see 1925
7:45
So this would be a job only one time. You keep on pasting the Excel files in that folder and you'll be getting the output here
7:52
Okay, definitely the last step is not required. So I'll just click on cross button here
7:56
So these are all the tables from that folder which has been filtered. Let's do a quick revision what we have done
8:02
I have just copy pasted the folder path. I filtered only Excel files
8:06
I wrote one function to get all this data, that is Excel. dot workbook. From that I just got 12 tables, I filtered those tables and then I have
8:14
appended those tables. So this was the complete story of appending Excel files kept into
8:19
one folder here. So I hope you have understood and that's all for this video
