Up next in 10
Advanced Excel Power Query | How to Clean Financial Data? | Tutorialspoint
368 views
Oct 18, 2024
Advanced Excel Power Query | How to Clean Financial Data? | Tutorialspoint Advanced Excel Power Query Online Training. In this power query tutorial, you will learn about How to Clean Financial data in Power Query. Get Certified in Advanced Excel (Power Query):https://bit.ly/3LdiidF Use coupon "YOUTUBE12" to get ‘’FLAT 12%’’ OFF at Checkout. Quality Learning to UPSKILL yourself only at TutorialsPoint. Explore & Learn the top trending courses curated by the best trainers in the Industry @tutorialspoint: Your go-to Learning Solution. Power Query is a powerful tool given by Microsoft. Power Query combines different software(SQL, Excel, VBA). With our Trainer, You learn: - Introduction to Advanced Excel Power Query - Excel Installation - Advanced Excel Power Query Text Functions - Date Function- Calculate Age in 2 button clicks - Merge Queries from two Excel Files - Append 100 Excel Tables - 99+ Excel functions can be replaced by button clicks - M Function for Date - 700 M functions to reduce tasks - Merge Queries - No VLOOKUP. - How to Clean Financial Data with Power Query. Upskill your career by training on the best-TRENDING Courses in the Market.
View Video Transcript
0:00
Hello everyone and welcome back
0:02
In this video we'll see one more case study related to finance department
0:07
How do I clean this kind of financial data when I get from the company
0:11
It becomes very tedious and very challenging part to create a pivot table or to do any ysis part
0:16
Or if I want to create a chart, report, anything. So you see on the screen I have some data, that's a financial data
0:23
And you see, I have June, July. This sells are merged. Then I have the second heading, actual budget and the variance
0:29
budget and the variance, same way actual budget and variance. On the left hand side, if you see
0:33
there's a revenue given and these are the empty rows, total revenues given, expenses, total
0:37
empty rows. And same way at the bottom, there are two empty rows or one single null row at the
0:42
bottom, the blank rows. How do I clean this data and convert into this format? So I can create a chart
0:50
pivot table, pivot report, anything with help of this data. So from coming this data, you know
0:55
unclean data to clean data, it takes only seven to eight. button clicks in Power Query. Seven, eight button clicks, no using function, no using any formula
1:04
no anything. Just using a few button clicks in Power Query, we'll see how to clean this data
1:08
Now, before going to Power Query, let me explain you what are the things we'll be doing here
1:13
First thing, I'll try to fill down this column so that I can get revenue, revenue, revenue
1:17
revenue, revenue, revenue, everywhere. Same way, I'll get expenses on all the places. Second thing, I don't want this total revenue, total expenses, net, loss and income
1:25
because this total values I can calculate later on. So I'll try to filter this values here
1:31
Third thing, if you see, there are two column headings into this data here
1:35
I cannot unpivoted two column headings. In the previous video of a scene, the case study, there was only one column heading Jan to December
1:42
It was easy to unpevote. But there are two column headings you cannot unpevote
1:47
So what you have to do is you have to merge these two records and then you have to unpevote
1:52
Okay that the thing But again there a challenge there is no merging rows in power query there is merging columns possible in Power Query So I have to do a transpose merge the columns etc etc and etc
2:04
And finally, I'll come back to that clean data. So start with the basic one. Let me show you how to pull this data and do it further
2:11
So right now, what I've done is I have created a name range and given the data as my data. The name range as my data
2:18
I have purposely selected this data here. I cannot convert this into a table and get it
2:22
because it doesn't look like a table format. Okay. Now, after giving this name range
2:27
click on data tab and click on from table. So ignore the warning which you're getting
2:32
Right. Now you can see I have got the data that is column one, column two, and so on
2:36
And carefully observe, when I had June, there were three cells which were merge. Now you see June, null, null
2:42
July, null, null, these are the values given. So I have to also fill right
2:46
There is no fill right option in power query. There's only fill down or fill up
2:50
So that thing also I have to work out. So first thing, select the column 1, transform and I'll just select the option fill down
2:57
So if I click on fill down, you can see all the places revenue, total revenues, expenses will come here
3:02
Now, I have to remove this total revenues and also total expense and net income loss
3:07
So click on this drop down and I can select the option from here as deselect total revenue expense and net income loss
3:15
Because this can be calculated later on. So in normal table, you don't need this aggregation value
3:20
So click on okay. that has been removed Next thing If I want to fill on the right there is no method Okay June fill right July fill right You see there is no fill right or fill left option So I have to transpose the table so then only I can fill down that option Okay So now I just click on this transform tab transpose and you can see this has been transpose Now it becomes very easy to fill down So select the column and now I can select the option as fill down Okay I got this data here After filling down when I again transpose back because I brought it for filling down This will be two column headings There is no method in Power Query to unpivot two column headings There is only one method
4:00
You can only combine these columns and make it as one column. So select those two columns
4:06
So I'll select these two columns, that is column one and column two. And I'll try to merge this columns using Transform
4:13
And there's an option given as merge columns. And the separator can be anything, comma, space, anything
4:19
because later on I'll just remove that delimiter. So I'll select the option as colon and I'll click on okay
4:25
Now, these two columns have become one. Okay, these two columns have become one
4:30
Now, why did I combine this columns? You can see right now. Again, if I click on transpose
4:35
now I have only one column heading. Previously, I had two column headings
4:40
Now, I have only one column heading. Now, the last thing, if I want to un-pivot
4:45
I have to promote this as headers. This row should be promoted as headers
4:49
So, transform, and you can see there are two options. Use first row as headers
4:54
So I'll select the option as use first row as headers. Now you can see all the headers are been given here, June, June budget, variance and so on
5:03
Now, the last thing is, I have to keep these two columns constant and I can unpivot the other columns
5:09
because you see this is June, June, and so on. So select those two columns, right click on any column heading, any selected column heading
5:17
and then you can select the option as un-pivot other columns. Click on un-pivot other columns and you can see the data is coming into a table format
5:25
It has still not come. We have to again clean the data. This attribute column has to be separated
5:31
The separator here is nothing but colon. So transform tab and I have the option as split column split by delimiter
5:38
So I'll select split by delimiter and automatically it will pick up the delimiter that is colon
5:43
I have to click on okay. And you can see these are two different columns I have
5:47
After doing that what I can do is I can just keep on renaming the columns here So I just double click here and type as month Second one I write as details or actual hyphen I can write as budget
6:04
And third one I can just write as product type. And fourth one I can write as revenue or expense
6:16
Okay, revenue and expense. Please ignore the spelling. So I've got this four columns
6:24
And I think this variance can be calculated later on. So I can just click on this drop down and I can select the option
6:29
De-select this variance. Click on okay. Okay. So this is a clean data which can be used for creating charts, reports and visualization
6:36
Anything. Now I can click on home, close and load. Okay. So see, that's a new data which I've got
6:42
And this is the unclean data which cannot be used for ysis purpose
6:46
Any point of time you want to go back and see what are the steps I've applied
6:50
So what you can do is you can just double click here, that is my data. And now you'll get this power query editor
6:55
What is the first step, second step, third step, this is a revision. So you can just click on the first step
7:01
This first two steps automatically come. Third step is, if you see, that is a fill down
7:05
Okay. So you see, that's a fill down. Keep on observing. If I keep on clicking here, the steps would be given here
7:10
So the filtered rows, and then I transpose the table, and then I fill down, and then I merge the columns
7:16
and transpose, etc., etc. So this can be notes for you. You can keep this notes with you
7:21
At any point of time, if you get similar case study, you can apply the steps
7:25
Yes, depending upon the logic, which is relevant to your example. So I can just click on close and that's it
7:31
So any changes made in this data will be reflected into your output data
7:36
And same if you create a pivot table or any pivot chart from this data
7:40
same story. Data source changes and the values will change here. So I hope you have understood
7:44
how to clean this financial data, by using Power Query in just few button clicks
7:49
That's all for this video
#Business & Productivity Software
#Business Education
#Financial Planning & Management
#Other