This tutorial will discuss Power Query functions, keywords, and pre-defined identifiers. You’ll learn what they do and how to use them in developing codes and formulas. You’ll also learn how to access function documentation to get information and examples for your report.
Keywords And Pre-Defined Identifiers
Keywords and Pre-Defined Identifiers can’t be redefined. This is an overview of all the reserved words in the M language.
If you want to give a variable the same name as any of the keywords, you have to use the quoted notations. However, using any of the keywords as a variable must be avoided because it may cause confusion.
This is another overview of the operators and punctuators in the M language.
Power Query Functions
In previous modules, we have talked about Power Query environments. Now, you’ll learn about the Power Query global environment.
To view the global environment, use the intrinsic variable #shared. It returns a record containing all the names and values of all the queries, tables, constants, records, lists, and native and custom M functions in the current file.
It’s also the reason why you can reference one query in the other; each query you create automatically belongs to that global environment.
To demonstrate, create a new blank query. Click New Source in the Home tab and select Blank Query.
Next, input the equal sign followed by #shared. Once done, press Enter. You’ll then see a record in the preview pane.
After that, turn the record into a table.
From there, you can use the Text Filters option to search for specific functional groups. Click the drop-down button in the Name column header and select Text Filters. Then select Begins With.
Power Query Text Functions
If you are interested in Text functions, just enter the word Text and you’ll get an overview of all the Text functions.
If you click the space inside any row of the Value column, you’ll see the documentation of that specific function.
In the Applied Steps pane, rename the Converted To Table and Filtered Rows steps to tshared and Text functions, respectively.
You can also have a quick access to Table or List functions by creating a dummy filter. Using the Text Filters option, enter the keyword Table in the Filter Rows dialogue box and then click OK.
Next, in the formula bar, change the first parameter to shared. You’ll then see all the table functions in the preview pane. Rename the step to Table Functions in the Applied Steps pane.
Power Query List Functions
For the List functions, copy the syntax in the formula bar and create a manual. Click fx and paste the copied syntax. Change the third parameter to List. After that, you’ll see all the list functions.
And again, rename the step to List Functions.
Next, change the Query name to Shared and disable the load.
It can be very convenient to have this kind of setup where you can quickly lookup functions, parameters, and types without leaving the query editor.
If you know the function name and want to review a parameter or an example, just enter the function name in a new blank query without the parentheses. It will show you the internal documentation of the function.
To demonstrate, input Text.Reverse in a new blank query and then click check. You will then see an example of how the function works.
From there, you can also invoke the function. If you input 123 in the parameter and click Invoke, the preview pane will show the numbers in reverse.
Power Query M Function Reference
You can also browse the power query M function reference online. You can either go directly to the website, or you can click the question mark at the top right corner of the Advanced Editor window of any query.
The website has sections for each of the function groups, making it easier to explore.
Another way to access the online documentation is by clicking the hyperlink in the Add Custom Column dialogue box. You need to have a table query to make the Add Custom Column option available.
The hyperlink will bring you to a different page. In this page, there’s a section called See Also where you can find a link to the power query M function reference.
It’s a slight detour if you want to go to the M Function Reference, but it also lists other information you may find useful.
Lastly, if you extend the External Tools tab in the Power BI desktop, you’ll also see the M Reference ribbon. That ribbon isn’t available inside the power query editor. But if you want to browse the M function reference on occasion, it could be convenient to have it inside the Power BI desktop.
***** Related Links *****
DAX Queries Using Variables And Important Functions
Power Query Data Types And Connectors
M Language and Power Query Editor In Power BI
Conclusion
A power query code, syntax, or formula is incomplete without keywords, functions, and identifiers. These entities help get data and information from other sources within a table to provide desirable results. You can use any of them in your data report as long as you use them correctly.
Melissa
