Data Loading and Transformation in PowerBI

Powerbi is a business intelligence and data visualization tool developed by Microsoft. It allows users to connect various data sources, transform and clean data and create interactive reports and dashboards for data analysis.Powerbi desktop is a free application we can install in our local system,with powerbi desktop we can create reports and with Powerbi Web service we can share our reports with others.

This is PowerBi Desktop. We have report view,dataview and model view,dax query view.  Report view used for visualization.Dataview for Data.Model view form relationship between tables automatically. We can connect tables manually only if tables don't connect automatically.

cardinality refers to the relationship between tables in a data model.

There are three main types of cardinality relationships in Power BI:

One-to-Many : In a one-to-many relationship, each unique value in the primary key column can have multiple corresponding values in the foreign key column.

Many-to-One : Each unique value in the foreign key column corresponds to exactly one value in the primary key column.

One-to-One : each unique value in the primary key column has exactly one corresponding value in the foreign key column.

In the report view we have 3 Panes, filter,visualization and data. in the Data Pane we can see our data files and tables. we can delete the dataset or table by selecting Delete From Model.

Connecting Data:

Get data icon used to connect data from multiple sources.We have different category of DataSources File,Database,Microsoft fabric preview,Power Platform,Azure,

Online Services.

connect data from files:

Powerbi Desktop->Get data->More->

Getdata popup window-> file

(Ex: Excel workbook)->connect-> Excel workbook->open->Select

table->Load.

Connect data from PostgreSQL:

Powerbi Desktop->Get data->More->Database->

PostgreSQL database->Connect->

PostgreSQL pop up window>server,

database->Ok->select tables->Load.

When we are connecting for the first time from postgresQL the default user name will be postgres, password will be pgadmin password.

Connect data from Web:

Getdata->More->Other->Web->Connect->URL->

from web pop-up window select advanced and give URL Link in URL Part->ok->select table->load

Transformations:

We are using ETL(Extract,Transform and Loading) , getting the data,cleaning data and loading data.

For transformation first we have to get data, then we have to connect power Query editor by selecting transform data. we are going to see transformations like renaming columns, renaming tables, and changing datatype. by select transform data in powerbi desktop we get power query editor,

Rename table:

1. Double-click on the table name in queries , give a new name and press Enter.

2.  Right-click on the table name in queries, Choose "Rename" from the menu and change the new name.

Rename Column:

1. Double-click on the query element we want to rename , give a new name and press Enter.

2. Click on the header of the column we want to rename, Right-click on the column header. Choose "Rename" from the menu.give the new name for the column.

Sort Data:

We can sort data by choosing ascending or descending in column header.

Merge Column:

We can merge using transform and add column tab, Doing changes by using Transform tab the original column will update, We lost the original column data.

doing changes by using add column tab a transformation will be in a new column.

 Home Tab Function:

1. Close & Apply: Close the QueryEditorWindow and apply any pending changes.

2. Enter Data: Create New table.

3.  Remove Rows: Choose Rows want to remove.

4. Group by: Group Rows in the table based on the values in the selected Column.

Transform Tab Function:

1. Data type: We can change the datatype.

2. Format: We can cleanse data.

3. CountRows: Return Number of Rows in the table.

4. Split Column : We can Split Column with Delimiter (Ex: comma), We can Split columns by the Number of Characters.

5. Move: Move columns to a different position.

6. Extract: Extract characters from text.

7. Fill: Fill used to fill empty cells.

8. Standard: perform basic math functions.   

9. Reverse Row: Reverse Rows in table.
   

10. Transpose: Transpose this table rows as columns, columns as rows.

11. Rename: change the name of selected column.

12. Pivot : We can pivot columns to transform unique values in a column into separate columns.

Add Column Tab Functions:

1. Custom Column: add a new column in the table based on a custom formula.

2. Index Column: Create New Column with an index

3. Conditional Column: Create a new column with values based on specified conditions. we can define one or more conditions.

4. Column From Examples: create a new column by providing examples of the desired output

5. Duplicate Column: Create a New Column that duplicates the values in the selected column.

6. Extract: Extract characters from Text.

7. Rounding: perform Rounding on Numbers.

8. Information: Extract information about Column.

9. Replace Values:

View Tab Function:

1. Column Quality : It gives data quality information.Empty values represent Null value or blank spaces. Example: Valid 100%,Error 0%,Empty 0%

2. Column Distribution: It shows how many distinct values and how many unique values in each column.Example: 4 Distinct,0 unique

3. Column Profile: It will distribute the Statistics about the column. Example: Error 0 Empty 0 Distinct 11 Unique 0 NaN 0 Min 1000 Max 19000

4. Monospaced: Monospaced refer font type, all the characters in the column will be aligned.

5. Formula Bar:

Help Tab Function:

1. Training videos: step by step instructions of how to use different types of powerbi.

2. Community: Community provides links to community Powerbi.

3. Guided Learning: Overview of Powerbi.