How to Use the Hash Symbol and Each Keyword in Power Query M
Question details
The user wants to understand the purpose and correct usage of the hash symbol (#) and the 'each' keyword within the Power Query M formula language.
- Product
- Power Query
- Device & OS
- not provided
- Scenario
- Writing or troubleshooting M code in the Advanced Editor, creating custom columns, or renaming query steps.
- Observed behavior
- Seeking clarification on when the hash symbol is required for identifiers and built-in functions, and how the 'each' keyword processes table rows.
Open the Advanced Editor in your Power Query window to view the raw M code. This will allow you to see exactly where the hash symbol and 'each' keywords are currently being applied in your data transformation steps.
Using the Hash Symbol (#) for Identifiers and Functions
The hash symbol is required in Power Query M to reference step names containing spaces or special characters, as well as to call specific built-in intrinsic values.
In Power Query M, variables and step names cannot normally contain spaces. The hash symbol, combined with double quotes, acts as an escape sequence that allows spaces and special characters inside step identifiers.
If your applied step name has spaces (e.g., Added Custom), you must format it with a hash and quotes. In the Advanced Editor, reference it as #"Added Custom".
For step names without spaces (e.g., CustomStep1), the hash symbol and quotes are not strictly necessary. You can refer to the step simply by its name, though the Advanced Editor often auto-generates them anyway.
Use the hash symbol to call specific M language intrinsic values. Common examples include #datetime, #date, #table, and #shared. For instance, to define a date, use #date(2023, 10, 1).
Applying the 'each' Keyword for Row-Level Expressions
The 'each' keyword is used to apply a formula or function to every single row within a table or list.
Use WPS Office for Streamlined Data Analysis
While Power Query M is specific to Microsoft's ecosystem, WPS Office provides a lightweight, highly compatible, and free alternative for complex data processing, pivot tables, and data visualization. It supports standard Excel formats seamlessly without the heavy system requirements.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Install and Launch: Follow the simple installation wizard, then open WPS Spreadsheet.
- 3. Open your files: Drag and drop your existing .xlsx workbooks into WPS Spreadsheet to continue your data analysis without losing any formatting.
Frequently Asked Questions
Why do some Power Query step names have a hash symbol and quotes while others do not?
Step names that contain spaces or special characters require the hash symbol and double quotes (e.g., #"Filtered Rows") so the M engine can correctly parse the identifier as a single entity. Single-word step names without special characters do not strictly require this syntax.
Can I use the 'each' keyword without the underscore (_) symbol?
Yes. When you reference columns within an 'each' statement (like 'each [ColumnName]'), the underscore is implicitly understood as the current row record. You only need to explicitly write the underscore if you are passing the entire row record to another function.
What does #shared mean in Power Query?
#shared is a special intrinsic record in the Power Query M language that returns a list of all the functions, variables, and enumerations currently available in your environment. It is an excellent tool for exploring built-in M functions directly in the editor.
