Connect Google Sheets Data to Power BI

 

Steps to Connect Google Sheets Data to Power BI

Step 1: Publish Google Sheets to the Web

1. Open your Google Sheet containing the dataset.
2. Click on File → Share → Publish to Web.
3. Under Link, select the specific sheet or entire document you want to publish.
4. Choose Comma-Separated Values (.csv) as the format.
5. Click Publish, confirm, and copy the generated link.

---

Step 2: Connect Google Sheets to Power BI

1. Open Power BI Desktop.
2. Click on Home → Get Data → Web.
3. Paste the published Google Sheets link in the URL box.
4. Click OK → Power BI will load the data.

---

Step 3: Transform Data (if needed)

1. Power Query Editor will open.
2. Check the column names and data types.
3. Perform data cleaning, such as:
   • Remove null values.
   • Convert data types (e.g., Numbers, Text, Dates).
   • Rename columns for better readability.
4. Click Close & Apply to save changes.

---

Step 4: Set Up Data Refresh (Optional)

1. In Power BI Desktop, go to Transform Data → Data Source Settings.
2. Enable automatic refresh to fetch updates from Google Sheets.
3. If using Power BI Service, schedule data refresh in settings.

---

Research Questions and Steps for Analysis

1. Which states in India have the highest and lowest population density?

Steps:

1. Select a Table/Chart → Use a Bar Chart or Map visualization.
2. X-Axis: States/Union Territories.
3. Y-Axis: Population per sq. km.
4. Filters: Urban vs. Rural population.
5. Insights: Identify states with the highest and lowest population density.
6. Format visuals

---

2. How does the rural vs. urban population distribution vary across states?

Steps:

1. Select a Stacked Column Chart → Compare rural vs. urban population.
2. X-Axis: States/Union Territories.
3. Y-Axis: Population (Total, Rural, Urban).
4. Legend: Rural vs. Urban.
5. Insights: Analyze the urbanization levels in each state.
6. Format visuals

---

3. What is the correlation between area size and population density in different states?

Steps:

1. Select a Scatter Plot to analyze the relationship.
2. X-Axis: Area (sq. km).
3. Y-Axis: Population density.
4. Size: Population of the state.
5. Trendline: Identify if larger areas have lower or higher density.
6. Format visuals

---

4. How many towns and villages exist in each state, and what is the household distribution?

Steps:

1. Select a Clustered Bar Chart.
2. X-Axis: Number of villages/towns.
3. Y-Axis: States/Union Territories.
4. Filters: Filter by Inhabited vs. Uninhabited villages.
5. Insights: Find states with the most rural settlements vs. urban expansion.
6. Format visuals

---

5. What is the male-to-female ratio in different states?

Steps:

1. Select a Pie Chart or Donut Chart.
2. Values: Male vs. Female Population.
3. Filters: State-wise comparison.
4. Insights: Identify gender ratio variations across states.
5. Format visuals

---