query

Power BI Tutorial for Beginners: Get Data. Query Editor (1.1.2)



Sharing buttons:

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.