Project 2 - Supply and Demand Gap Analysis
- Explain Power BI Query Editor Components along with Home Tab.
A. Query Editor is the tool available with Power BI Desktop to transform the data, shape data structure to meet the needs of the users, and after transforming the data we can load back to the Power BI Desktop to start working with the transformed data.
Power Query Editor
To get to Power Query Editor, select Transform data from the Home tab of Power BI Desktop.
With no data connections, Power Query Editor appears as a blank pane, ready for data.
Once a query is loaded, Power Query Editor view becomes more interesting.
The query ribbon
The ribbon in Power Query Editor consists of four tabs: Home, Transform, Add Column, View, Tools, and Help.
The Home tab contains the common query tasks.
To connect to data and begin the query building process, select New Source. A menu appears, providing the most common data sources.
The Transform tab provides access to common data transformation tasks, such as:
- Adding or removing columns
- Changing data types
- Splitting columns
- Other data-driven tasks
For more information about transforming data, including examples, see Tutorial: Shape and combine data in Power BI Desktop.
The Add Column tab provides additional tasks associated with adding a column, formatting column data, and adding custom columns. The following image shows the Add Column tab.
The View tab on the ribbon is used to toggle whether certain panes or windows are displayed. It's also used to display the Advanced Editor. The following image shows the View tab.
It's useful to know that many of the tasks available from the ribbon are also available by right-clicking a column, or other data, in the center pane.
The left (Queries) pane
The left pane, or Queries pane, displays the number of active queries and the name of the query. When you select a query from the left pane, its data is displayed in the center pane, where you can shape and transform the data to meet your needs. The following image shows the left pane with a query.
The center (Data) pane
In the center pane, or Data pane, data from the selected query is displayed. This pane is where much of the work of the Query view is accomplished.
In the following image shows the Web data connection established earlier. The Overall score column is selected, and its header is right-clicked to show the available menu items. Notice that many of these right-click menu items are the same as buttons in the ribbon tabs.
When you select a right-click menu item (or a ribbon button), the query applies the step to the data. It also saves step as part of the query itself. The steps are recorded in the Query Settings pane in sequential order, as described in the next section.
The right (Query Settings) pane
The right pane, or Query Settings pane, is where all steps associated with a query are displayed. For example, in the following image, the Applied Steps section of the Query Settings pane reflects the fact that we just changed the type of the Overall score column.
As additional shaping steps are applied to the query, they're captured in the Applied Steps section.
It's important to know that the underlying data isn't changed. Rather, Power Query Editor adjusts and shapes its view of the data. It also shapes and adjusts the view of any interaction with the underlying data that occurs based on Power Query Editor's shaped and modified view of that data.
In the Query Settings pane, you can rename steps, delete steps, or reorder the steps as you see fit. To do so, right-click the step in the Applied Steps section, and choose from the menu that appears. All query steps are carried out in the order they appear in the Applied Steps pane.
- Explain Text, Number and Data Transformations
A. When working in a large set of Data, definitely we cannot use the data as it is. We need to perform some refinements so that we can exactly look into the data in which we are interested. In Power BI, this process is called Data Transformation, which can be done in different ways.
Transform Data
Let’s assume you have a text field like below with values that are email addresses. and you want to extract different parts of the email address, as the email and domain. To do this operation in Power Query, you can click on the Transform Data in the Power BI Desktop.
Open transform data from Power BI Desktop
This will open the Power Query Editor, which is the place to do data transformation in Power BI.
We are having Text, number and date transformation tools in the data transformation. Please find below screenshot of these three data transformation tools:
Text Transformation:
Text column is used to split the data by delimeter into multiple columns. We can format text into like uppercase, lower case, capitalize each word, we can trim the text, clean white spaces and can add prefix and suffix also to the existing column.
We can also extract the date based on lengths, first anf last characters, ranges and delimeter.
Number Transfomation tool:
In Number Transformation, we are having statistics to perform sum, mean, median, average, maximum and count values. It allows to evaluate basic stats for selected columns. Also we are having standard, Scientific and Trigonometry. which are used to allows us to apply standard operations (addition, substraction and etc) and advanced calculations to each value in the columns. Information is used to allow us to define the binary flags to each value in the column.
Date Transformation Tool:
Date transformation is used to extract the age, date, time, year/month,quater,week/day and hour/minuite,seconds and also earlier and latest from the columns.
- Adding Indexing Columns – Sort the month names
- Adding Conditional Columns - add a quarter column
- Grouping & Aggregating Data - Find out quarterly total students
- Un-pivot – transform month which are in columns to rows
- Pivot – transform attributes which are in rows to columns