Insights on Power BI and M Functions

Aug 1, 2024

Lecture Notes on Power BI and M Functions

Introduction to Power BI

  • Power BI Definition: A business intelligence tool for data analysis and visualization.
  • Main Components:
    • Power Query: ETL tool for data extraction, transformation, and loading.
    • Power Pivot: For data modeling.
    • Power View: For data visualization.
    • Power BI Service: For sharing and collaboration.

Advantages of Power BI

  1. High Search Volume: Increasing popularity over competitors like Tableau.
  2. Maximum Features: Offers around 1200 functions, 280 visuals.
  3. Cost-Effective: Approximately $10/user/month.
  4. Data Connectivity: Connects to 100+ data sources.
  5. Market Recognition: Recognized as a leader by Gartner.

How Power BI Works

  • Power BI Desktop: Developer tool for report creation.
  • Power BI Service: Cloud version for sharing and collaboration.

Installation of Power BI Desktop

Creating Basic Charts in Power BI

  • Column Chart: Most used chart type.
  • Stacked Column Chart: For comparing multiple categories.
  • Pie Chart/Donut Chart: For showing proportions.
  • Funnel Chart: To show stages in a process.
  • Ribbon Chart: To visualize data over time.

Filtering Data in Power BI

  • Use right-click for include or exclude options on charts.
  • Data can be filtered using slicers and buttons.

Advanced Visuals in Power BI

  • Animated Bar Charts: Show trends over time.
  • Drill Down Charts: Allow users to explore data hierarchically.
  • Infographics: Visualize data in engaging designs.
  • Map Visuals: Used for geographical data representation.
  • Custom Visuals: Download from AppSource for different chart types.

M Functions in Power Query

Date Functions

  • Date Is In: Check if a date falls in a specific time frame (previous year, next year).
  • Date Add: Add or subtract days, months, or years from a date.
  • Current Date: Get the current date.

Text Functions

  • Upper/Lower/Proper Case: Change text casing.
  • Text Before/After Delimiter: Extract parts of a string.
  • Concatenation: Merge columns into one using conditions.

Number Functions

  • Basic Operations: Add, subtract, multiply, and divide.
  • Percentage Calculation: Find out percentages of values or totals.
  • Round Functions: Round values up or down to the nearest whole number.

Grouping and Transposing Data

  • Group By: Aggregate data based on columns.
  • Transpose: Switch rows and columns for better data structuring.

Unpivoting Data

  • Unpivot: Transform wide data formats into long data formats for analysis.

Conditional Columns

  • Use conditions to create new columns based on existing data.

Merging Files and Tables

  • Merge queries from different tables and data sources.
  • Handle situations with multiple matching columns efficiently.

Conclusion

  • Power BI is a powerful tool for data visualization and business intelligence.
  • Understanding M functions and how to manipulate data through Power Query is crucial for effective analysis.