Connecting to different datasets in power bi

Connecting to different datasets in Power BI involves accessing various

data sources and integrating them into your Power BI reports and
dashboards. Here’s a detailed guide on how to connect to different
datasets in Power BI:

1. Opening Power BI Desktop

 Launch Power BI Desktop: This is where you will create and

manage your connections to different datasets.

2. Connecting to a Data Source

 Get Data:

1. Go to the Home tab.

2. Click on Get Data. This opens a window where you can select
your data source.

 Choose Data Source Type: Power BI supports a wide range of

data sources, including:

o File: Excel, CSV, XML, JSON, etc.

o Database: SQL Server, MySQL, PostgreSQL, Oracle, etc.

o Online Services: SharePoint, Dynamics 365, Google

Analytics, etc.

o Web: Use web scraping or connect to APIs.

o Other: OData, Azure services, Hadoop, etc.

 Examples of Connecting to Common Data Sources:

o Excel:

1. Select Excel from the list.

2. Browse to the Excel file and select it.

3. Choose the relevant sheets or tables you want to

import.

o SQL Server:

1. Select SQL Server from the list.

2. Enter the server name and database name.

3. Choose the data import method (DirectQuery or Import).

 4. Select tables or views to import.

3. Connecting to Multiple Data Sources

 Add New Data Source: To connect to additional data sources,

repeat the steps above:

1. Click Get Data again.

2. Select the new data source and follow the connection steps.

 Managing Connections:

o Data Source Settings: Go to File>Options and

settings>Data source settings to view and manage all
connections.

o Manage Relationships: Use the Model view to create and

manage relationships between tables from different data
sources.

4. Transforming and Preparing Data

 Power Query Editor:

1. After connecting to a data source, click Transform Data to

open the Power Query Editor.

2. Use Power Query to clean, transform, and combine data from

different sources.

3. Apply transformations such as filtering, merging, and

appending tables.

5. Creating Relationships Between Tables

 Define Relationships:

1. Go to the Model view.

2. Drag and drop fields to create relationships between tables.

3. Define cardinality (e.g., one-to-many, many-to-one) and cross-

filtering options.

6. Using Data in Reports

 Build Visualizations:

1. Drag and drop fields from different tables onto the report
canvas.
 2. Create charts, tables, and other visuals using data from
multiple datasets.

 DAX Calculations: Use DAX (Data Analysis Expressions) to create

measures and calculated columns that combine data from different
tables.

Basic data prep and model on power query in power bi

Basic data preparation and modeling in Power Query within Power BI
involves several key steps to clean, transform, and structure your data
before you start creating reports and visualizations. Here’s a step-by-step
guide to get you started:

1. Opening Power Query Editor

 Launch Power BI Desktop: Open your Power BI Desktop

application.

 Get Data: Click on the Home tab and then Get Data to connect to
your data source (e.g., Excel, SQL Server, CSV file).

 Load Data: Select your data source, load the data into Power BI,
and then click on Transform Data to open the Power Query Editor.

2. Data Preparation in Power Query Editor

**a. Navigating the Power Query Editor

 Query Pane: Lists all the tables and queries you’re working with.

 Data Preview: Shows a preview of the data in the selected query.

 Applied Steps: Displays the steps you’ve applied to transform the

data, which can be edited or removed.

**b. Common Data Transformation Tasks

 Remove Columns:

o Right-click on the column header and select Remove.

o Or use the Remove Columns button on the Home tab to

remove multiple columns.

 Rename Columns:
 o Right-click on the column header and select Rename.

o Enter the new column name.

 Change Data Types:

o Click on the column header.

o Use the Data Type dropdown in the Transform tab to select

the appropriate data type (e.g., Text, Number, Date).

 Filter Rows:

o Click the filter icon on a column header.

o Select or deselect values to filter the data accordingly.

 Sort Data:

o Click the drop-down arrow in the column header.

o Choose to sort ascending or descending.

 Split Columns:

o Select the column you want to split.

o Use the Split Column option in the Transform tab to split by

delimiter (e.g., comma, space) or by number of characters.

 Merge Columns:

o Select multiple columns to merge.

o Use the Merge Columns option to combine them into a

single column with a separator.

 Remove Duplicates:

o Select the column(s) to check for duplicates.

o Click on Remove Rows>Remove Duplicates in the Home

tab.

 Fill Data:

o Use Fill Down or Fill Up to propagate values from adjacent

cells.

 Group By:

o Use Group By in the Home tab to aggregate data. For

example, you can group data by a category and calculate the
sum, average, or count of another column.
  Pivot and Unpivot:

o Pivot Columns: Transform rows into columns (e.g., to create

a summary table).

o Unpivot Columns: Convert columns into rows (e.g., to

normalize data).

3. Creating a Data Model

**a. Loading Data

 After preparing your data, click Close & Load to load the
transformed data into Power BI Desktop’s data model.

**b. Managing Relationships

 Model View: Go to the Model view to create and manage

relationships between different tables.

 Create Relationships:

1. Drag a field from one table to a related field in another table.

2. Define relationship properties, such as cardinality (one-to-

many, many-to-one) and cross-filter direction.

**c. Creating Calculations

 Calculated Columns: Create new columns based on existing data

using DAX formulas.

o Go to the Data view.

o Click Modeling>New Column and enter your DAX formula.

 Measures: Create aggregate calculations like totals, averages, or

percentages.

o Go to the Data view.

o Click Modeling>New Measure and write your DAX formula.

4. Building Reports and Visualizations

 Create Visuals: Drag and drop fields from the fields pane onto the
report canvas to create charts, tables, maps, etc.

 Use Slicers and Filters: Add slicers and filters to make your
reports interactive.

 Customize and Format: Adjust visual settings to enhance the

appearance and clarity of your reports.
Drill Down

Drill Down allows users to navigate from a summary level of data to

more detailed levels. This feature is useful for exploring data hierarchies
and uncovering deeper insights.

How to Use Drill Down:

1. Create Hierarchies:
o Ensure your data model has hierarchies defined. For example,
a common hierarchy might be Country -> State -> City.

2. Add Hierarchy to Visuals:

o Drag your hierarchical fields into a visual like a bar chart or
matrix. For example, add Country, State, and City to a bar chart.

3. Enable Drill Down:

o Click on the visual to select it.
o On the visual header, you'll see drill down icons (e.g., arrows).
You can use these to drill down into the data. If you don’t see
these icons, make sure the visual is selected and check the
"Drillthrough" or "Drill Down" options in the Visualizations
pane.

4. Use Drill Down:

o Click on a data point in the visual to drill down. For example,
clicking on a country will drill down to show data at the state
level.
o Use the "Back" button (typically a left arrow icon) to navigate
back to the previous level.

5. Configure Drillthrough:
o You can also set up Drillthrough Pages for more detailed
insights. This allows users to right-click on a data point and
navigate to a separate page that provides a deeper analysis of
that point.

Tooltip

Tooltips provide additional information when users hover over or click on

a data point in a visual. They are useful for displaying extra details
without cluttering the visual itself.

How to Use Tooltips:

1. Basic Tooltips:
 o By default, Power BI displays basic tooltips showing the value
of the data point you're hovering over. You can customize
what’s shown in these tooltips by modifying the fields in the
visual.

2. Custom Tooltips:
o To create a custom tooltip page:
1. Create a new page in your Power BI report.
2. Set the page size to Tooltip (you can do this from the
Format pane under Page size).
3. Add visuals to this page to show detailed information.
4. Return to your main report page, select the visual you
want to add a tooltip to.
5. In the Visualizations pane, find the Tooltip field well.
Drag the desired fields or measures into this area.

3. Configure Tooltip:
o For the visual you want to customize:
1. Go to the Format pane.
2. Expand the Tooltip section.
3. Configure the settings to either use a report page as a
tooltip or customize the default tooltip.

4. Test the Tooltip:

o Hover over or click on the data points in your visual to see the
custom tooltip in action.

AI visuals in Power BI leverage artificial intelligence to provide advanced

analytics and insights directly within your reports and dashboards. These
visuals enhance your ability to analyze and understand data by
integrating AI-driven features like anomaly detection, forecasting,
clustering, and key influencers. Here's an overview of the various AI
visuals available in Power BI and how to use them:

1. Key Influencers Visual

Key Influencers visual helps identify factors that most significantly

impact your metrics or outcomes. It analyzes your data to show which
factors are driving changes or influencing the results.

How to Use:

1. Add the Visual:

o Go to the Visualizations pane and select the Key Influencers
visual.

2. Configure the Visual:

 o Drag the field you want to analyze into the Analyze field well.
This is typically your target metric or outcome.
o Drag potential influencing factors (variables) into the Explain
by field well.

3. Analyze Results:
o The visual will show how different factors impact the selected
outcome. It provides insights into which variables are most
influential and how they affect the result.

2. Decomposition Tree Visual

Decomposition Tree visual allows users to break down a measure into

its contributing factors hierarchically, providing a detailed view of how
different dimensions affect the metric.

How to Use:

1. Add the Visual:

o Select the Decomposition Tree visual from the Visualizations
pane.

2. Set Up the Visual:

o Drag the measure you want to analyze into the Analyze field.
o Add dimensions to the Explain by field to specify which
aspects you want to use to break down the measure.

3. Explore the Data:

o Click on the measure to see a breakdown by different
dimensions. You can drill down into each level to understand
how each factor contributes to the overall metric.

3. Q&A Visual

Q&A visual enables natural language queries, allowing users to ask

questions in plain English and get instant visual answers.

How to Use:

1. Add the Visual:

o Select the Q&A visual from the Visualizations pane.

2. Configure the Q&A Visual:

o Set up the visual by defining the data model and providing a
good set of example questions.

3. Interact with the Visual:

o Users can type questions into the Q&A box, and the visual will
generate answers in the form of charts or tables based on the
data available.