Getting Started With M Language in Power Query | Basic to Advanced

Step-by-Step Tutorial: Getting Started With M Language in Power Query

Part 1: Understanding Syntactical Requirements and Writing M Code

1. Understand the Structure of M Language in Power Query:

   • The M code in Power Query consists of steps that are variables.
   • Each step can hold different values like numbers, tables, functions, etc.
   • Steps should ideally not contain spaces, but if needed, prefix the step name with a hash symbol.

2. Writing M Code in the Advanced Editor:

   • Open the Advanced Editor in the View tab.
   • Create steps in a structured manner using the let and in keywords.
   • Ensure each step ends with a comma except for the last step.

3. Logical Order of Steps:

   • While creating steps, they may or may not follow a logical order.
   • Power Query evaluates the steps from bottom up, not top down.
   • Even if steps are jumbled, the query will still work correctly.

Part 2: Working with Different Objects in M Language

1. Lists in Power Query:

   • Lists are one-column data structures.
   • Define a list manually using curly brackets or extract a list from existing data using column names or functions.

2. Records in Power Query:

   • Records represent rows of data with headers.
   • Extract a record from a table by specifying the row number.
   • Define a record manually by listing column headers and values.

3. Scalar Values in Power Query:

   • Scalar values are single values like numbers or text.
   • Extract a scalar value from a table by referencing the specific cell.
   • Define a scalar value manually by entering the value directly.

4. Functions in Power Query:

   • Functions are pre-built capabilities to transform data.
   • Access functions by typing #shared in a new step.
   • Understand the input and output of functions to use them effectively in queries.

Conclusion:

• Building a strong foundation in M language fundamentals is essential for mastering Power Query.
• Experiment with creating and manipulating lists, records, scalar values, and functions to enhance your data transformation skills.

By following these steps, you can start exploring and utilizing the M language in Power Query effectively.