Almost all of the testing projects require data to be tested/analyzed based on the requirement. Many of the testing applications require the testers to set up data, whether it might be simple or complex. This document helps to understand how a user can easily get different data Sources for Data Preparation/ Test data, modify the data as per the user’s needs (like removing a column, changing a datatype, or splitting a single column) and for Data analysis.

Power queries can act as an ETL tool and also can be used to build reports adhering to the business requirements. It is a part of the larger domain provided by Microsoft, i.e., Power BI. A power Query is a user-friendly way to play with the data.  Power Query is a free Excel add-in that allows the user to import data into excel from many data sources, allow data modification, refine data, and load to the data model. This applies to Excel 2016, Excel 2013, and Excel 2010.

With Power Query, user can:

• Connect to a variety of data sources.
• Can bring in the data from these sources and transform the data which matches the requirement or prep it for further analysis using tools like Power Pivot or Power view.
• Create custom views over the transformed data.
• Perform data cleansing/modelling/integration operations.
• Data can be imported from multiple log files.

This section will mainly focus on connecting to the ORACLE database and generating excel reports.

Power query can be downloaded from the following link . The link also has other requirements that specify the software requirements for installation.

1. After installation, navigate to the FILE menu. Click on OPTIONS to enable the use of add-in.
2. Now select the Add-Ins tab, which is located on the left side of the screen. Go to Manage Combo Box and then click COM Add-Ins. Finally, select the GO button.
3. When you have the dialog box on your screen, check the checkbox for “Power Query for Excel.” Once done, select the OK button to continue.

4. After doing the above steps, there will be a new Power Query tab present in MS Excel, as shown in the below image.

You will consider the ORACLE database as your source.

There are mainly three parts to it –

• Connect to the database
• Data Load
• Data Refresh

Connect to the database

Under the Power Query tab, click on FROM DATABASE icon as shown below:

Click on FROM ORACLE DATABASE. Provide the SERVER (SID/database name) and QUERY.

Then provide the credentials of the database you want to connect.

Data Load

The user can see the data in a popup and load the data into the desired excel sheet.

Data Refresh

Right-click on the workbook query and click EDIT.

A Query Editor window pops up. Click on the Data Source settings tab.

Then change the source to some other environment and edit the permissions by giving the credentials.

After successfully providing the credentials and connecting to the database, refresh the preview and then close the query editor and load the data to the main sheet.

The user can also refresh the data from a link button present in the workbook query window tab (right side of the screen near the black arrow).

Simple Installation

• Excel 2016 - Power Query is a built-in tab in Excel 2016.  It has been renamed to the Data tab.  It is known as “Get & Transform” in Excel 2016.
• Excel 2010 & 2013 – Power query has to be downloaded from the below link and installed.

Once the installation is completed, open excel, and the user will be able to access the Power Query tab.

Incase power query tab is missing, follow the below steps:

1)     Go to File > Options > Add-in.

2)     Select COM Add-ins from the Manage drop-down.

3)     Click the Go button.

4)     Check Microsoft Power Query for Excel checkbox and Click on OK.

5)     Now power query tab will appear on the screen.

ETL (Extract, Transform, and Load) is a process where data is extracted from various data sources and transformed in a proper format for analysis and querying and loaded into the destination.

Extract

Data Sources

Power query can transform both structured and unstructured data from various sources. Different sources include Excel, Web Page, XML file, Text File, Folder, HDFS, Facebook, and from different databases such as SQL, Oracle, DB2, MySQL, Sybase, Teradata, etc.

If you want to import data from the web, you need to enter the URL in the textbox, so that the user is navigated to the desired web page.

The contents will be loaded in the form of tables on the extreme right of the sheet under the Navigator section. If you hover over the tables specified under the navigator, details of the table, along with the column names, will be specified in the display box for a quick view to the user.

Due to access restrictions and for easier understanding, you shall import data from another Excel file. Say, you import data from Excel. Data imported from excel will be displayed under Navigation pane. Users can load multiple files by selecting the ‘Select multiple items’ checkbox and checking the desired files.

‘Load To option’ in the navigator pane allows viewing the data in the way the user wants. Clicking on ‘Load’ will load the data.

Transform

Query and Design tabs will get enabled to the user.

• Query tab is used for the transformation/shaping of data.
• The design tab helps in improving the look and feel.

Once data is loaded, it will get displayed on excel automatically. The count of the number of records loaded will be displayed under Workbook Queries.

Query Tab

Click on ‘Edit Query’ in the Query tab. Users can now modify/transform data without using the normal excel formulas and coding.

Operations in the Query tab

Below are the operations that can be performed in Edit Query:

1. Choose Columns - Allows the user to select the columns that are needed by the user.
2. Remove Columns - Remove the selected columns from the table.
3. Remove Other Columns - Remove other columns from the table except for the selected ones.
4. Move - Columns can be reordered.
5. Keep Rows - Keep top rows/Keep bottom rows/ Keep a range of rows- based on the number of rows specified.
6. Remove Rows - Remove top rows/Remove bottom rows/ Remove alternate rows- based on the number of rows specified.
7. Remove Duplicates - Remove rows containing duplicate values for the selected columns.
8. Split Column -Split values in the selected column based on the delimiter/number of characters.
9. Group by - Group rows based on the selection of columns.
10.                      Datatype - Change datatype for the specified column(Text, Decimal, Whole Number, Logical, Date, Time, Date/Time/Timezone, etc.).
11.                      First Row as header - First row of the column can be used as a header.
12.                      Replace value - Existing value can be replaced with the specified new value.
13.                      Append – Consolidate data from different sources and from multiple tables into one.
14.                      Merge – More like a join operation to add data.

Values can also be sorted ascending/descending.

Merging Two Tables

If you want to merge two columns together, given the need, Power query will evaluate the selection and will give the results on how many matches the selection will return.

To Merge Orders and Returns table, click on the Merge option present in the ‘Power query’ ribbon. Select the tables and matching columns to create a merged table. Matching column act as a join condition.

A new column will be added at the end of the table, which has to be expanded to see the original results.

After expanding the columns, you can see the columns that are merged. Unmatched rows are displayed with the value null.

Load

Close and Load: Save the changes to the query, close the query editor window, and load the data to a new workbook.

Applied Steps

The applied steps section shows the operations applied to the data in sequential order, which helps to keep track of the transformation process. These steps can also be deleted.

Thus power query excel makes it easy to get data in good shape. It has all the common transformations. This is of more use comparing to VBA macros which use coding in data transformation.

One of the key components of Intelligence from Business Intelligence is to find trends and patterns over various periods of time and be able to compare them across. These “Periods of time” have a large generic form or structure such as Month over Month, Year over Year, Same Month Last Year Vs. This year. Power BI Visualizations require DAX’s Time Intelligence functions to calculate through them. The formulae using DAX functions take different shapes depending on the type of measure as being additive or semi-additive or non-additive. Bringing Contexts and Filters in the mix can sometimes make it very complicated to understand.

Let us see synoptic panel by SQLBI and their usage in Power BI Desktop. And also how to customize a visualization using it in Power BI Desktop.

A synoptic designer is a tool of Synoptic Panel by SQLBI. It will allow us to draw customized areas, maps, images, etc. and export it as SVG file to Power BI. Then using it, able to color the different areas based on your dataset data.

The Synoptic Panel connects regions of the image with attributes in the dataset and coloring the regions or with a saturation of a color related to the value of a measure. For any image, you can draw custom areas or regions using the Synoptic Designer tool, and an SVG file is generated, which you import in the Synoptic Panel.

Now you will try to implement one of the travel systems which owns cars; you need to find whether the seats are available to book the cab on a sharing basis or not.

1. Get the desired image on which the dashboard has to be created; here, it is the car seating arrangement. Let the image look as shown.
2. Now open the URL
3. Drag the image or click on the insert an image path/URL, then navigate to the image file location and import.
4. Click on Draw new areas icon. Then go clicking the areas required. Give the appropriate names to those regions as per the data in the dataset. Note: The region names and the data values in the dataset should be the same.
5. You can automatically say the designer to detect the remaining regions by clicking on the tab “automatically detect bitmap areas,” or else you can detect them manually as earlier. And then give appropriate names accordingly.
6. Now click on EXPORT TO POWER BI. Click on DOWNLOAD SVG and save it in the desired location.
7. Now download and import “Synoptic Panel by SQLBI….” visualization and click on it. Select a few of the columns as per the requirement; it will ask for a select map or Gallery. Select one of the options. I click on the Select map, and I will import the SVG file created using the designer in the earlier step.
8. The column values matched with map region names come under Matched areas.
9. You can specify the colors of the matched and unmatched regions to a single color or multiple colors in the Format tab, as shown. Enable the Labels button to show the values.

Now you will try to implement the colorization of the regions based on their availability. I.e., if the seat is available, it should display in green; it is not available or booked, show it in red and if it is the driver seat, show it in yellow.

For this, I am adding a new column as below.

Color = IF(Car[Status] = "Available", 1, IF(Car[Status] = "Booked", 2, 3))

Place the columns values as shown, and give the values for State 1 From = 1, To = 1 and color = Green, for State 2 From = 2, To = 2 and color = Red and for State 3 From = 3, To = 3 and color = Yellow

Similarly you can achieve any desired customized visualization as per the requirement using Power BI Desktop.