Create a data model in Power BI

Posted by By Giulio Stefanica 6 Min Read 371 Views

In this short tutorial I will go through the process of getting data into Power BI and creating a data model in six simple steps. I make use of a demo data Excel file of a company selling goods in various locations in Denmark.

Step 1: Get data from Excel

There are three main ways to import data in Power BI Desktop.

Get Excel Power BI SQL Enter data workbook datasets Server data Common data sources Excel workbook Power 81 datasets Power 81 dataflows Data verse SQL Server Analysis Services Text/CSV OData feed Blank query Power 81 Template Apps Dataverse Recent sources Transform data Refres h New Text visual box More visuals New Quick Sensitivity (preview) v Publish Add data to your report Once loaded, your data will appear in the Fields pane. Import data from Excel Import data from SQL Server Paste data into a blank table Try a sample dataset Get data from another source -4

The first is by clicking on the Import data from the Excel button in the middle of the canvas. The second is by clicking on the Get data button on the ribbon or by clicking on the Excel workbook button right beside it. The third is by clicking on the Transform data button in the Queries group on the ribbon which will open the Power Query Editor.

In the Power Query Editor, on the top left, there is the New Source button and by clicking on it, a dropdown menu will appear with the most common data sources including Excel.

Home New Source • Transform Add Column Data source settings Data Sources View Tools Help Recent Sources • Enter Data Manage Parameters • Parameters Refresh Preview Properties Advanced Editor • Manage • Query Choose Remove Columns• Columns • Manage Columns Keep Remove Rows • Rows • Reduce Rows Split Column • Sort Group Most Common Data Type: • use First Row as Headers • Replace Values Transform Merge Queries • Append Queries • Combine Files Combine Text Analytics @ Vision Azure Machine Learning Al Insights i Excel Workbook SQL server Analysis Services Text,'csv Web OData feed Blank Query More...

Step 2:  Select the right Worksheet or Table

x Navigator Display Options • DK Retailer.xlsx [17] audget_TabIe Customer Table Inventory_TabIe Locations Tabe Municipalty_TabIe Product Table Sales Table Salesperson_TabIe Budget Customers Inventory Locations Municipality P Salesperson Salesperson ID 91017 Table Salesperson Name Akeem Rivas Blythe Atkinson Bruce Greer 8 u ffv Cortez Charles Hudson Chelsea Perez Cody Sharpe Desirae Weiss F Iynn Lyons Gil Mack Gretchen Weber Hedwig Cooke Holly Branch Isaac Shepherd Jade Roth Jessamine Lewis Julie Webster Kaye May Kennan Norman Lacey Macdonald Lionel Galloway Madaline Garrett Marcia Parks Mariam Clayton

After clicking one of the three buttons outlined above, the Navigator window will open. Here it’s possible to bring in the whole worksheet or separate tables. As a best practice, it is always better to format the data as tables before importing it into Power BI especially when working from an Excel file.

The way to differentiate between worksheets and tables is by checking the icons: blue for tables and white for worksheets. In this case I also renamed the tables in Excel by adding an underscore and the word “table”. I go ahead and put a check mark on all the tables that I want to import and click on Transform Data.

Step 3: Power Query Editor and basic Data Transformations

X ad 、 1 pa6ueu3 x 5d315 0 当 Idd 《 、 、 」 山 っ 0 524 】 2d0 」 d - イ 5 当 1 亠 dOHd 、 ( 宀 宀 X24 2 は 、 : 2 = N 」 2E045nD : ) よ X24 2 は 、 '„OI 「 ロ 4 い n) : ) ) 、 21q21 21q21 04 Ⅵ n) ) Ⅵ ad 、 Lu 「 '-ITO 凵 「 00 十 Ⅵ ueJL ・ ト 朝 n け - 「 コ 一 08 」 一 p 一 08 あ Luo 砌 2 、 2 2 も 02 0-2 に 一 O 凵 S U!LUZe 「 、 」 」 2q22d 0E22 2 、 uunOS = 」 20 」 ゝ 22S 2 一 W を 2 、 ′ eu 一 」 ON 2n1 一 aqeu 、 2 を 2 エ 一 」 」 21 」 25 鬲 uÅlO 「 ・ ま O 朝 当 け - 「 コ 一 08 一 p 一 'qdLLE ・ 00013 【 10013 国 ZOOID E t0013 S 90013 、 ZOOID 8 80013 5 60013 OL 01013 【 【 11013 【 ZIOID EL GOOD マ SOCIO 9 寸 【 2 一 qe ト ー u05 あ d52 一 es ロ 2 一 qe ト 」 2E04 当 【 凵 2q2 ト 5u042 い 0 コ 【 凵 型 qe ト も コ て 0 」 d ロ 型 q21 52 一 ロ 心 山 っ 0

In the left pane of the Power Query Editor under Queries, I can see the five tables I just imported. On the right pane under APPLIED STEPS, I can see the different data transformations steps already applied to a particular table. One of the most important initial data transformations to be made is ensuring that the correct data type is set for each column.

Starting with the Customers table, I go ahead and click on it to see the columns it is comprised of. To change the data type I can click on the icon in the column header as shown below.

z; DK Retailer- start- Power Query E&ltor Tools Help O Data Type: Text • use First Row as Headers • Close & Apply • Close Home New Source • Transform Add Column Data source settings Data Sources View Recent Sources • Enter Data Manage Parameters • Parameters 0. Refresh Preview • Properties Advanced Editor Manage • Query Choose Remove Columns • Columns • Manage Columns Keep Remove Rows • Rows • Reduce Rows Split Column • New Query Sort Group 1 , 2 Replace Values Transform type text}, {"Customer Merge Queries • Append Queries • Combine Files Combine type text}}) Text Analytics @ Vision Azure Machine Learning Al Insights Queries [S) Customer Table Locations Tabe Product Table Sales Table Salesperson_TabIe Query Settings PROPERTIES Name Customer All Properties APPLIED STEPS Source Navigation X Changed Type x - Table. {f • ABC Customer Name "Customer ID", Name" , 10 1.2 Decimal Number Fixed decimal number 123 Whole Number Percentage Date/Time Date Time Duration Tex t True/FaIse Binary Using Locale... cloog 100 % ty tinct. 801 unique Luet'/ dberrv ewour rdey Gavaghan Cham Morissa Adamo

Another way of changing the data type is by navigating to the ribbon and in the Transform section, click on the Data Type button and choose the appropriate data type for each column.

z; DK Retailer -start- Power Query E&ltor Tools Help Close & Apply • Close Home New Source • Transform Recent Sources • Enter Data Add Column Data source settings Data Sources View Manage Parameters • Parameters Refresh Preview • New Query Properties Advanced Editor Manage • Query Choose Remove Columns • Columns • Manage Columns Keep Remove Rows • Rows • Reduce Rows Split Column • Sort Group type Data Type: Text • Decimal Number Fixed decimal number Whole Number Percentage Date/Time Date Time Date/Time,'Timezone Duration True/FaIse Binary Merge Queries • Append Queries • Combine Files Combine text}}) Text Analytics @ Vision Azure Machine Learning Al Insights Queries [S) Customer Table Locations Tabe Product Table Sales Table Salesperson_TabIe Query Settings PROPERTIES Name Customer All Properties APPLIED STEPS Source Navigation X Changed Type x - Table. {f • ABC Customer Name "Customer ID", 0. 4 10 11 12 13 14 15 16 17 • Valid • Error • Empty go I distinct. go I unique Clooo CIOOI C1002 Cloos C1004 C1005 C1006 C1007 C1008 cloog CIOIO CIOII C1012 C1013 C1014 C1015 C1016 100 % • Valid • Error • Empty 100 % Column statistics Count Error E m pty Distinct Unique Empty string go I distinct. go I unique Ana bel Luet'/ Ailsun Padberrv Jolvn Sivier Rusty Brver Amie Seeviour Terri Hardey Rossie Gellert Maddie Gavaghan Sammie Cham Morissa Adamo Norina 'Wyeth Darline Souten Jazmin Scholfield Sigmund MacPake Thurstan Legon Dinny Trenear Sherve Heinrici Value distribution CIOIO CIOOO C1800 C1012 C1013 C1014

Step 4: Arranging queries

It is a best practice to group the queries in distinct folders in the Queries pane. I usually group them by Fact tables and Dimension tables, Supporting tables etc.

To create a group simply select the tables by holding the Ctrl key and then right click and choose New Group.

Queries [S) Customer Table Locations Tabe Product Table Sales Table Salesperson_TabIe New Group Name Dimension Tables Description These are the dimension tables = Table. {f "Salesperson ID", type text}, {"Salesperson Name", type text}}) 16 BC Salesperson ID • Valid • Error • Empty distinct, unique x Cancel 91021 • ABC Salesperson Name 100 % • Valid • Error • Empty distinct, LS unique Akeem Rivas Blythe Atkinson Bruce Greer Buff,' Cortez Charles Hudson Chelsea Perez Cod'/ Sharpe Desirae Weiss F lynn Lyons Gil Mack Gretchen Weber Hedwig Cooke Holly Branch Isaac Shepherd Jade Roth Jessamine Lewis 100 %
Queries [S) Dimension Tables 141 Customer_TabIe Locations_Tabe Product_TabIe Salesperson_TabIe Fact Tables [Il Sales_TabIe Other Queries

Step 5: Rename the tables

There is also a good idea to rename the tables as they get referenced when writing DAX formulas.

Power BI comes with IntelliSense which is very intuitive, and it is quite easy to identify the table to which the formula or function refers to. I find it helpful to give the tables a name that will immediately help me identify which table I am referencing. To rename a table, simply double click on the left pane under Queries and type in the appropriate name.

Step 6: Create a Data Model

Once all the above steps have been applied, click on the Close & Apply button in the top left corner of the ribbon. This will load the data and Power BI will automatically create relationships between the tables. However, it is always a good idea to check that the correct relationships have been made.

To navigate to the Model canvas click on the Model icon on the top left of the canvas. It is the last icon on the bottom. The first two are the Report and Data icons. 

In the Model canvas, we can already see the relationships between the tables that Power BI automatically created.

It is also possible to manually move and place the tables on the canvas. The most popular layouts are either by placing the fact table in the middle, in this case the Sales table and all the dimension tables around it or by placing the dimension tables on top and the fact table below. Both methods bare the name of a Star Schema whereby all the dimensions are connected directly on a one-to-many relationship to the fact table.

Last step is to Save this report locally on the computer and give it a name. In this case I name it DK Retailer – start.pbix.

Tags: