- Getting Started.
- Importing the data.
- Data Preparation.
Getting Started
For this blog, we will disable the auto detection of relationships by Power BI. This is done to create an environment where you can dive deeply into model creation. Disabling relationship detection is done purely to enhance concept building and is not preferred in ideal conditions. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/Importing Data
The Importing Data phase of a data modeling involved procuring the right tables that can answer the business question at hand. To Gathered Data for Data Model contains:- Product Sheet: It consists of three columns addressing the product details, i.e., Product Code, Product and Product Type. Each Product has a distinct Product Code and falls under only one Product Type.
- Product Line Prod Type Sheet: Each Product Type is a subcategory of Product Line. This sheet records the subcategory to category mapping.
- Product List Sheet: Product list contains the details of each Product and the list price associated per unit.
- Sales Person Per Product Type Sheet.: This sheet contains details of Sales Person associated with the Sales. There exists a many to many relationships between the sales person and the product.
- Sales Sheet: Sales sheet contains transactional Data for the Sales.
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/
Data Preparation
Data Preparation involves ensuring that the data is capable of delivering value.
Data Preparation involves:
- Checking the data types and data categories.
- Making sure that all the required columns are present in data set.
Power BI guesses the data type of the column based on top 1000 Rows.
For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne-2/
In this blog post, we’ll cover the topic of creating date table.
Creating Date Table
Date is an important dimension table in any data model. Date Table can be generated in Power BI using either the CALENDER() function or the CALENDERAUTO() function.
Both of these functions generate a single date column table. However, CALENDER(,) requires the starting and ending dates as the arguments to the function. Whereas, CALENDERAUTO() automatically detects the first and last date in the dataset and generates a date table accordingly.
Continue using the Previous file for this exercise
To Create a Date Table:
1.Click on Modeling Tab in the Ribbon.
2.Click on New Table.
3. In the Formula bar type the following DAX expression.
4.Click on New Column in the Modeling Tab and Type the following expression in the formula bar.
5.Click again on New column in the Modeling Tab and type following DAX in the Formula Bar for Month.
Now let’s create a column for month name.
6.Click again on New column in the Modeling Tab and type following DAX in the Formula Bar for Month.
7.Finally, click on the Date table in the Fields Pan.
8.Click Mark as Date Table.
9. Choose date from the Date column drop-down
10. Click OK.
Conclusion
Data Modeling is a part of our every Power BI training in Australia. In Power BI Basic course, only the basic concepts related to data modeling are covered whereas Power BI Advanced course is more focused towards data modeling and data transformation. Therefore, understanding what a Data Model is entirely important for both beginners and intermediate level users. This blog is dedicated to understanding the creation of date table in a data model.
Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.
More Checkouts for Power BI Training in Australia.
