Power Query editor.pptx dfrdgzdfgzfdgdfgfdgf

all SQl notes

1. By: Aishwarya Mate DATA ANALYSIS WITH POWER BI

2. Power Query Editor Loading Files from folder

3. Null Value Imputation Missing values from table can be dealt by mainly two methods: Replace Remove To replace the missing values in table, there are 2 functions in Power Bi: Fill down Fill up

4. Append Query Append will join the table row wise.

5. Append Query

6. Merge Query Merging Queries in Power BI is the process of combining columns from two or more tables based on a common field (like a JOIN operation in SQL). This is useful when you want to enrich one table with additional information from another table. You have two tables: Product Sales Data that lists products sold along with their sales amounts and product IDs. Product Details that contains additional information about each product, such as the product name, category, and price, also identified by the product ID. You want to merge these two tables based on the Product ID .

7. Merging

8. Merging Left Anti join: A Left Anti Join in Power BI can be thought of as finding the rows in one table that do not have a matching row in another table.

9. Merging Right Anti join: A Right Anti Join is the opposite of a Left Anti Join. It returns rows from the second table that do not have a matching row in the first table.

10. In Power BI, the Group By operation allows you to summarize your data based on one or more columns, enabling you to perform calculations like sum, average, count, etc. Group By

11. Pivoting : Pivoting is the process of transforming data from a long (or vertical) format to a wide (or horizontal) format by turning unique values from one column into multiple columns. Pivoting

12. In the pivoted table: The "Region" values (North, South, East) have become new columns . The corresponding "Sales" values are placed under each region for every month . The "Month" column remains as the identifier for each row.

13. Unpivoting Unpivoting in Power BI is the process of transforming data from a wide format to a long format. This is the reverse of pivoting. It’s useful when you have columns that represent values (like months or years) and you want to turn them into rows, making the data easier to analyze. Scenario: You have a table where sales data is stored in a wide format with months as columns. You want to convert the months into rows to make it easier to analyze and visualize.

14. Unpivoting Objective: You want to convert the month columns (January, February, March) into rows , so you have a single "Month" column with the corresponding sales amount for each product.

15. Transpose Transposing in Power BI (or Excel) is the process of flipping the rows and columns of a table. This is useful when you want to switch the orientation of your data for better analysis or to meet specific reporting requirements. Transposing in Power Bi has 3 steps: Demote header Transpose Promote headers

16. Transpose

17. Data Modeling Data modeling in Power BI refers to the process of connecting different data sources, defining relationships, and structuring the data in a way that supports efficient analysis and reporting. It involves creating a logical structure that allows users to easily navigate and query the data to extract meaningful insights.

18. Cardinality Cardinality in data modeling refers to the nature of relationships between tables, specifically indicating the number of instances of one entity that can be associated with instances of another entity. In Power BI, cardinality is essential for defining how data tables relate to each other and how they should be joined. Understanding cardinality helps in creating accurate and efficient data models. Types of cardinality in Power Bi: One to One One to Many Many to One Many to Many

19. One to One

20. One to Many

21. Many to One

22. Many to Many In this scenario, authors can write multiple books, and each book can have multiple authors.

23. Creating Hierarchy Hierarchies in Power BI are important for several reasons, as they enhance data analysis, visualization, and overall usability of reports and dashboards. Here are some key reasons why hierarchies are important in Power BI: Improved Data Organization Enhanced Drill-Down Capabilities Simplified Reporting and Analysis Better Data Insights Time-Saving Improved User Experience

24. Drill Options In Power BI, drill options refer to the various interactive features that allow users to explore data at different levels of detail within a hierarchy. These options enable users to navigate from summary-level data to more detailed, granular data and vice versa. The primary drill options in Power BI are: Drill Down : Drill Up Drill Through