00:00Hey friends, welcome back.
00:07Now I really hope you had a little bit more time to dive deeper into building your first
00:12report and try out different visualization types, formatting options, and so on.
00:16Now, if you're done with this, just make sure that you click the save disc here again.
00:20And also if you close the report without saving and Power BI would just create a pop-up window
00:26and ask you whether we want to save the data.
00:28And for now, I'd like to do this, let me just click on the save option here and then close
00:32it because now we would like to dive deeper into a more advanced data set.
00:37Because remember, so far, what we did was just importing one simple Excel file, which
00:42was this small table and create our report.
00:46But now of course, we would like to know and learn how we can use a more advanced data
00:51set where we need to combine tables together, create relationships, and so on.
00:56And in this video here, I just would like to show you what kind of data we deal with
01:00the next couple of videos.
01:02So let me just open this one.
01:03And that is the Excel file called PBI, so Power BI Training 1.
01:09And you should also find it in the resource section to follow along with me.
01:13So what you could see here is in this Excel file, we have three tabs.
01:18So we have a sales tab for 2023, and I mean three sales tabs.
01:22We have a sales tab for 2022 sales and for 2021 sales.
01:28What I'd like to highlight here is the structure of the table is exactly the same, right?
01:34So we have the same column names, which is important for setup, but we have the sales
01:39data in three different tables, right?
01:42For each of the years separately.
01:44And of course, what we want to accomplish in Power BI is getting this into one big table,
01:50which we can then analyze, slice and dice, and figure out, for instance, different kind
01:54of sales numbers across various products, for instance.
01:58But to do this, we need to combine these three tables.
02:01Also, we can see that for the product, for instance, we have a product ID in this table.
02:08And for the location, for instance, we have a location ID.
02:13Or for the salespeople, we have a salespeople ID, we have a customer ID, we have an order
02:17ID, right?
02:18So we have these RID numbers, but they don't give us much information because so far, okay,
02:23I can see this is product PID 2029.
02:27But what kind of product is it, right?
02:29From this table, we can't see it.
02:31And also for the other sales tables.
02:33Now this is a common structure, which you will find when we are talking about fact and
02:37dimension tables in, well, in data models normally, right?
02:41And also in databases.
02:43So we have a kind of key here that is actually the right word for it.
02:47We have a key, and we can look up this key in different tables.
02:51So for instance, to figure out what about this transaction here, right, we have a quantity
02:55of two, a price of 1522.
02:59So a total sales of two times this value here.
03:02This was generated by this product in this location by this salesperson to this customer.
03:10And we can look this up because we have a product table here as well, products.
03:14And here we have the product name, right?
03:16These are the product names.
03:17And we have additional information, like for instance, what is the cost of the product?
03:22What was the original sale price?
03:24What is the discount?
03:25What's the current price?
03:26What are the taxes?
03:27All the information for the product are listed here.
03:31And we can get this information for the product as well as the name and add it to the sales
03:36table by creating our relationships.
03:38And when I say adding those values, I do not mean adding additional columns here directly,
03:44but combining the tables with relationships so that the Power BI model knows, for instance,
03:50for the product ID to look the data up in the products table in this one.
03:56And the same is true for locations.
03:58Here we have the locations, which we're going to deal with.
04:00They also have the location ID.
04:02This is the common field we have to our sales table where we all have a location ID.
04:07And that's how we can combine those tables with relationships to make sure that we can
04:11look up exactly the right information for each of the transactions.
04:16And hopefully it's clear how that works, because for customers, for instance, and also for
04:20salespeople, that's exactly the same.
04:22We have the ID, and this is our lookup table, more or less, where we can then search, okay,
04:27based on the salesperson ID, which we find in sales transactions.
04:31We know now, for instance, that Kevin was the guy who sold the product to our customers.
04:37And that's it actually for exploring this data set.
04:41So hopefully it is clear what the data looks like.
04:43And of course, feel free to open it and have a look at it yourself.
04:47But in the next couple of videos, we're going to dive deeper into importing the data in
04:51Power BI, transform it, slice and dice it the way we need it in the query editor.
04:56So we basically prepare the data, and then we can use it in order to create our report.
05:01So hopefully you're excited, as I am, and I can't wait to see you in the next video.
05:06Until then, best guys.
Comments