How to use Power Query to load and clean data

Power Query is a powerful data transformation and data loading tool available in Microsoft Excel and Power BI. It allows you to connect to various data sources, load data into a workbook or data model, and perform data cleaning and transformation operations. Here's a step-by-step guide on how to use Power Query to load and clean data: 

1.     Open Excel or Power BI and go to the "Data" tab.
2.     Click on the "Get Data" button and select the desired data source from the available options (e.g., Excel workbook, CSV file, database, web, etc.).
3.     In the Power Query Editor window that opens, you'll see a preview of the data. If needed, you can apply various transformations to clean and shape the data.

a.      Remove unwanted columns: Select the columns you want to remove and click on the "Remove Columns" button in the "Home" tab.
b.     Rename columns: Right-click on a column header and choose the "Rename" option to give it a more descriptive name.
c.      Change data types: Select the columns and choose the appropriate data type from the "Data Type" dropdown in the "Transform" tab.
d.     Filter data: Use the filtering options in the "Home" tab to exclude rows based on specific conditions.
e.      Split columns: If a column contains multiple values separated by a delimiter, you can split it into multiple columns using the "Split Column" feature in the "Transform" tab.
f.       Merge columns: Combine two or more columns into a single column using the "Merge Columns" option in the "Transform" tab.
g.      Remove duplicates: Remove duplicate rows by selecting the columns to consider for duplication and clicking on the "Remove Duplicates" button in the "Home" tab.
h.     Fill missing values: To fill in missing values, select the desired column(s), go to the "Transform" tab, and choose an appropriate option like "Fill Down" or "Replace Values."
i.       Perform calculations: Use the available functions and operators in Power Query to create new calculated columns.

 
4.     As you apply transformations, the preview data in Power Query Editor will update in real-time, allowing you to see the results immediately.
5.     Once you have finished cleaning and transforming the data, click on the "Close & Load" button to load the cleaned data into Excel or Power BI.
6.     Choose the desired destination for the loaded data, such as a new worksheet or a data model.
7.     The cleaned data will be loaded, and any further changes made to the original data will require re-loading or refreshing the query. 

Power Query provides a wide range of data cleaning and transformation capabilities, and the above steps cover some common tasks. However, the specific steps and options may vary depending on your version of Excel or Power BI. Experimenting with the available features and exploring the Power Query documentation can help you discover additional functionality to meet your data cleaning needs.

Excel tables

Excel tables are a feature in Microsoft Excel that allow you to organize and analyze data in a structured and dynamic manner. Tables provide several advantages over regular ranges in Excel, including automatic formatting, filtering, sorting, and built-in formulas.

To convert a range of cells into an Excel table, follow these steps:

1.     Select the range of cells that you want to convert into a table.
2.     In the Excel ribbon, go to the "Home" tab and click on the "Format as Table" button. It is located in the "Styles" group and represented by a grid icon.
3.     Choose the desired table style from the available options. Excel will automatically detect the range, including headers if present, and apply the selected style to the table.

Once the range is converted into a table, it gains a set of features and functionality. Here are some key benefits and features of Excel tables:

1.     Structured organization: Excel tables automatically apply formatting to the data range, including alternating row colors and header formatting. This enhances the readability and aesthetics of the data.
2.     Dynamic range expansion: When you add new data to the table, it will automatically expand to include the new rows or columns. Formulas and references within the table adjust dynamically to accommodate the changes.
3.     Table references: Excel tables use structured references, which are special formulas that refer to table columns by their names rather than cell references. These references make formulas easier to read, write, and maintain.
4.     Filtering and sorting: Tables have built-in filtering capabilities that allow you to quickly sort and filter data based on specific criteria. You can apply filters to individual columns, and Excel provides filter drop downs for each column header.
5.     Total row: Tables have an optional total row that appears at the bottom, allowing you to easily calculate and display totals or other summary functions for each column.
6.     Table formulas: When you use formulas within a table, they are automatically filled down to all the cells in that column, making it easier to apply calculations consistently.

Excel tables are a powerful feature that can significantly enhance your data organization and analysis capabilities. They provide a structured and dynamic environment for working with data and offer various tools and features that simplify data manipulation and analysis tasks.

Society and It's Impacts on Human Life

Society can be defined as a group of individuals living in the same geographical area, sharing a common culture, norms, values, and institutions. It is a complex network of social relationships and interactions that shape the behavior and beliefs of its members.

Society can be divided into different categories based on various factors, such as race, ethnicity, religion, language, social class, and gender. These factors often influence the social, economic, and political opportunities and outcomes for individuals within a society.

Society is also subject to change and transformation over time. It can evolve in response to various factors, such as technological advancements, demographic shifts, political movements, and cultural influences. These changes can lead to new forms of social organization, cultural practices, and economic systems.

The study of society and its functioning is the subject of sociology, which seeks to understand the complex relationships between individuals, groups, and institutions and their impact on social behavior, culture, and society as a whole.

Positive and negative impact of Society on Human life

Society can have both positive and negative impacts on human life. Some of the positive impacts include:

1.   Social support: Society can provide individuals with a sense of belonging, support, and community. This can lead to improved mental health and well-being.

2.  Economic opportunities: Society can provide access to economic opportunities such as education, employment, and social welfare programs, which can improve the standard of living for individuals.

3.  Cultural enrichment: Society can provide individuals with exposure to diverse cultural practices and traditions, which can broaden their horizons and increase their appreciation for different perspectives.

4.    Innovation and progress: Society can foster innovation and progress through technological advancements, scientific discoveries, and social movements that challenge and transform existing norms and practices.

However, society can also have negative impacts on human life. Some of these negative impacts include:

1.    Social inequality: Society can perpetuate social inequalities based on factors such as race, gender, and social class, which can lead to marginalization, discrimination, and exclusion of certain groups.

2.    Social pressure: Society can exert pressure on individuals to conform to social norms and expectations, which can lead to anxiety, stress, and feelings of inadequacy.

3.    Environmental degradation: Society can have negative impacts on the environment through pollution, over consumption of natural resources, and climate change, which can have detrimental effects on human health and well-being.

4.   Conflict and violence: Society can be characterized by conflict and violence, such as war, crime, and interpersonal violence, which can lead to physical and psychological harm for individuals and communities.

Overall, society can have both positive and negative impacts on human life, and it is important to consider and address the negative impacts to create a more just and equitable society.