Streść mi w postaci pytań i odpowiedzi: "In this video, I'd like to give you the update which Microsoft released in the new July 2023 version
regarding the on object feature.
Now, the on object feature means that we can select any kind of visual like this one, for instance.
And then based on our selection, we can directly click on these icons here to either choose the data
we want here as well as, as you can see here, what kind of visual we want.
Currently it's this donut chart, but I can switch it here if I want and we can also go up here and
do our formatting.
Now if you work with Power BI in 2022, for instance.
So in earlier versions, then you were actually more familiar with these options here actually pinned
up there on the upper right.
Now if you want to switch this back, you can easily do this using the pane switcher, which means that
you can click on this little icon here and you can say move to pane switcher.
And if you do that, then you see that.
Now you have the box here with all the visuals up there on the right side again, and then you can use
it here.
So it depends on what you prefer and you can easily switch it back by simply click this icon again here
and then say you want to move to visual and then it's back on the visual itself.
So it's really up to you what kind of option you want to use.
This is now available for you.
Besides doing it here, you could also go under the View tab and there's also this option pane switcher,
and you can go there and then you can say, I want to move here to the paint switcher and then you can
switch it here or you can of course go back there again and you can act untick this option and then
it's back on the visual.
If you're just wondering what this actually means, this always show pane switcher.
This just means if you take a look at the little bar here at the side, the data view, if I untick
this here, you see that this is gone.
That is the only thing which this does.
And the second thing which is new is that beside clicking on these icons here, to add them here to
the pane, like for instance, if I click on format, you would see here there's also the format option
here.
If I click on bookmarks, I would find the bookmarks and so on.
So this is an option.
And new in the July 2023 release, you can also click on the plus symbol here.
So customize pane switcher.
If you click here, then you can simply check here the boxes, what you want to see and what not.
So if I go to the selection, you will see that this also appears in here.
So it's up to you how you want to do it.
But this plus symbol now is also available for you.
I just wanted to mention this because in the videos when I recorded that, that was not available.
And just to have you have the full experience here and know where you can find everything, I want to
give this video here to you.
So hopefully that was helpful.

Now to get started, we need to set up our data model and you find the data attached in the resource
section to follow along.
And this is what I highly encourage you to do, because this is a hands on course and that's the way
how you learn power.
BI So let's get into that.
You already see I opened a complete new Power BI file, so if you have not done so far, please do the
same.
So make sure to be able to follow along.
So to get some data we have now the option to go under the home ribbon here and then choose one of the
data options up here.
Or we could also use one of those which are available in here.
Or if the one source which we are trying to leverage is not there, you could go to get data from another
source to open the navigation window.
For us now it's quite easy because we have an Excel file, so I can go to import data from Excel, click
this option, and then the window or in this case appears where it allows us to actually put the get
the data which we're going to use.
And in this case is this Pi underscore training XLS X, that's the file.
So I like to select it.
You can either double click it or click on it and then click on Open.
So then the navigator window appears and here we have actually the well, the content of the Excel file.
And you can clearly see there are different icons.
So if you have already taken my first power bi course, then you probably know that the difference here
is that the upper ones are tables from the Excel file.
The lower ones are the complete excel sheets.
And as a best practice tip, I would always recommend you to go with the tables just in case they are
available, because then you make sure you only import the relevant data from this table and not additional
data which might also be on the specific sheet.
So that's why I will click on the employee table.
And this also gives us a little preview of what the data looks like.
And you see that we got employee names and the surnames, also the country.
And I can already spot here that we have a little issue because currently here there are missing values.
So these null values simply refers to missing values, which we need to fix when we open it in the query.
Editor.
Okay, so what else do we have?
We have the orders table.
Let's select this one and also check it.
And in here we have various columns which look fine at first glance.
But of course we need to check this in detail when we go in the advanced editor.
So in the power query editor.
So otherwise we have also the suppliers.
Let's check this option here and we can see that this is the suppliers list.
Okay, that looks fine.
Okay, so let's select those three for now.
And then we need to transform the data because the data, as we've seen, is not yet in the right format.
And even if you think it is in the right format, I would always recommend to transform the data first
and check it in the query editor because you can never be completely sure that there is not something
you want to actually transform first.
And even though you have the option to go back to the query editor later on, it's always best practice
to always have a look at the data first and only if the data is really the way you want it, only then
load it into power.
BI And this of course is especially true if you have a really large dataset, then always, always,
always go to the query editor first and then check out whether you really need everything you can see
there.
So let's actually go to transform data, click on it, and then we wait.
And here is our navigator window and we can actually go to the employees first because there we already
spot that there is an issue which is the office team in here.
So this is something we need to fix and we can easily do this by using the options which the Power query
editor provides to us.
In this case, I want to fill those missing values.
That is why I go to transform.
And under transform we have various transformation steps and one of those is fill.
Now there are options to fill up and fill down, so let's just check.
What do we need actually?
Well, the first one, the first value is inserted here.
The other ones are missing.
So we clearly want to fill down.
Right.
So for us, it's Team Targaryen.
That is why we need to fill those values down and here for UK Team Lancaster.
So we need those values down.
So let's do that.
Select the column first.
You can clearly see in the black bar at the bottom at the top, sorry, that there are currently missing
values.
But if we fix it by simply fill down, click on fill down.
You see that now they are filled and no data is missing anymore.
Also keep in mind that you always have, if you right click on a column, also various transformation
steps which are there.
And there's also, for instance, the fill option.
So you could also do it from here.
This is what I personally call redundancy in power BI but not in a negative way, but more in a way
that it allows us to do the same transformation on various places in power.
BI Which I personally think is helpful because sometimes I do not find the default setting maybe immediately
and then I can use the other one.
So it's easier to find, at least for me.
So okay, now we filled this and we also want to check before we go what is actually here, the data
type.
I can see this is one, two, three.
ABC.
ABC.
ABC.
That looks fine.
Especially because the employee ID is the ID.
We want to link to our model later on.
That is why I would like to have here a number and make sure that the employee ID inside our facts or
orders table is also a whole number.
The last thing I'd like to do is actually rename this and some one of my preferences is always use something
like TBL at the end to to identify this as a table.
So I press TBL, press enter and here I got my employees table.
Now for the orders.
Let's check the orders and let's just see.
Okay.
It looks like here that currently the data has not been transformed correctly for whatever reason,
because you can see here this ABC one, two, three.
This is ABC one, two, three.
So obviously the data type has not been assigned correctly.
I don't know why this happens.
This actually most of the time it works properly, but that's actually fix this.
Let's just see what we can do.
So there is one option.
So let me actually do the following.
Let's select everything we have.
So Ctrl A, and then there's an option to detect data type.
So let's actually click this and now we can see that the change type step has been applied.
And also here are data types.
Now of course we still need to check whether these are correct, but this already helps instead of doing
this manually step by step.
So let's just check.
Order ID is a whole number.
That is fine.
And this is text.
That's an ID employee ID important.
This is also a whole number which is the same as employees table.
That is fine.
Then we have ordered date.
It's a date.
Ship date is currently an issue.
Ship date obviously is not considered as a date currently.
So this is something we need to fix.
So let's try that right click left, click here and right click also would work, by the way, because
there's also change type option here.
But let's left click here and then let's just see if we transform it into a date.
Please note it's not a date time because there is no time attached to that.
But if we try to transform it to a date, click this option.
So now it asks me, do I want to change here the current setup or replace the current step or add a
new step?
I always like to go with the new step because this makes sure that even though or even if my replacement
runs into an error, I can easily remove it and do not remove or have to remove the whole change type
from this step here.
So let me click on Add New Step and you see that exactly what I was assuming it ran into an error.
So if you have taken the first master class course beginner to advanced, then you now know how to fix
it.
But if not, no worries.
The problem here is that it is not.
It's not able to transform the number directly into a date.
So we need to have some kind of bridge, if you want to call it this way.
So some kind of intermediate step.
So for that, let me just remove this change type and then let's first convert this into text because
converting something into text regarding the data type actually always works.
So let's click on this text.
And again, I will add a new step like that.
Okay.
And this does not throw any errors.
So the bar here is still green.
And I can see here now the values left aligned, which tells me this is text.
Next, I want to convert this from text into a date, so converting it from number to text is the step
or intermediate step we do, and then we convert it.
We convert it into a date from the data type text.
So let's do that.
Let's actually click on date.
And again, I don't want to replace the current step.
I want to add a new step.
So click on New Step.
And now you see that this worked exactly as we want it to work.
So now we have also the little calendar icon and we have here the date type.
So far, so good.
So let's scroll further to the right.
What else do we have?
Ship mode.
This is fine.
The data type seems to be correct.
Selling price, amount, decimal numbers, whole numbers.
That all looks fine to me.
So let's scroll further to the right and let's just see.
Okay, so what I can or what I realized the supplier ID that's important because that's the column we
want to link to the supplier table.
And this is a whole number that is already a good sign.
And what else do we have?
The region is also fine.
Customer zip code.
There is a few data missing.
You can see here there are 29 entries which are missing.
However, in this case it is not that important to us because we will not use this column.
That is why, regarding best practices, you could actually right click here and say, I want to remove
this column because if you do not use a column at all in the data model, there is no need to keep it.
And this of course is best practice because then you shrink the size of the whole data model.
In this specific case.
So in this use case we have here, it does not really matter because our model is really small.
And if you have a tiny model, it does not really matter whether you delete columns you don't need or
whether you want to keep them.
So we keep it inside now.
But just to mention this, if you have a big model and work with large enterprise models later on,
always make sure you remove everything you don't need.
Okay?
So like that, we just keep this.
That is fine.
And also, we agree that the missing values do not really bother us in this specific use case.
However, as I said, if you have this in the company environment and it is important where the customer
lives because you need to send something to his or her address, then of course make sure that you go
to the people who are in charge here, for instance, your sales department or anything like that and
ask to fill this missing values.
So what else do we have?
Well, let me just check If I go to my supplier table, I see that the data type is one, two, three.
So that is fine.
But I can also see that the country here is USA and because country here is USA, I like to actually
also have in my orders table the country name as USA.
Because here it is actually specified, as in this case, if I go to my countries here, let's just
check that.
And there actually is a us, right?
So either I need to keep it us or I need to transform it into USA.
Now, having said that, US is probably more common.
And actually if I go to the employees table, there's also specified us.
So maybe it's even better to fix the USA in the suppliers table and transform it to us.
So are we going to do this?
So let's click on the country here and say, we would like to replace values.
And this can be done either by transform under transform the replace values option or you right click
in the country.
And there's also replace values option.
So if you go in here, replace values, then you can actually go in here and just say the values defined
in this case USA.
So type in USA and you want to replace it with, in this case, us.
And then let's do that.
Let's click okay.
And now you see that all the US names in this case the suppliers from the US or USA in this case have
been replaced with us.
So that's the way how you can replace values.
The other way, which you might be familiar from Excel, does not work.
So you cannot simply click in one cell, double click here and edit this.
This doesn't work in power BI, but the replace values gives you some options to transform this.
And of course you could also write additional transformations if you go to Add column and you can create,
for instance here custom code.
If you want to sort something that's also possible or use the great column from Examples feature, which
you also checked out in a different course.
So that's also available in here.
Okay, so we got this as well.
And the funny thing here is actually this is the supplier, CBL, so I call it supplier supplier TBL
Enter.
And I should also actually use this and say employee TBL so M employee, employee TBL call it this way.
And then finally the orders table.
Go back to the orders.
I would like name to name this orders TBL so orders TBL press enter.
And now I've also renamed the orders so quickly check the orders one more time.
If I scroll to the right, there's something else I wanted to change.
Let me just see that.
What I can see the regions, the customer country now has been fixed and the only thing I think, which
I also spotted is inside the customer country.
There is actually currently a wrong entry, which is zero.
So let's also fix this.
And the way we want to fix it in our case is that the country zero.
Of course, in a real world scenario, you need to ask what this is.
But in this case, the zero belongs to us.
That is why I can go right click in the customer country one more time, replace values.
So here it is, and then just search for the zero entry and replace it here with us.
And now let's click okay and we are good to go.
And if you take a look at the filtering option, there is no zero anymore.
If you see a hint like that, always click on load more to see all the values, but you can still see
there is no zero.
Okay, so we got this fixed as well.
So let's click okay.
And now we actually have our three tables.
The final table we need.
If you're familiar with that, is, as I always recommend, a proper date table, because whenever you
want to do any kind of time intelligence in power BI.
So using the date add function, same period last year function and all the other kinds of time intelligence
functions which are available in power.
BI.
Then you want to make sure that you do them based on a proper date table and a proper date table itself
is actually quite easy because all it is, is a table which covers the whole time frame you're going
to analyze in your model, but without any missing values.
So a proper dates table would actually already be enough.
If you have just one column with dates and each day is unique and it simply covers, in this case,
three years, which we have in this dataset.
So starting in 2021 until the end of 2023, that would be enough.
But if you want to maybe add, give more and more, add more information to the dates table, this is
where this helper table comes into play and I have it in a resource section of this course.
You can download it, which is here, let me just take this the wrong which is here, and that's called
date table code.
So let me just open this.
And this is the code actually.
So let me just maybe add it in a different window.
Let me show it to you.
So if you don't know this already, go to the editor here.
Open it.
And that's what the code looks like.
So it's quite a lot to digest.
I get that.
But actually all it is, is simply a m code, which is used in the power query editor, and it creates
a few additional columns for our date table, like what kind of weekday it is, what's the name of the
month, and so on.
And it is a function.
So it allows us to control Z to copy it and then control V to paste it in here by simply going to home
and then say new source, add a blank query.
And then we just need to go to the advanced editor here and paste our query.
So remove the template which is inside.
Paste the query from the code and you should see no syntax errors.
As soon as you can see that, click on Done.
And now you have here the function for the date table.
So for the starting date here, we can start with the 1st of January in 2021 because my current date
range here in the orders table is from 2021 to the end of 2023.
So the end date will be the 31st in this case of December in 2023.
The financial year start month is one because we do not have a different financial year holidays table.
We don't have one, so there's nothing to choose from.
If you have that, you could do it.
But of course you need to provide this table.
So then we need to invoke the query, click on Invoke and you see that now we have our dates table.
So including the most important column, which is this first one which contains our unique list of dates
for this date range, and then of course, all the additional columns.
So let's rename this properly and call it dates TBL.
So to be consistent here, of course you can also drag this up if you want.
And now we could create right click here and create in this case, groups.
So different kinds of folders to add our tables.
But in this case there is not really much to process for staging and transformation and so on.
So that's why I will just keep it like that.
So this is it actually, for creating the tables.
And as soon as we're done with that, we could now click on close and apply and load the data in Power
BI desktop.
So click on Close and apply and then we just wait until the loading happens.
And then we should see the tables just in a few seconds and there they are.
So here are our tables.
And now we can of course start with our reports.
But one very, very, very important thing we need to do first is creating the relationships for our
data model.
So setting up the data model correctly.
To do this, we need to go to the model view here, click on Model View.
And then let's just let me just zoom out a little bit.
We can see that we got our effects table so that the table we want to analyze in here, which is the
orders and we got our dimension tables here for filtering, which is suppliers, table and employee
table.
And those two have already been mapped correctly.
Have they?
Well, let's check that.
If we hover over the relationship, we can see employee ID to employee ID this relationship.
And it's also a one to star relationship, So one to many.
And it also has a filter direction from the one side to the many side.
So exactly what the perfect structure looks like.
And the same is true for the pliers.
Hover over this and let's just check.
I need to maybe scroll down a little bit.
Where is the supplier ID?
So supplier D at the end here.
So let's just check, hover over it and you see that the two columns suppliers are highlighted.
So hopefully you can see that if I hover over that like that, you see that these are highlighted.
So obviously the relationship also correct.
And please note I'd like to spot this out.
Supplier ID here is written with a dash here.
Supplier D is written together.
So this is something is still was able to to figure out the data table connection is currently missing.
But this is actually expected because here it's called date.
And if I take a look at my orders table, I have a ship date here and I also have another date which
I want to use.
Here is the orders date and the order date.
Of course, the name is completely different from date, so the Power BI was not able to figure out
that this belongs together.
But that's not an issue because we can simply place it somewhere and then we can simply drag and drop
drag date on order date to create relationship.
And we see that it's also a one to many, so one to star relationship.
So this is actually also fine now.
Of course, you can drag the tables around and place them differently.
I personally like to have this flow that the dimension tables, so the tables I'm going to use to slice
and dice my data are on top and the fact table is at the bottom.
But this is just a personal preference.
You can do that a different way if you want.
So that's actually it for setting up the data model.
And now we could go into the report view and get started with Power BI, with Dax, with visualization
and all the cool things we can do in Power BI.
So that's it for this video.
Hopefully we were able to follow along.
If not, then please now open Power BI and do the model setup yourself."