Designing Data Model in Power Query Simplified 101

You can connect to the world of data with Power BI, generate engaging and interactive reports, share your work with others, and enhance their business intelligence initiatives. Connecting to sources, and shaping and transforming data to meet your needs is made easier with Power Query.

This article gives an introduction to Power BI and Data Model in Power Query, and how to use Power Query in Power BI.

Table of Contents

• What is Power BI?
  • Key Features of Power BI
• What is a Data Model?
• What is Data Modeling?
• Power BI Data Model in Power Query
• What is a Power Query?
• Understanding Power Query in Power BI
  • Using Power Query in Power BI
  • Saving the Work Done
• Conclusion

What is Power BI?

Power BI is a set of software services, apps, and connectors that combine to transform unstructured data into logical, visually immersive, and interactive insights. Your data could be in the form of an Excel spreadsheet or a hybrid Data Warehouse that is Cloud-based and on-premises. Use Power BI to connect to your Data Sources, Visualize and uncover what matters, and share your findings with everyone you choose.

Users can do the following tasks in Power BI Desktop:

• Connect to the information.
• The data is transformed and modeled.
• Make graphs and charts.
• Create Visual Collections for reports and dashboards.
• Using the Power BI service, you may share reports with others.

Key Features of Power BI

• Power BI offers an Interactive Desktop: You may use this interactive Power BI desktop solution to quickly access data and create reports. It is not required to have advanced skills to create a report this sophisticated too. It is simple to comprehend and apply. This tool’s main feature is that it’s free to download and use, allowing you to create reports without any technical knowledge.
• Customize Your Visualization: Every organization is different in terms of how it functions and achieves its objectives. There are times when taking the traditional route is the best option. This is also true for visualization. Because of the complexity of the data, the Power BI application gives a default standard that is frequently inadequate for businesses. Companies may easily use the custom visualization library to develop a visualization that matches their needs in these situations. 
• Visibility: Data is at the center of any organization, and the primary challenge that companies face today is combining data from many sources to provide actionable insights. Gathering different datasets and visually organizing them for improved understanding is one useful strategy. This form facilitates the supply of greater in-depth data understanding, giving firms a competitive advantage over their competitors.
• Data Sources: The Get Data function in Power BI allows users to select from a range of data sources. On-premises or in the cloud, unstructured or structured data sources are all possibilities. A fresh data source is added every month. Excel, Power BI Datasets, Power BI Dataflows, and other Data Sources are examples.
• Filtering Datasets: You can filter datasets to generate smaller subsets that only contain the most important facts and context. Power BI has data connectors for Excel, SQL databases, Oracle, Azure, Facebook, Salesforce, and MailChimp, to name a few. Users can easily connect to these data sources and create datasets by importing information from one or more of them.
• Data Analysis Expressions (DAX) Functions: Power BI’s DAX functions are Data Analysis Expressions. These analysis functions are predefined programs that can be used to execute analytics-specific data tasks. Over 200 functions are currently available in the Power BI function library. For example, MIN, MAX, COUNTDISTINCT, COUNT, etc. The creators are still adding new ones.

What is a Data Model?

Data Models are graphic representations of an organization’s data items and their relationships. Models aid the development of effective information systems by assisting in the definition and structuring of data in the context of key business operations. They allow business and technical personnel to work together to determine how data will be kept, accessed, shared, updated, and utilized within a company. There are 4 types of Data Models:

• Relational Data Model: This model arranges data in a table in the form of rows and columns. Tables are used to represent data and in-between relationships in a relational paradigm. Tables are also known as relationships.
• Entity-Relationship Model: An ER model is a logical representation of data as objects and the relationships that exist between them. Entities are the objects in question, while relationships are the connections between them.
• Object-based Data Model: A variation on the ER model that includes concepts such as functions, encapsulation, and object identification. A sophisticated type system, including structured and collection types, is supported by this paradigm.
• Semistructured Data Model: Unlike the other three data models, this one is semistructured. Where distinct data objects of the same type may have various attribute sets, the semistructured data model allows for data specifications.

What is Data Modeling?

The process of constructing a data model is known as Data Modeling. These Data Models are abstract data that specify the structure, attributes, and relationships of the data. Multiple entities or values for a specific scenario must be related to each other to establish a Data Model. A Data Model can be classified into several types based on its structure and features. Hierarchical Model, Relational Model, Network Model, Entity-Relationship Model, and others are examples of Data Models. You will be learning about the Entity-Relationship Model in Power BI.

Power BI Data Model in Power Query

To develop Data Models in Power Query the data you are going to use is simple electronic store sale records. The Shop Sales data covers customer, country, product, cost, month, and year factors.

• Power BI Data Models
• Data Model in Power Query Steps: Creating and Managing a Relationship
• Data Model in Power Query Steps: Calculate and Measure the Data
  • Data Model in Power Query Steps: Create a Table
  • Data Model in Power Query Steps: Create a Column
• Data Model in Power Query Steps: Create a Visualization

Power BI Data Models

Power BI detects all possible relationships between different types of data automatically. You sometimes need to manually construct a relationship between the data.

After the data import is finished, go to the ‘Models‘ tab on the left side, as seen in the figure above. All of the lines displayed here in the ‘Models‘ tab depict the cardinality and direction of the relation from one table to another, and they were built by Power BI. Using the Manage Relationship item on the top menu, as noted in the image, you may build and edit the default relationship created by Power BI.

Data Model in Power Query Steps: Creating and Managing a Relationship

The first step in Data Model in Power Query Steps is to Create and manage a Relationship. After clicking on ‘Manage Relationship’ a screen similar to the one shown above will appear. From one table to the next, you may observe all the active relations. The following is a list of all the things you can do with these relationships:

• New: This option will assist you in creating a new table relationship.
• Autodetect: With this option, Power BI finds the link between data in tables automatically.
• Edit:  Using this option, you may make changes to your data relationship.
• Delete: Power BI can remove the selected table-to-table relationship with this option.

The next step in Data Model in Power Query is to click on ‘New‘ or ‘Edit‘. A screen similar to the one seen above will appear in front of you. Each choice is explained one by one below:

• The first drop-down menu lets you choose the table from which you wish to make a relationship.
• The second drop-down menu selection will build a link between the first and second tables.
• You’ll choose the Cardinality relation here, but you can’t have Power BI pick a cardinality that doesn’t exist.
• You can choose between ‘Single‘ and ‘Both‘ for the relationship’s direction.

Data Model in Power Query Steps: Calculate and Measure the Data

The next step in Data Model in Power Query is to calculate and measure data. DAX is a set of instructions for calculating data from tables, similar to programming. Addition, multiplication, average, percentage, and other commands with various filters are included in these phrases.

Consider the following scenario:

• Table 1 = DISTINCT(Table 2[Column 1]); this phrase will fill all of the unique values in Table 1 with values from Table 2’s Column 1.
• Column1 = RIGHT(Table1[Column Name],3); this statement fills Column 1 with the last three characters from Column Name.

You can make some computations using the data from your shop in Power BI. As shown in the image below, select the ‘Data‘ tab from the left menu. You’ll find several tools to help you calculate your data here. These will be used in Power BI.

Data Model in Power Query Steps: Create a Table

The next step in Data Model in Power Query is to create a table. After selecting ‘New Table‘ you must type the DAX expression displayed in the preceding image.

• The table’s name is defined by the first portion of the expression.
• The second is the filter; the ‘DISTINCT‘ function will only choose the column’s unique values.
• Inside the ‘DISTINCT‘ function, you must pass the parameters, which are the place from which you will extract our data. So far, you’ve passed the table and column names containing your country codes. Click ‘Enter‘ when you’re through with your expression.
• You’ll obtain your new column with the default name and results when you apply the phrase. You can rename a column by double-clicking on it.

Data Model in Power Query Steps: Create a Column

The next step in Data Model in Power Query is to create a column. To create a calculated column, go to the top menu and select ‘New Column‘.

• With the ‘Country‘ filter, the DAX expression will calculate all revenue from the table ‘Revenue.’ You might have spent hours calculating the individual money earned by the country if it hadn’t been for this expression.
• You obtain this as a consequence of the phrase in Data Model in power Query.

Although Power BI recommends writing an expression, remembering all of the expressions may be tough. You can utilize the Quick Measure tool if this is the case. To calculate, you only need to enter the parameters and function. This program will then generate the expression for you based on your choices. When you need to make rapid calculations for your reports in Data Model in Power Query, these measurement tools come in handy.

Data Model in Power Query Steps: Create a Visualization

The next step in Data Model in Power Query is to create a Visualization. In your imported data, you only have sales, sales by country, and product prices. You constructed a separate table containing the money generated by each country using the procedures described above. Using several visualizations, you have plotted the country’s revenue on a world map. Power BI allows you to construct and manage Data Model in Power Query similarly.

What is Power Query?

Power Query is an engine for Data Transformation and Preparation. Power Query has a graphical interface for obtaining data from sources as well as a Power Query Editor for performing transformations. It can be used for Data Model in Power Query. Because the engine is utilized in so many products and services, the data’s final destination is determined by where Power Query was employed. You may execute data extract, transform, and load (ETL) processing with Power Query.

Understanding Power Query in Power BI

Power Query Editor is included with Power BI Desktop. The Power Query Editor allows you to connect to one or more Data Sources, as well as shape and change the data. You might tailor the data to your needs, make it more usable, and then import the model into Power BI Desktop. You can create Data Model in Power Query.

Select the Edit Queries option from the Home tab of Power BI Desktop to access the Query Editor. The Query Editor appears drab without any data connections, as it should, as a blank pane.

When a query is loaded, however, this Editor view becomes significantly more intriguing. When you connect to a Data Source, the Query Editor loads information about the data, which you can shape before using it to Data Model in Power Query.

Using Power Query in Power BI

As a result, when you get to the query editor, you won’t have any data connections. Data Model in Power Query in Power BI can be done.

The Query Editor loads information about the data. Then you may start shaping and transforming it to perform Data model in Power Query.

The Query Editor should look something like this once a data connection has been established:

• Many buttons on the ribbon are now active, allowing you to interact with the query’s data.
• Queries are listed in the left pane and can be selected, viewed, and shaped.
• The data from the selected query is presented in the center pane and can be shaped.
• The Query Settings window appears, displaying the query’s properties and steps that have been applied.

Saving the Work Done

You can have the Editor apply the changes to the data model after your query is where you want it.

• Step 1: To apply changes in Data Model in power Query, go to the File menu in Power Query Editor and select Close & Apply.

Power BI Desktop includes a dialogue to display the state of progress as it is made. Once you’ve created your query, save it. Power BI Desktop can save your work as a .pbix file.

• Step 2: To save your work done in Data Model in Power Query, go to File > Save As or File > Save if you’re not saving it for the first time.

Conclusion

You learned about the Data Model in Power Query functionality, Power BI and how to use them. While learning how to connect to data sources and save your query, you also got a quick overview of how to utilize the Query Editor in Power BI Desktop.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 100+ multiple sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!