Introduction to M Query in Power BI

🔹 Why Use M Query?

M Query, also known as the Power Query Formula Language, is a functional language used in Power BI to transform and shape data before loading it into the report. It is a powerful tool that enables users to clean, filter, merge, and add custom calculations to datasets within the Power Query Editor.

• Automates Data Transformation: Helps in cleaning and preparing data before visualization.

• Enhances Performance: Reduces the load on Power BI's DAX engine by pre-processing data.

• Custom Computations: Enables advanced transformations that are not always possible through the UI.

• Repeatability & Reusability: Once defined, transformations can be applied dynamically to updated datasets.

• Importing and shaping data from different sources (Excel, SQL, APIs, etc.).

• Renaming columns for better clarity.

• Changing data types to ensure proper calculations.

• Creating custom columns for computed values.

• Sorting and filtering data efficiently.

• Removing unnecessary columns to optimize dataset size.

Step-by-Step Guide to Using M Query in Power BI

---

🔹 Step 1: Load Data into Power BI (Result Data)

Objective: Import data into Power BI and open the Power Query Editor.

Steps:

1. Open Power BI Desktop.

2. Click Home → Get Data → Excel (or choose CSV if the file is in CSV format).

3. Browse and select your dataset file.

4. Click Load to import the data.

5. Click Transform Data to open the Power Query Editor.

Step 2: Rename Columns for Clarity

Objective: Change column names to more readable formats.

M Query Code:

#"Renamed Columns" = Table.RenameColumns(Source, {
    {"Student ID", "ID"},
    {"Math", "Math_Score"},
    {"Science", "Science_Score"},
    {"English", "English_Score"},
    {"History", "History_Score"},
    {"Computer Science", "CS_Score"}
})

Steps 3 to Add a Custom Column in Power Query

Method 1: Using Power Query UI (No Coding)

1. Open Power BI Desktop.

2. Click Transform Data to open the Power Query Editor.

3. Click on the Add Column tab in the ribbon.

4. Click Custom Column.

5. Name the column Total Marks.

6.  Write m Query for Total marks
   

   Using M Query (Code)

   #"Added Custom Column" = Table.AddColumn(#"Changed Type", "Total Marks", 
       each [Math_Score] + [Science_Score] + [English_Score] + [History_Score] + [CS_Score], Int64.Type)

Step 4: Add a Column for Percentage

Objective: Calculate the percentage score of each student.

M Query Code:

 #"Added Percentage Column" = Table.AddColumn(#"Added Custom Column", "Percentage", 

    each ([Total Marks] / 500) * 100, type number)

 Step 5: Create a Grade Column Based on Percentage

Objective: Assign grades based on the percentage obtained.

Grading Criteria:

75% and above → Distinction

60% – 74% → First Class

50% – 59% → Second Class

35% – 49% → Pass

Below 35% → Fail

M Query Code:

#"Added Grade Column" = Table.AddColumn(#"Added Percentage Column", "Grade",

    each if [Percentage] >= 75 then "Distinction"

         else if [Percentage] >= 60 then "First Class"

         else if [Percentage] >= 50 then "Second Class"

         else if [Percentage] >= 35 then "Pass"

         else "Fail", type text)

Step 6: Close & Load Data

Objective: Apply transformations and load data back to Power BI.

Steps:

Click Close & Apply in Power Query Editor.

The transformed dataset is now available for analysis in Power BI.

Generate Power BI Report on Result Data

1. How does student performance vary across different subjects?

Steps to Analyze & Generate Report:

Load Transformed Data into Power BI (Ensure subject-wise scores are present).

Create a Bar Chart

X-Axis: Subject Names (Math, Science, English, etc.)

Y-Axis: Average Scores

Apply Sorting (Descending Order) to visualize highest-to-lowest scores.

Format the Chart (Add titles, data labels, color coding).

Analyze Insights

Identify the highest and lowest scoring subjects.

Compare subject difficulty based on average marks.

 

2️ What is the overall grade distribution among students?

Steps to Analyze & Generate Report:

Load Transformed Data into Power BI (Ensure the "Grade" column is available).

Create a Pie Chart

Values: Count of Students

Legend: Grade Categories (Distinction, First Class, Second Class, Pass, Fail)

Apply Data Labels to display percentage values.

Format the Chart (Use different colors for each grade category).

Analyze Insights

Identify the proportion of students in each grade category.

Determine whether most students pass or fail.

3️  How do total marks correlate with individual subject scores?

Steps to Analyze & Generate Report:

Load Transformed Data into Power BI (Ensure "Total Marks" and

subject scores are available).

Create a Scatter Plot

X-Axis: Subject Scores (e.g., Math_Score)

Y-Axis: Total Marks

Add multiple subject scores as series for comparison.

Apply Trendline to observe relationships between subject

performance and total marks.

Use Correlation Matrix (Optional) to find strong/weak correlations.

Analyze Insights

Identify which subjects contribute the most to total marks.

Determine if weak scores in one subject affect overall performance.