Advanced-Data Model with Power Query Editor in Power BI

expertiseexcelerate

Advanced-Data Model with Power Query Editor in Power BI  Elevate your data modeling skills with our in-depth course on using the Power Query Editor in Power BI. This playlist is tailored for professionals, data enthusiasts, and advanced Power BI users looking to master the complexities of creating and managing advanced data models. In this course, we explore the full capabilities of the Power Query Editor, providing step-by-step guidance on transforming and shaping data to build robust models.  Learn how to optimize your data workflows, create efficient models, and unlock the full potential of your datasets for advanced analysis and reporting.  What You’ll Learn: • Advanced data transformation techniques with Power Query Editor • Best practices for building and optimizing data models in Power BI • How to handle complex datasets and integrate multiple data sources • Tips for enhancing performance and accuracy in your data models  Ideal For: • Data Analysts and Business Intelligence Professionals • Power BI users aiming to refine their data modeling skills • Advanced users seeking to leverage Power Query Editor for complex data tasks  Subscribe for more advanced software tutorials and stay ahead in your data analysis journey with Power BI.  Power BI data modeling tutorial Power BI Power Query Editor advanced techniques Advanced data transformation in Power BI Mastering Power BI data modeling Data Modeling Best Practices in Power BI Power BI advanced data modeling tips Creating complex data models in Power BI Power Query Editor's advanced functions Advanced DAX functions in Power BI Optimizing data models in Power BI  #PowerBI #PowerQuery #DataCleaning #BusinessIntelligence #DataVisualization #ETL #DataModeling #DataTransformation #DataAnalysis #PowerBITutorial #DataPreparation #BusinessAnalytics #AnalyticsTools #AdvancedPowerQuery #PowerBITips #DAX #PowerBIforBeginners #PowerQueryTutorial #PowerBIAdvancedTechniques #PowerBITraining #PowerBIProject #DaxPowerBI #PowerBIDashboard #DataAnalytics #DataModelingTechniques #MicrosoftPowerBI #PowerBI2024

Transcript

00:00All right, so welcome back. Now let's dive deeper into Power BI Advanced. And in order

00:10to do this, I first, we first need to open a complete new Power BI file. Because this

00:16time we do not deal with our, the easy way to get started data anymore. And that's why

00:21I want to have a clean Power BI report for our new data. So after you open a complete

00:27new Power BI file, then we need to import the data. Well, we have seen that the data

00:32is an Excel file. So what you could do is either click on Excel workbook in the ribbon

00:37bar, go to get data, choose Excel from here, click on the Excel logo or the icon here,

00:43or open also the window of get data from another source and choose Excel. It's up to us. But

00:48in this case, simply click on the icon here and then the navigate to the specific folder.

00:53In this case, for me, it's already open. So I can simply click on PBI training one

00:58xlsx and I click on open, click on open here. And then we can see here the navigator. The

01:05only difference is now that considering last time as a comparison, we have much more tables,

01:10right? Which we have also seen inside our Excel file. Now, in our case, one more time,

01:17we have the option to import tables. If tables have been created inside the Excel file or

01:22the whole sheets. Again, it would not make any difference in this specific case, but

01:27as a best practice and to make sure I only import the data which I really want, which

01:32is coming from the tables and not the whole sheet. That's why I like to use the blue icons

01:37in here. So I'm choosing the sales tables. These are the ones we need. We can see the

01:42preview here as well, but we already have seen this in the last video and you can also

01:46see it of course, if you open the Excel file. So it's nothing to check for us here. We know

01:50that this is the right data we want. So select the sales data. Then we also like to have

01:55the sales person. So use the sales person. We also like to have information about the

02:00product. We would like to see the location and in this case, the customer data. The other

02:06tables here are not relevant for us, so we can skip this. We only want to have those

02:10ones here. And this also as a tip for you as a best practice tip, always only import

02:16the data you really need. So that's something which I really like the emphasis on because

02:22sometimes people complain about maybe that our report is slow and so on. And the easiest

02:28way to make a report faster is to import only the relevant data. So please always keep this

02:34in mind also in your future projects. Only import the tables and the data you really

02:38want to use later on in the report. For us now, these are the tables we want to use and

02:44now we need to transform them because currently the data structure is not yet exactly the

02:49way we need it. So that's why instead of clicking on load, do not click on load, click on transform

02:55data. This is something I'd like to mention one more time. Hopefully Power BI or Microsoft

03:00in the future will not highlight the load option automatically anymore because this

03:05is tempting people to click on load, which means that we load the data directly into

03:11Power BI instead of transforming it up front. Of course, we can still transform it later

03:17on. I'm going to show you how that works. But in this case, always if you import data

03:23and you want to transform it to clean it and so on, to prep it up front, then always click

03:29on transform data. So let's do that. Click on transform data and then just wait. You'll

03:35see that now I knew this a little bit. I make it smaller, but now we have a query editor

03:40window which opens up, which is this one here. So let me just make this bigger here a little

03:45bit like that. And you see it's a separate window. It's in front of the Power BI report.

03:50And as long as this window, the query editor is open, I cannot edit anything in here. I

03:55cannot do anything in here. This one, this window is locked as long as the query editor

03:59is open. Okay. So inside the query editor, we have here on the left side, all the tables

04:07which we want to import. And also one more thing I'd like to mention, let me click on

04:12refresh for now. Okay. One more thing I'd like to mention is currently this data is

04:17not loaded to the model yet. The query editor allows us to first transform the data, to

04:23shape it, to clean it and so on. And only, only if we click on close and apply at the

04:28end, then the data gets loaded into Power BI. Okay. So what do we see here? Well, we

04:36see the queries. The queries are the tables we have currently in here, which we now can

04:41modify. We also see a preview of the data in here. So if I click on sales, for instance,

04:47you would see here, these are all the sales columns. If I click on sales people data,

04:52I would see the sales people data and so on. Right? So you can see the data which is currently

04:56inside. We also see in this case here, the, well, information about the data. For instance,

05:03how many distinct values do we have, how many unique values and so on. There's also

05:07additional information which can show, let me just click on refresh one more time, which

05:11can show and see how the distribution of your data looks like. Not too important to us,

05:16but right now, but it's still available. What we could do at any time is we can change the

05:23data type. So what I mean by that is maybe one more thing in addition to that, but if

05:29we import the data and open it into, or not importing yet, but if we open the data in

05:34the Power Query editor, what happens is that a few transformation steps are already applied

05:40by default from Power BI. And we can see that by, if I take a look at the right side, there's

05:46a so-called applied steps window. This applied steps window lists all steps and also the

05:53transformations we can do, or we do on the data later on. This is a really a tremendous

05:59help because it gives us then the option to see what kind of transformation has been done

06:04at each of the steps. And these three steps here on the applied steps, the source, the

06:10navigation, and the change type, this has been done automatically by Power BI. So if

06:15you take a look, you can click on a step. Please make sure don't click on the cross

06:19symbol here. Click somewhere on a name, like source or instance, and you see that the data

06:24changes now. And what this gives us is this is what the data looks, looked like actually

06:31before this step was applied. The next step was applied, actually. So this is the source

06:37data. The source data contained here, as you can see, the different kinds of names of the

06:43tables we have imported. So the data type was a table and the item, here's again the

06:48name, the kind is sheet. It was an Excel sheet. And to be specific, it was a table

06:54on a specific Excel sheet, which had the name 2023 sales. That is what the source looked

07:01like. Then the navigation appeared that the specific sheet was selected. In this case,

07:08the sales 2023 sheet. And in the next step, the change type was applied because you currently

07:15can see, if you take a look at the column, there's always ABC 123, ABC 123, and so on.

07:22This just means that currently, Power BI has not yet selected a specific data type, like

07:27for instance, a decimal number for a number, right, a numerical value, or a date for a

07:33specific date column, for instance. This happens automatically in the next step. So if you

07:37click on change type, select the step, you see that now, each of the columns has a data

07:43type attached. ABC, this means this is a text or a string value. This little calendar

07:49icon here means Power BI has identified the order date as a date. And for the numbers

07:56here, for instance, 123 for the quantity, this means that now this is a number. So that

08:01what's happened under this change type. And by the way, again, these steps have been done

08:07automatically for us. And we can also right click on a step, and there's an option to

08:11rename it. This might be helpful in the future, if you do a lot of transformation steps, which

08:17then get applied here. And you only have these, let's say generic names for transformation,

08:23maybe you would like to be a little bit more specific, either for yourself, or maybe a

08:27colleague of yours working on the report as well later on, or you you send a report to

08:32him or her. And then it might be helpful for him or her to understand actually, if you

08:37simply name those steps properly. But he or she could also, of course, click on a step

08:43and take a look at what the data looks like for and then see actually what the step did.

08:49And if you or if we would like to apply our own steps, for instance, for whatever reason,

08:55we would say that instead of having this as a whole number, an integer number, I'd like

08:59to see this as a decimal number, the quantity, we can simply click in here 123. And then

09:05we could say, I'd like to change this in here to a decimal number. So you can see there

09:11are various data types. In this case, I'd like to see this as a decimal number. And

09:15watch what happens with the icon here, as well as in here in the applied steps window.

09:20If I simply click on decimal number, you click on it, you see that first, a little pop up

09:25window appears, which asks me, do I want to replace the current step or add a new step?

09:32The reason why this question is asked is because the last step is already a change

09:36of data types. So I could tell Paul BNR, I want to replace the current one, which means

09:42that the transformation from quantity column into a decimal number is also part of this

09:47change type. Or I could add a new step. In this case, because it's also only a demo,

09:54and I'd like to switch this back easily, I click on add new step. Now, if I click on

09:58that new step here, you see that now an additional step has been created here, which is called

10:03change type one. Again, it's a generic name. But now I can see that there's a one dot two.

10:09So the data type of the quantity column has now changed to a decimal number. Before, if

10:15I click here, it was a whole number, one, two, three, and now it is a decimal number.

10:22So that's why this applied step window is really, really helpful, because each step,

10:27is recorded here. And you can always go back, take a look at it. And also, if you say that

10:33this transformation is something I did not like to do, so I want to revert it, you can

10:37simply now this time hover over the X here, click on it, and then you see that the step

10:42is removed. And I have my original change type to a whole number here back. So that's

10:49really, really helpful to have this window here to show what kind of transformation we

10:53have applied, and also to go back in case we want to do that.

10:59One thing I'd like to mention is, if we remove a step from here, we cannot go back. So, just

11:06click on the cross symbol here, as we've done for the change type, we cannot go back like

11:10control C, control Z to go back, or have a back button here, this is not possible. So

11:17if you delete a step, the step is deleted. You need to redo it manually yourself. Okay?

11:23So that's just one thing I'd like to keep you in mind.

11:26Alright, so now we have here learned how, what the supplied step window gives us. Now

11:32to our tables. Currently, we have here sales 2023, and so on, that's fine. But here, for

11:38instance, for salespeople data, product data, location data, customer data, the tables are

11:44all called underscore data. I don't like that. So I could select the table. And let me for

11:50now click refresh here. And I can rename it here. So I can give it a different name.

11:54Let me just remove the underscore data. Just do that, press enter. And you see that now

11:59the table has been renamed. By the way, that would also be possible. If you double click

12:04here, I also could here, just remove it from here, just remove that underscore data, press

12:09enter, and also table has been updated. So I like this as well. This, there's a fresh

12:15option here. But just by the way, this should not be visible for you. It's just because

12:20I last used the table for two days ago, where updated the data. That's why it's just popping

12:24up here. But for now, let me just click on it. Okay, there it is. Okay. So also for the

12:30location, I can rename it either again in the property option here on the query settings,

12:35or I could double click here, or I could also right click and rename that will also be possible,

12:40right? Three options to rename the table. Let me call this location. And let's double

12:45click here and call this customer or customers. But for now, we have our tables here. Okay,

12:53so we renamed the tables. And now I also would like to maybe structure the tables. So I could

12:59right click here and say I'd like to create a new group. So grouping is also an option

13:06to get a little more order in here in the query window, especially if you have a lot

13:10of tables. And also if you have different kinds of staging inside the query editor.

13:17What I mean by staging, just in case you're not familiar with that, it might be the case

13:20and you'll see this in a minute, that you have some let's say, basic tables. And based

13:26on these basic tables, you do transformations to create new tables. And maybe you'd like

13:31to have some kind of order structure to see okay, these are the tables I imported. And

13:37then I did transformations on them in order to create new tables. And I'd like to have

13:41them in a separate folder. It's just a structure. It's not required to do, but I personally

13:45like to do it to have a little bit more structure and order in my in my applied steps here in

13:49the query section here. So I could name this for instance, this is my, let's say, model

13:56TBL. TBL is just an abbreviation for tables. I could give it a description. For now, I

14:02just skip that and I click OK. And now you see that we have the other queries, which

14:07are the current queries we have. And we have the model table folder. And the model table

14:13folder, I want to see all the tables we've got, which I'm going to use directly in the

14:16model, which in my case is customer, location, product and sales. So these are the four tables.

14:24I can select the first one, hold my shift key, select the last one, or I can control

14:29click and select them immediately. And I want to drag them and place them inside the model

14:33folder, drag and drop. And now they are part in this model table folder, which of course

14:39right click here could also be in this case, you can ungroup it if you want to do that,

14:44or also have a look at the properties and also rename it. If you don't like the name,

14:47you can change this later on. So these are the tables we're going to use directly in

14:52the model. That's why I put it, placed it here in the model table folder. For the other

14:58ones, these three, those are not yet exactly in the structure. We need them. We just click

15:04on refresh it. Okay. So because as I said at the beginning, if you remember maybe from

15:10the last video, these are currently three separate tables. Our goal is to bring them

15:15together into one giant table. So we have the sales 2023, 2022 and 2021 in one table.

15:23That is the goal. How can we do this? Well, now we can have a closer look at all the different

15:31transformation options, which are up here in the ribbon bar. And by the way, not only

15:36those under home, there are also some under transformation and the other Tom here at column

15:42there also transformations and so on. So a lot of transformations in here. But for now,

15:47what we need actually is under home, the option to append queries here under combine

15:54combines a good word for it. There's an option to append queries and appending. If you hover

15:59over it, it gives you some information. That's by the way, always as a tip here, hover over

16:05one specific option, replace values, right? And gives you some description of what this

16:10transformation can do for you. For us now under append queries, append queries, append

16:16this query to another query in this file. And that's what we need because what appending

16:21here means is simply stacking. So please say it's 2023, 2022 and 2021 on top of each other

16:30and then have one table. That's what append queries can do first. Now for pending queries,

16:35we need to be a little bit careful. What I mean by that is you can click directly in

16:40here, but you could also click on a little dropdown area. It's a little bit hidden. I

16:46have to admit, but if you click on a dropdown, you have two options. Either choose the append

16:52queries, which is the default one. If you click on here or append queries as new. Now,

16:58what is the difference? The difference is if we append queries, that means currently

17:03have sales 2023 is selected. And if I append the queries, this would modify this table.

17:09This would mean that if I click on append queries here, if I do that for now, you see

17:13that our new window appears this one. And now I have the option to either to choose

17:19two tables that I can select another table I would like to append or three or more tables.

17:24And then I could add here 2023 and 2021. But this would modify my original table. That

17:32means there is not, we do not have a sales 2023 anymore, but it would have a sales table

17:37which is named 2023, but it contains also the data 2022 and 2021. And because I like

17:44to have it differently, I click on cancel for now and say, I'd like to append queries

17:50with a little down area here, but I like to append them as new. So I click append as new.

17:57And now let me just drag this up here. I could say three or more tables. And then I say I

18:02like to see 2023, sorry, in here. So let me also choose 2022 and hold control key 2021.

18:09And then I add. So I'd like to create a new table with three or more tables. Of course,

18:14if you have several tables, then you can also add them. Nowadays, we have three of them.

18:20Now we click OK. And now we see that we have one new appended query, which is completely

18:25new, right? So we still have our original 2023, 2022 and 2021. But now we also have

18:32this sales table, which is called append one. And of course, we'd like to rename this.

18:37So let's go here and say this is here sales. And then just say it's still okay. Let's just

18:43call it sales. Press enter. And now we have our prepared sales table, which contains the

18:49data of all these three sales tables. And I can easily show this to you because we can

18:55select the table, go to the order date here, click this down arrow here. And then let's

19:01just load more. Click on this option here. And you see that there is 2021 data in here.

19:06And if I scroll down, I can see 2022 data in here. And if I scroll down, I would see

19:112023 data. So we can clearly see that this allowed us in here, the order date, in this

19:18case, the transformation with appending here, the order date showed us that we have now

19:22appended all these three tables and put it into one giant table. And this is actually

19:28the table I'd like to use in the model. So that's why now I can drag this table up here

19:33and also place it to my model tables. And now we have our sales prepared for actually

19:40now getting this imported into Power BI. Before we do this, however, there's one additional

19:46thing which is important. And I'd like to mention this. If we would, if we would click

19:50on a close and apply right now, we will still load all the tables. Also these tables in

19:56here, even though we don't want to use them. This is why it's best practice now to right

20:03click on the table here on all of them. And you see here two options, enable load and

20:09include and refresh. Now, currently, both of them are checked for the tables. Include

20:15and refresh. This is something we would like to have because including refresh means if

20:21the underlying data for sales 2023, for instance, right now for me, this year is not completed

20:27yet. So the data in the sheet might change. If I would uncheck this included report refresh,

20:34this would mean that if we update, if we refresh the data, then the sales 2023 gets not updated.

20:42And the problem with that is if the sales 2023 gets not updated, the sales table gets

20:47not updated because it depends on these three tables. This is why what we want is leave

20:54this checked, include and report refresh so that these tables also get updated when new

20:59data is there. But we want to disable the load. So we check this unable load, we disable

21:05it also uncheck it by simply click on it and also enable this one. You will see that now

21:11the names of these tables are written in italic. That's an indication that they are not included

21:16in the load anymore. And what this means is that the underlying data for these tables,

21:22if we refresh the data, still gets updated, but we will not see those tables inside our

21:28reports. We will not import it directly. And I'm going to show you this right now because

21:34our transformation is done. We have our tables in here. We renamed them. We combined the

21:39sales table. So now we can click on close and apply, click on close and apply. And now

21:45we wait and you'll see that now the tables get loaded into the model and just a second.

21:51And there we have all tables. So we have the customer table, location, product, our newly

21:56created sales table and the sales people. And notice there are no sales 2023, 2022 and

22:042021. But these tables would be visible if we would not have unchecked this enable load.

22:12And because we don't want them, we only need our sales, the combination of them. That's

22:16why we uncheck this enable load. Now we have done our transformations and we could now

22:23start actually creating our report. But if for every reason we are not finished, we are

22:29not done yet, right? And we want to explore more or also shape our data later on after

22:35importing the table, there's still an option to reopen the Power Query editor by simply

22:40going under transform data. On the home, if you click on transform data, click on it,

22:47then the query editor opens again and we can continue changing, preparing, cleaning our

22:52data. So we are not locked to what we have done at the beginning. We can at any time

22:58in the future, reopen the transform data, the Power Query editor, and then we can change

23:02something here. Also, we could right click here and I could enable the load again if

23:07I would like to do that for whatever reason. For us, we do not want to have it, but I just

23:12wanted to mention and to show you here, you're not bound by anything. At any time in the

23:16future, you can change it back again. Okay. And that's it actually for the first part

23:22of the Power Query editor. It was again quite a lot, I know, but hopefully it was interesting.

23:28Hopefully we're able to follow along. Of course, feel free to rewatch the video and please

23:33make sure that you also do the same steps and do it practical with me because that's

23:37the way how you learn. Okay. So thanks a lot for watching and I can't wait to see you

23:42in the next video. Until then, thanks guys.

Category

Transcript

Recommended