Power BI: How to Analyze Datasets in Excel from Power BI Service

Power BI offers the “Analyze in Excel” feature, enabling users to seamlessly connect to datasets hosted in the Power BI Service and analyze them directly within Excel.

Follow these steps to analyze Power BI Service datasets in Excel using the “Analyze in Excel” feature:

1. Open Power BI Service by navigating https://app.powerbi.com 

2. log in with your credential

3. Navigate to the Workspace where the dataset is located.
   

4. Click on the dataset you want to analyze.

5. Select the More options (…) menu next to the dataset name.

6. Click Analyze in Excel.
   

7. A .ODC file (Office Data Connection file) will be downloaded.
   

8. Open Microsoft Excel.

9. Locate and open the .ODC file you downloaded.

10. Excel will connect to Power BI and load the dataset.

11. You will now see the dataset as an OLAP PivotTable, ready for analysis.
    

12. Use the PivotTable Fields pane to drag and drop fields into rows, columns, values, and filters.

13. Apply filters to refine your analysis.

14. Use Excel formulas and functions for advanced calculations.

15. Create charts or graphs using Excel visualization tools.

16. Save your Excel file to your local system or cloud storage.

Power BI - How to use Blank Query to create a report

In Power BI, a Blank Query allows users to create a table or dataset manually using M code in the Power Query Editor — without needing

a connected data source.

Following are the Use Cases & Benefits of using the Blank Query:

1. Ideal for mock data creation, manual lookup tables, or custom calendars.

2. Great for demos, testing calculations, or building logic before data arrives.

3. Helps in creating quick what-if scenarios or manually entered mappings.

Following are the steps to create a report using the Blank Query:

1. Open Power BI → Go to Home > Get Data > Blank Query
   

2. In the Query Editor → Click on Advanced Editor & Paste the M code for your requirement.
   

3. We have created below code for World Clock to display current time for each continent (Clock):

4. Create the Report using the data provided by the query. Below is the sample report that we have created with the query:

How to consume a Flat file (CSV/Text) into Power BI

Flat files like CSV or Txt are one of the most common formats used to exchange data between systems, vendors, or legacy applications. Power BI’s ability to consume, clean, and analyze these files allows teams to quickly turn raw, disconnected data into actionable insights — without needing complex databases or APIs.

Steps to consume a Flat File into Power BI

1. Below is the sample flat file that we are going to consume in Power BI
   

2. Open Power BI Desktop 

3. Go to the Home tab and click Get Data → Text/CSV.
   

4. Browse to your local or network location and select the .txt file you want to load.
   

5. Power BI automatically shows a preview of the file with detected delimiters (e.g., comma, tab, pipe |).

6. You can change the delimiter from the dropdown if required. Choosing the right delimiter prevents data misalignment or loading issues in Power BI. Always ensure the delimiter used in the file matches the one selected during import for accurate parsing.

7. Click Load if the data looks good Or click Transform Data if you need to clean it first (recommended).

8. Below is the table that has been successfully loaded into Power BI
   
   
   

How to connect to SQL Server in Power BI

SQL Server acts as a secure, high-performance backbone for business data, ensuring Power BI has accurate and up-to-date information to analyze. This integration helps businesses quickly uncover insights, monitor operations, and make data-driven decisions that drive growth and efficiency.

Following are the steps to connect SQL Server in Power BI:

1. Open Power BI Desktop

2. From the Home ribbon, click Get Data → Select SQL Server from the data source list.
   

3. In the dialog box, enter the following details:

1. SQL Server Name (e.g., ServerName\Instance or just ServerName).

2. Database Name 

3. Choose the Data Connectivity mode:

1. Import (loads data into Power BI)

2. DirectQuery (queries data live from SQL Server)

Note- This example is for Windows Credentials. If you are using SQL Authentication then you need to enter SQL Server UserName & Password to connect to database

Write Query for a table.

4. Click Ok then you will see data from the table

5. Click Load  to load the data directly.

6. Power BI will also show a navigator that lists available tables and views. We can pick any table or view to see & load the data
   
   

Power BI – How to create a report using Power BI sementic models

Power BI Semantic Models (formerly known as shared datasets) allow you to reuse centralized, governed data models across multiple Power BI reports. This enables consistent definitions, promotes collaboration across teams, and reduces redundancy in data preparation efforts.

Steps to create a report using Power BI semantic models:

1) Open Power BI Desktop

2) Go to Home > Get Data > Power BI datasets

3) We will see a list of available datasets (semantic models) from your workspaces

4) Choose the one you want to connect & Click on Connect

6) you can see a table named SalesData has been created. But you can not see the data in the table

7) Power BI establishes a live connection to the semantic model, meaning the data remains in the service and is not imported into the report.

8) All DAX logic and data relationships are inherited from the semantic model

9) Now you can create a report using fields/measures available in the table

Power BI – How to Load Excel or CSV Files Using Web Connector

Consuming Excel files via Web in Power BI enables seamless collaboration by allowing teams to work with live, cloud-hosted data without manual updates. It ensures that reports always reflect the latest changes, fostering real-time insights, centralized data control, and cross-team transparency.

Steps to Load Excel or CSV Files Using Web Connector

1. Open Power BI Desktop

2. Click on Home > Get Data > Web
   

3. Paste the URL and click Ok
   

4. Power BI will auto-detect the file type. If you use CSV, you’ll see the preview directly.
   

5. Click Load to bring the data into Power BI.

6. Now you can see the data in Power BI Table
   
   
   

Power BI - How to connect to Oracle Database

 Steps to Connect to an Oracle Database

1. Open Power BI Desktop

2. Click on Home > Get Data and search for Oracle Database.

3. Select Oracle Database and click Connect.
   
   

   
   
   

4. You'll be prompted to enter the database connection details:

5. Use the format <Host>:<Port>/<ServiceName> & Click Ok.

Here,

Oracle Server is SALESDB.CloudIndia.com, 1529 is the port number, and SALESGRP is the service name.

6. If authentication is required, enter your username and password.

Click OK to proceed.

7. Power BI will establish the connection and display available tables in the database. Choose the required tables and Load or Transform Data as needed.
   
   

   
   
   

8. Once the data is loaded into Power BI, you can start building visualizations, reports, and dashboards using the imported data.
   
   

   
   
   

Oracle - How to Connect to Oracle Database Using SQL Developer

Oracle Database is a robust, enterprise-level relational database management system used worldwide for storing, managing, and retrieving structured data. It supports high performance, scalability, and security for mission-critical applications.

Oracle SQL Developer is a free graphical tool for database development that simplifies writing and executing SQL queries, managing database objects, and administering Oracle Databases efficiently.

Prerequisites

Before you can connect, ensure you have the following:

1. Oracle Database installed locally or credentials to access a remote database (hostname, port, service name/SID).
   
   

2. SQL Developer installed on your system. You can download it from Oracle’s official site.
   
   

3. Credentials – You need:

1. Username

2. Password

3. Hostname / IP Address

4. Port (default is 1521)

5. Service Name or SID

Steps to connect to Oracle Database using SQL Developer:

1. Launch SQL Developer

2. Open the Connections Panel, On the left side, you will see a “Connections” panel.

3. Click the green "+" icon or go to File → New → Database Connection.
   

4. Fill in Connection Details

5. Click the Test button.

6. If successful, a status message will appear at the bottom as Success

7. Now you can click on Connect button to connect to the database

8. Now try to retrieve data from some table or view