An assignment of the beginner level, Getting started with power Bi desktop, merging and appending data, transforming data

MARYAM ASAD

03-05-2024

An assignment of the beginner level, Getting started with power Bi desktop, merging and appending data, transforming data:

1.Getting Started with Power Bi Desktop:

Q:1- How do you create a new report in Power Bi desktop?

Open Power BI Desktop: Launch the Power BI Desktop application on your computer.

Connect to Data: In the Home tab, click on "Get Data" to connect to your data source. You can connect to various data sources such as Excel files, databases, online services, etc.

Choose Data Source: Select the data source you want to use for your report. This could be a file, database, online service, or another source supported by Power BI.

Load Data: After connecting to the data source, Power BI will load the data into the Data Model. You can transform and clean the data using Power Query Editor if needed.

Create Visualizations: Once your data is loaded, navigate to the Report tab. Here, you can create various visualizations like charts, graphs, tables, and maps based on your data.

Drag and Drop Fields: In the Fields pane on the right side, drag and drop fields from your data tables into the Values, Axis, Legend, and Filters areas to create visualizations.

Customize Visuals: Customize each visualization by selecting different chart types, colors, labels, and formatting options available in the Visualizations pane on the right.

Add Filters and Slicers: Use Filters and Slicers to allow users to interactively filter and slice data in your report based on their preferences.

Add Titles and Text Boxes: Enhance your report by adding titles, text boxes, images, and other elements using the Insert tab.

Arrange and Format: Arrange your visuals on the canvas and format them to create a visually appealing and informative report.

Save Your Report: Finally, save your report by clicking on File > Save or Save As. You can save it locally or to your Power BI service account for sharing and collaboration.

Q:2 -What is the purpose of the navigation pane in power Bi desktop?

The navigation pane in Power BI Desktop serves several important purposes:

1.???? Data Source Navigation: The primary purpose of the navigation pane is to allow users to navigate and manage data sources. You can see a list of all connected data sources, including files, databases, online services, and data tables.

2.???? View Data Tables and Fields: Within each data source, the navigation pane displays a hierarchical view of data tables and their fields. This makes it easy to understand the structure of your data and select specific fields for analysis and visualization.

3.???? Data Model Management: The navigation pane also plays a role in managing the data model. You can view relationships between tables, create calculated columns and measures, and define hierarchies directly from the navigation pane.

4.???? Access Query Editor: The navigation pane provides access to the Query Editor, where you can perform data transformations, cleaning, and shaping operations on your data before loading it into the data model. You can launch the Query Editor by right-clicking on a data source or table in the navigation pane.

5.???? Organize Reports and Dashboards: In addition to data-related functions, the navigation pane helps organize reports and dashboards. You can create folders to categorize reports, datasets, and other objects, making it easier to manage larger projects and collaborate with team members.

6.???? Manage Relationships: If your data model includes multiple tables with relationships, you can manage and view these relationships in the navigation pane. This is crucial for building accurate and insightful reports that rely on data from different tables.

7.???? Access Report Pages: In newer versions of Power BI Desktop, the navigation pane also includes a section for report pages. This allows you to quickly switch between different report pages and manage their layout and interactions.

Q:3 - How do you switch between different views in Power Bi desktop?

Switching between Report View, Data View, and Model View: Report View: This is the default view where you create and design visualizations for your reports. To switch to Report View, click on the "Report" icon in the left sidebar.

1.???? Data View: This view allows you to see the underlying data tables and fields in your dataset. To switch to Data View, click on the "Data" icon in the left sidebar.

2.???? Model View: This view is used for managing the data model, including relationships, measures, and calculations. To switch to Model View, click on the "Model" icon in the left sidebar.

3.???? Switching between Report Pages: If your report contains multiple pages, you can switch between them by clicking on the page tabs at the bottom of the canvas. Each page represents a different set of visualizations and data analysis.

4.???? Navigating between Query Editor and Data Model: To switch between the Query Editor (where you perform data transformations) and the Data Model (where you manage relationships and calculations), you can use the tabs at the top of the power BI Desktop window. Click on "Home" for Query Editor and "Model" for Data Model.??? Using Keyboard Shortcuts: Power BI Desktop also supports keyboard shortcuts for quickly switching between views. For example, you can use Ctrl+D to switch to Data View, Ctrl+R to switch to Report View, and Ctrl+E to switch to Model View.

5.???? Switching between Different Reports: If you have multiple Power BI reports open in separate tabs, you can switch between them by clicking on the respective tab at the top of the Power BI Desktop window.

Q:4 - What is the difference between power bi desktop and power bi service?

Power BI Desktop: Purpose: Power BI Desktop is a desktop application used for creating reports and data models. It is where you build and design visualizations, connect to data sources, transform data, and create relationships between datasets.

Key Features:

1.???? Report authoring and design.

2.???? Data modeling and relationships.

3.???? Data transformations using Power Query Editor.

4.???? Advanced analytics and calculations with DAX (Data Analysis Expressions).

5.???? Local file storage (reports are saved as pbix files on your computer).

Usage: Power BI Desktop is primarily used by data analysts, report developers, and business users to create interactive and insightful reports before publishing them to the Power BI Service for sharing and collaboration.

Power BI Service (also known as Power BI Online or Power BI Cloud):

Purpose: Power BI Service is a cloud-based platform for publishing, sharing, and collaborating on Power BI reports and dashboards. It allows users to view and interact with reports online, share insights with colleagues, and access reports from any device with an internet connection.

Key Features:

1.???? Report sharing and collaboration.

2.???? Scheduled data refresh for up-to-date reports.

3.???? Data access control and security settings.

4.???? Integration with other Microsoft services like SharePoint, Teams, and Excel.

5.???? Mobile access to reports and dashboards.

Usage: Power BI Service is used by teams and organizations to share and consume reports, collaborate on data analysis, create dashboards, set up data alerts, and manage access permissions.

2. Connecting to data sources :

Q:1 - What are the different types of data sources that Power Bi can connect to?

Power BI can connect to various types of data sources including:

1.???? Files: Excel, CSV, XML, JSON, PDF.

2.???? Databases: SQL Server, MySQL, PostgreSQL, Oracle, DB2.

3.???? Online Services: SharePoint, Dynamics 365, Salesforce, Google Analytics.

4.???? Cloud Storage: Azure Blob Storage, Amazon S3.

5.???? Other Sources: Web, Hadoop, OData, R Script, Custom APIs.

Q:2 - How do you connect to an excel file in Power Bi desktop?

To connect to an Excel file in Power BI Desktop, follow these steps:

1.???? Open Power BI Desktop.

2.???? Click on the "Get Data" button in the Home tab.

3.???? Select "Excel" from the list of data sources.

4.???? Browse to the location of your Excel file and select it.

5.???? Choose the specific worksheet or table you want to import.

6.???? Click "Load" or "Transform Data" to import and transform the data as needed.

Q:3 - What is the purpose of the data source settings option?

Connection Configuration: One of the primary purposes of data source settings is to configure the connection parameters required to establish a connection with a data source. This includes details such as the host or server address, port number, database name (if applicable), username, password, and any additional authentication or encryption settings.

Data Retrieval and Storage: By configuring the data source settings correctly, applications can retrieve data from the source (like fetching records from a database) and store data into the source (like adding new records or updating existing ones).

Security and Access Control: Data source settings often include options related to security and access control. This can involve specifying the type of authentication (such as username/password, OAuth tokens, API keys), encryption methods for data transmission (like SSL/TLS), and permissions management (limiting access to specific data or operations based on user roles).

Performance Optimization: Advanced data source settings may include parameters for optimizing performance, such as setting connection timeouts, adjusting buffer sizes for data retrieval, enabling caching mechanisms, or configuring connection pooling to manage resources efficiently.Error Handling and Logging: Some data source settings allow configuring error handling mechanisms, such as specifying how errors should be logged or displayed to users. This helps in diagnosing and resolving issues related to data retrieval or connectivity problems.

Compatibility and Integration: Depending on the application or system, data source settings may also offer options for selecting the appropriate data source type (e.g., MySQL, PostgreSQL, MongoDB, RESTful API) and configuring specific features or behaviors that align with the data source's capabilities and requirements.

Q:4? - How do you use the query editor to connect to a data source?

Open the Query Editor: Start by opening the query editor tool in your software or platform. This tool is commonly used for writing and executing SQL queries or other types of data retrieval commands.

Select the Data Source: Within the query editor interface, look for an option to select or configure the data source. This option may be labeled as "Connect," "Data Source," "Connection Manager," or something similar.

Choose the Connection Type: Depending on the capabilities of the query editor, you may need to specify the type of data source you want to connect to. Common types include databases (e.g., MySQL, SQL Server, PostgreSQL), APIs (RESTful, SOAP), or file-based sources (CSV, Excel).

Enter Connection Details: Provide the necessary connection details for the chosen data source type. This typically includes information such as the server address or URL, port number (if applicable), database name (for databases), authentication credentials (username and password), and any additional settings required for the connection (e.g., encryption settings, driver selection).

Test the Connection: Many query editors offer a feature to test the connection before proceeding. Use this option to ensure that the provided connection details are correct and that the query editor can establish a connection with the data source.

Save the Connection Profile: Once the connection is successfully tested, you may have the option to save the connection profile for future use. This can be helpful for quickly reconnecting to the same data source without re-entering all the connection details each time.

Write and Execute Queries: With the data source connected, you can now start writing and executing queries or commands against the data. Use SQL or the appropriate query language supported by your data source to retrieve, manipulate, or analyze data as needed.

Review Results: After executing queries, review the results returned by the data source within the query editor interface. Depending on the query editor, you may have options for formatting results, exporting data, or further analyzing the output.

Q:5? - What is the difference between a live connection and an import connection?

Live Connection:

1.???? Real-Time Access: In a live connection, data is accessed directly from the source in real time. This means that when you interact with the data in your software or platform (such as performing queries, creating visualizations, or generating reports), the software communicates directly with the source to fetch the most up-to-date data.

2.???? Dynamic Updates: Any changes or updates made to the data source are reflected immediately in your software. For example, if new records are added, existing records are modified, or data is deleted in the source, those changes are visible in real time through the live connection.

3.???? Dependency on Source Availability: Since data is fetched dynamically from the source, a live connection requires the source to be available and accessible whenever you need to work with the data. If the source experiences downtime or connectivity issues, it can impact your ability to access the data in real time.

Import Connection:

1.???? Data Extraction and Storage: In an import connection, data is extracted from the source and stored locally within your software or platform. This data extraction is typically done at specific intervals or upon user request, rather than in real time.

2.???? Static Snapshot: Once the data is imported, it forms a static snapshot or copy of the data at the time of import. Any changes made to the original data source after the import are not automatically reflected in the imported data unless you perform another import operation.

3.???? Independent of Source Availability: Since the data is stored locally after import, you can work with it even if the original data source is temporarily unavailable. However, this also means that the data may become outdated if not refreshed regularly through new import operations.

3.Importing data into Power Bi :

Q:1 - Import the data into Power Bi ?

Q:2 -What are the steps you took when importing the data?

Open Power BI Desktop:

Launch Power BI Desktop on your computer.

Get Data: In Power BI Desktop, click on the "Home" tab in the ribbon menu.

Click on "Get Data" and then choose "Excel" from the list of data sources. Alternatively, you can click on "Excel" directly if it's available in the Home tab.

Locate and Select Excel File:

·?????? Navigate to the location where your Excel file is saved.

·?????? Select the Excel file (.xlsx or .xls) that contains the data you want to import.

·?????? Click "Open" to proceed.

Choose Data to Import:

??????? After selecting the Excel file, Power BI will display a navigator window showing the sheets or tables available in the Excel file. Choose the sheet or table that contains the data you want to import. You can preview the data by clicking on the sheet/table name.

Transform Data (Optional):

??????? If you need to perform any data transformations, such as cleaning, filtering, or reshaping the data, you can do so in the Power Query Editor. Click on "Transform Data" or "Load" to open the Power Query Editor.

Edit Data in Power Query Editor (Optional):

??????? In the Power Query Editor, you can apply various transformations to the data using the options available in the ribbon menu. Common transformations include removing columns, renaming columns, changing data types, filtering rows, and merging data from multiple sources. Once you're done with the transformations, click on "Close & Apply" to load the data into Power BI.

Load Data into Power BI:

??????? After closing the Power Query Editor, Power BI will prompt you to load the data into the dataset. Choose the "Load" option to load the data directly into the dataset without any further transformations. Alternatively, choose "Transform Data" if you want to perform additional transformations before loading.

Verify Data Load:

??????? Once the data is loaded, you should see the imported data in the Fields pane on the right side of the Power BI Desktop interface. You can now use this data to create visualizations, reports, and dashboards within Power BI.

Q:3? - How do you import data from a csv file into Power Bi?

To import data from a CSV file into Power BI:

1.???? Open Power BI Desktop.

2.???? Click "Get Data" > "Text/CSV."

3.???? Select your CSV file and click "Open."

4.???? Choose the data to import and click "Load."

5.???? Optionally, use the Power Query Editor to edit data.

6.???? Close the editor and verify the data in Power BI's Fields pane.

Q:4? - How do you schedule data refresh in Power Bi?

To schedule data refresh in Power BI:

1.???? Go to "File" > "Options and settings" > "Data source settings."

2.???? Select the data source and click "Schedule settings."

3.???? Enable scheduled refresh and set the frequency and time for refresh.

4.???? Save the settings to schedule automatic data refresh in Power BI.

4.Transforming data using Power query editor :

Q:1 - What is power query editor and what is its purpose?

The Power Query Editor is a tool within Power BI that allows users to transform and shape data from various sources before loading it into Power BI for analysis and visualization. Its purpose is to clean, transform, merge, and manipulate data to prepare it for analysis, ensuring data quality and consistency in reports and dashboards.

Q:2? - What are the different types of data transformations available in power query editor?

In Power Query Editor, you can perform various data transformations, including:

1.???? Cleaning: Removing duplicates, filtering rows, and handling null or missing values.

2.???? Formatting: Changing data types, renaming columns, and applying formatting.

3.???? Combining: Merging, appending, or joining tables.

4.???? Splitting: Splitting columns based on delimiters or patterns.

5.???? Aggregating: Grouping data and calculating summaries.

6.???? Pivoting and Unpivoting: Rotating data to pivot tables or converting pivoted data to a tabular format.

7.???? Adding Custom Calculations: Creating new columns with calculated values using M expressions.

8.???? Advanced Transformations: Applying conditional logic, creating conditional columns, and using custom functions.

Q:3? - Perform 7 transformations in power query using the data you imported?

Remove Duplicates:

·?????? Select the column(s) containing duplicate values.

·?????? Click on "Remove Duplicates" in the ribbon's "Home" tab.

·?????? Choose the columns for duplicate removal and click "OK."

Filter Rows:

·?????? Use the filter icons in column headers to filter rows based on specific criteria.

·?????? Or, click on "Filter Rows" in the "Home" tab to set advanced filtering conditions.

Change Data Types:

·?????? Select the column(s) whose data type you want to change.

·?????? Click on the data type icon in the column header and choose the desired data type.

Split Columns:

·?????? Select the column you want to split.

·?????? Click on "Split Column" in the ribbon's "Transform" tab and choose the splitting option (by delimiter, number of characters, etc.).

Add Custom Column:

·?????? Click on "Add Column" in the ribbon's "Home" tab.

·?????? Choose "Custom Column" and define a custom formula using M language for the new column.

Group By:

·?????? Select the column(s) you want to group by.

·?????? Click on "Group By" in the ribbon's "Home" tab and define the grouping criteria and aggregation functions.

Merge Queries:

·?????? Load another table or query into Power Query Editor.

·?????? Select both tables/queries and click on "Merge Queries" in the "Home" tab to perform a merge operation based on matching columns.

Q:4? - How do you use the formula bar in power query editor?

In Power Query Editor, you can use the formula bar to create and edit M language formulas for data transformations. Simply select a step in the query's applied steps pane, then use the formula bar to modify or add transformations using M language functions and syntax.

Q:5 - How do you handle missing values in power query editor?

In Power Query Editor, you can handle missing values using various techniques:

??? Remove Rows: Use the "Remove Rows" feature to eliminate rows with missing values in specific columns or across the entire dataset.

1.???? Fill Down or Up: Use the "Fill Down" or "Fill Up" options to propagate non-missing values from adjacent rows into the missing value cells.

2.???? Replace Values: Use the "Replace Values" option to replace missing values with a specified default value or a value derived from other columns.

3.???? Custom Columns: Create custom columns that calculate values based on conditions, such as replacing missing values with averages, medians, or specific calculations.

4.???? Conditional Handling: Use conditional logic to handle missing values based on specific criteria, such as replacing missing values in one column based on values in another column.

5.???? Advanced Transformations: Apply advanced transformations using M language functions to handle missing values in complex scenarios, such as imputation techniques or conditional replacements.

5.Merging and Appending data :

Q:5 - What is the difference between merging and appending data?

Merging Data:

1.???? Purpose: Merging is used to combine data horizontally based on matching values in one or more common columns (keys).

2.???? Result: The result of merging is a new dataset with columns from both sources aligned based on the matching keys. Rows are combined where the keys match, creating a wider dataset with additional columns.

3.???? Common Use: Merging is commonly used when you have related data split across different tables or sources, and you want to combine them based on shared key columns (e.g., merging sales data with customer data based on a customer ID).

Appending Data:

1.???? Purpose: Appending is used to combine data vertically by stacking rows from one dataset below another dataset.

2.???? Result: The result of appending is a single dataset with rows from both sources stacked on top of each other. The column structure remains unchanged.

3.???? Common Use: Appending is useful when you have similar data structures (same columns) from different sources or time periods and you want to combine them into a single dataset without changing the column structure (e.g., appending monthly sales data into a single yearly dataset).

Q:2 - How do you merge two tables in power query editor?

To merge two tables in Power Query Editor, follow these steps:

Open Power Query Editor:

Load the tables you want to merge into Power Query Editor.

Select the First Table:

In the Power Query Editor, select the first table from the Queries pane.

Merge Queries:

Click on the "Home" tab in the ribbon menu. Click on "Merge Queries" in the "Combine" group.

Choose Second Table and Key Columns:

In the "Merge" dialog box, select the second table you want to merge with the first table from the drop down list. Choose the key columns in each table that you want to use for merging. These are the columns that have matching values.

Merge Options:

Choose the type of join you want (e.g., Inner, Left Outer, Right Outer, Full Outer). Optionally, specify if you want to create a new column with matching values or expand the columns from the second table into the first table.

Preview and Apply Merge:

Click "OK" to preview the merge results in the Power Query Editor. Review the merged data to ensure it matches your expectations. Click "Apply" to apply the merge and load the merged data into Power BI.

Q:3 - How do you append data to an existing table in power query editor?

To append data to an existing table in Power Query Editor:

·?????? Load both the existing table and the new data into Power Query Editor.

·?????? Select the new data table.

·?????? Click on "Append Queries" in the "Combine" group on the Home tab.

·?????? Choose the existing table you want to append the data to.

·?????? Review the appended data preview and click "OK" to apply the append operation.

·?????? Click "Close & Apply" to load the updated table with the appended data into Power BI.

Q:4 - What is the purpose of the join option in power query editor?

The purpose of the join option in Power Query Editor is to combine data from multiple tables based on matching values in specified columns. Joins help create a unified dataset by merging related information from different sources, allowing for comprehensive analysis and reporting in Power BI.

Q:5 - How do you use the combine queried option in power query editor?

To use the "Combine Queries" option in Power Query Editor:

1.???? Load the tables or queries you want to combine into Power Query Editor.

2.???? Select one of the queries from the Queries pane.

3.???? Click on the "Home" tab in the ribbon menu.

4.???? Click on "Combine Queries" in the "Combine" group.

5.???? Choose the type of combination (e.g., Union, Merge, Append, Intersect) you want to perform.

6.???? Follow the prompts and select the necessary options based on the combination type.

7.???? Review the preview of the combined data.

8.???? Click "OK" to apply the combination and load the combined data into Power BI.