How to Check Power Query M Function Compatibility in Excel
Question details
The user needs to list the Power Query M functions available in the Excel version where a query is running.
- Product
- Microsoft Excel (Power Query)
- Device & OS
- not provided
- Scenario
- A user wants to verify whether an M function can be used in their current Excel build before writing or troubleshooting a Power Query query.
- Observed behavior
- The expected result is a sorted list of functions available in the current Power Query environment, not a complete version history.
Open the workbook in the Excel version you want to check and make sure the Power Query Editor is available from the Data tab. An empty query is sufficient to run the #shared inspection.
List Available Power Query M Functions with the #shared Library
Use this method when you need a current-environment function inventory or need to check whether a specific M function is available before using it.
The #shared record contains built-in and environment-specific functions. Removing workbook query fields and selecting only function values returns a clean list from the currently running Power Query engine.
In Excel, go to the Data tab and click Get Data > Launch Power Query Editor. If you already have a query open, open the Power Query Editor directly.
In the Power Query Editor, click New Source > Blank Query or right-click in the Queries pane and choose New Query > Blank Query.
With the blank query selected, click Home > Advanced Editor to open the M code window.
Replace the existing code with: let Source=Record.ToTable(Record.RemoveFields(#shared,Record.FieldNames(#sections[Section1]))), Selected=Table.SelectRows(Source, each [Value] is function), Names=Table.SelectColumns(Selected,{"Name"}), Sorted=Table.Sort(Names,{{"Name",Order.Ascending}}) in Sorted. Then click Done.
The query returns a table sorted by Name. Click Close & Load to load the list into a worksheet, or inspect it in the data preview.
Check a Specific M Function Name Directly
Use a short #shared query when you only need to confirm whether one M function exists in the current environment.
Use WPS Office to Open and Edit Excel Workbooks Free
WPS Office is a free, lightweight Microsoft Office alternative that opens Excel files with high format compatibility, a familiar ribbon UI, and smooth migration for everyday spreadsheet tasks. While Power Query M function compatibility checks run in Microsoft Excel's Power Query environment, you can use WPS Office to open, review, edit, and share the workbook results without a subscription.
Frequently Asked Questions
Does #shared show every M function from all Excel versions?
No. The #shared query lists functions available in the current Power Query engine where the query is executed. It does not provide a complete version history or include functions from other Excel builds.
Where do I paste the M code to get the function list?
Open the Power Query Editor in Excel, create a blank query, then click Home > Advanced Editor. Paste the #shared M code into the Advanced Editor and click Done.
Can I check a specific M function without generating the full list?
Yes. Create a blank query and use a short test such as let exists = Record.HasFields(#shared, "Text.Combine") in exists. Replace Text.Combine with the function name you want to check.
Why do I see fewer functions than someone on another Excel build?
Function availability depends on the Power Query engine version, Excel update channel, installed connectors, and environment. The same query can return different lists on different builds.
Does WPS Office include the same Power Query M environment?
WPS Office is a free, lightweight spreadsheet alternative for opening and editing Excel workbooks. For native Power Query M environment checks, use Microsoft Excel because the M engine version determines which functions are listed.
