In this video I will show you how you can use Power BI to not only connect
your data but also the green shape and transform it before you bring it in. Keep
watching. So we're going to go ahead and pull in data from another data source
and this one is kind of representing a manual or offline data and I see this
scenario all the time that business users have to combine some systemic data
with some other data source. Its either manual or their own Department
data set which is not available in the system sources could be a third party it
could be something they're pulling from the web. So I'm going to go to get data
again and click more and this one is actually an excel file so when I click
connect and sequel and temp that's my budget file that's one-half downloaded
and I'm going to click open again this represents a manual data source and
there's only one table I can click on it to see a preview. I do want this table
but this time I'm not going to click load because clearly this is a little
messed up I don't want this column 1 column 2 that's not how I want to load
this data amongst other changes. So this time I'm going to click edit right, now
when you click Edit you would notice on the left that this is not the first
query the tables that we loaded earlier are also represented here but they were
just the default I mean they don't have much going on here it just simply grabs
the table from the database but for budget it kind of grabs the table from
the database but we know we need a lot more transformation, By the way this is a
new window so this is a query editor notice how it's opened up open as a
separate window from the Power BI desktop but you can consider it a
component of Power BI desktop. So let's go through the query editor interface
quickly again the ribbon up top very very easy to use a lot of stuff 95% of
what I use is just by clicking buttons up on the ribbon over on the left you
have a query's pane a lot of times you end up with quite a few queries
in your data model and this helps you kind of navigate go across and just
quickly edit the query that you need to. So that's a query pane I'm just going to
collapse it for now because we're going to focus on the budget query, over here
this is showing you how the data set is going to be loaded now this does not
pull in the complete data set in our case it just happens to be a very small
data set so you're actually seeing everything but imagine you were
connecting to a table which had a million rows, but no means it's going to
pull in a million rows and show them to you here. It shows you just a preview
just the top, you know, a few hundred rows I think that it shows you here so it
shows you a preview of the data set and lets you work with that and over on the
right side are some of the most interesting elements so sure there's the
query name I can change that if I wanted to, there's also an all properties button I
can click that and I can perhaps specify more description, this is a great thing
for documentation for yourself or somebody else who might step in your
place. You notice there are some other options here we're going to come back
and talk about these options later for now description is interesting but
really my favorite part is this applied steps ooh
boy, this is magical, now when you connect to an excel sheet like this what it does
is it scans the columns and tries to make its best guess about the data type.
Now notice in our data set and let me show you the price so this is the file
that we're trying to load and this is what I call a human friendly file it's
got headers up there, it's got color codings, got subtotals, subtotals, grand
totals here grand totals here and the numbers are spread across on the
columns and this is fine this is probably in a well at least in this
scenario we're pretending that this is actually manually maintained by a group
right, so they they go through maybe a review cycle and they discuss the number
what it needs to be and they come back and type it in. Right, so it's
maintained manually so that's the follow connecting to and go back to Power BI
desktop, so we're trying to pull it in here but clearly it needs some cleanup.
So in this scenario when you don't have the data starting up top, then often this
automatic step that's added change type doesn't really help so in most scenarios
what I do is just hit an X to delete that step right so this is this is very
easy to work with a lot of these have this gear icon which you can use to
let's say change that steps in this case I can point to a different location.
Imagine if a file source moved now it's found in this folder rather than that
folder you can quickly fix that or you can also just hit the X and delete that
step. So change type let's get rid of that we'll worry about that later, and
let's get started working on this data set. The first thing we're going to do is
remove these rows up top these three rows and they're not needed so I'm just
going to click remove rows, remove top rows, and I'm going to say, remove
three rows hit OK and notice what happened here, first of all this step got
recorded, remove top rows and of course that's reflected here as well
three rows are gone and the cool part is you can step back and forth through
these steps so you can go back like oh this is how the data came in and click
this is my next step there's a textual description which tells me what
happened here. Remove top rows and boom I can see in the data set that that's gone
let's keep going and as I'm going along I want you to keep your eyes glued on
this applied step this is really really cool, Alright great, so now what I want to
do I don't want this column 1 2 3 headers in there, really row 1 the first
row should be used as headers guess what there is a button for that right here
use first row as headers I'm going to click and again it's going to transform
hit here and record that step over here so used for asteroid headers boom that's
done again you can replay it go back click this is what the changes happened.
Let's remove the subtotals here so I'm just going to click on the arrow
and this exposes a lot of options for now we're just going to say text filters
does not contain total. Now mind you that power query in general is case sensitive
so if I type in a lowercase total let's try that
notice the subtotal rows and grand total that doesn't do anything I mean it stays
filtered rows but nothing happened here so I'm going to delete that step and try
it again. Text filters does not contain and this time I'm going to use the right
case total and now those are gone this is probably the first thing that caught
me off guard the case sensitivity but once you're aware I don't think you're
going to get in trouble and of course there are lots of functions and and
methods to get around it if you don't want a you want a case insensitive
matching you can do that you can convert things all to uppercase or lowercase and
then do the matching or things like that. So we'll make sure we'll
cover that at a later point so for now you've got filtered rows done, subtotals
are gone, if we scroll all the way to the right there's a grand total column which
we do not want because again we have the monthly totals no need for the grand
total so we're going to right-click on it and just say remove. By the way the
button is up here as well remove columns so again
grand total is gone and it's recorded in here. Perfect, now the mind the next step is by
far my favorite. Hey, keep watching more videos and keep learning Power BI but if
you did enjoy this video I would love to hear from you so leave a comment, like,
subscribe, all the good stuff. Power on my friends.