Creating Functions in M, The Power Query Formula Language

functions

You have to be careful with economic data (with economists, too, but that’s an entirely different story). Is the data seasonally adjusted? Do the numbers represent current values or have they been adjusted to reflect the dollar’s average value in 2010? For such reasons, it is not uncommon for economists to annotate tables of data. An excellent tool for making such annotations is Excel’s commenting feature. Sadly, published data commonly contains notes right in cells and tables that interfere with the data’s direct import into Excel or Power BI.

Power Query can, of course, clean and import such data. But if the similar problems arise again and again, a more efficient reusable solution might become very attractive. The Power Query Formula Language (known as M only informally, as Microsoft constantly reminds us) allows us to create functions as well as queries. Since these functions can accept different parameter values at runtime, they are very flexible. Vary the argument and vary the behavior of the function.

For our example we will choose a very simple text file that has been annotated

annotated data

For those interested in such things, these numbers represent monthly change in the wages of employees in the private sector. Obviously, the problem for us is the (P) annotation. (The data for these months was only partially complete when the results were tabulated.)

We shall start building our solution using classic Power Query menu options. Each step is straightforward.

1) Reading the data will result in most columns being the decimal number datatype, but columns containing annotations will be read as text.

2) Converting the entire data matrix to a text array will permit the use of the Table.ReplaceValue method.

3) After the annotations are replaced with empty text, we must return the table data to a numeric datatype.

When we are finished, Power Query will have recorded some ponderous M code

let

Source = Csv.Document(File.Contents(“E:\Knowledgebase\Excel\_Documents\_Blog\20160926-IntroductionToM\SeriesReport-20160929125851_79e60f.txt”),[Delimiter=” “, Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),

#”Promoted Headers” = Table.PromoteHeaders(Source),

#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Year”, Int64.Type}, {“Jan”, type text}, {“Feb”, type text}, {“Mar”, type text}, {“Apr”, type text}, {“May”, type text}, {“Jun”, type text}, {“Jul”, type text}, {“Aug”, type text}, {“Sep”, type text}, {“Oct”, type text}, {“Nov”, type text}, {“Dec”, type text}}),

#”Replaced Value” = Table.ReplaceValue(#”Changed Type”,”(P)”,””,Replacer.ReplaceText,{“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Replaced Value”,{{“Jan”, type number}, {“Feb”, type number}, {“Mar”, type number}, {“Apr”, type number}, {“May”, type number}, {“Jun”, type number}, {“Jul”, type number}, {“Aug”, type number}, {“Sep”, type number}, {“Oct”, type number}, {“Nov”, type number}, {“Dec”, type number}})

in

#”Changed Type1″

Because of the potential width limitations within which you might be reading this code, it may likely not appear formatted as it would within the Advanced Editor. It is worth mentioning that it is only the comma at the end that determines a “line” of code. M code can contain whitespace characters like tabs and carriage returns; use these to format the code to make it more readable. Of course, the whitespace characters used for formatting cannot be within any literal strings defined in the M formulae.

Fortunately, we need only consider one very small bit of this code, the string annotation we need to eliminate (In this example “(P)”.

let

Source = Csv.Document(File.Contents(“E:\Knowledgebase\Excel\_Documents\_Blog\20160926-IntroductionToM\SeriesReport-20160929125851_79e60f.txt”),[Delimiter=” “, Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),

#”Promoted Headers” = Table.PromoteHeaders(Source),

#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Year”, Int64.Type}, {“Jan”, type text}, {“Feb”, type text}, {“Mar”, type text}, {“Apr”, type text}, {“May”, type text}, {“Jun”, type text}, {“Jul”, type text}, {“Aug”, type text}, {“Sep”, type text}, {“Oct”, type text}, {“Nov”, type text}, {“Dec”, type text}}),

#”Replaced Value” = Table.ReplaceValue(#”Changed Type”,“(P)”,””,Replacer.ReplaceText,{“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Replaced Value”,{{“Jan”, type number}, {“Feb”, type number}, {“Mar”, type number}, {“Apr”, type number}, {“May”, type number}, {“Jun”, type number}, {“Jul”, type number}, {“Aug”, type number}, {“Sep”, type number}, {“Oct”, type number}, {“Nov”, type number}, {“Dec”, type number}})

in

#”Changed Type1″

Future imports may involve different annotations, so our query might be far more useful if the annotation were not inflexibly hard-coded as it is here.

In M, defining a parameter will automatically create a function

(unwantedCharacters as text) =>

let

Source = Csv.Document(File.Contents(“E:\Knowledgebase\Excel\_Documents\_Blog\20160926-IntroductionToM\SeriesReport-20160929125851_79e60f.txt”),[Delimiter=” “, Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),

#”Promoted Headers” = Table.PromoteHeaders(Source),

#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Year”, Int64.Type}, {“Jan”, type text}, {“Feb”, type text}, {“Mar”, type text}, {“Apr”, type text}, {“May”, type text}, {“Jun”, type text}, {“Jul”, type text}, {“Aug”, type text}, {“Sep”, type text}, {“Oct”, type text}, {“Nov”, type text}, {“Dec”, type text}}),

#”Replaced Value” = Table.ReplaceValue(#”Changed Type”,unwantedCharacters,””,Replacer.ReplaceText,{“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Replaced Value”,{{“Jan”, type number}, {“Feb”, type number}, {“Mar”, type number}, {“Apr”, type number}, {“May”, type number}, {“Jun”, type number}, {“Jul”, type number}, {“Aug”, type number}, {“Sep”, type number}, {“Oct”, type number}, {“Nov”, type number}, {“Dec”, type number}})

in

#”Changed Type1″

The Power Query editor adjusts in response to the fact that the query is now a function and must be invoked with a value for the parameter.

assignargument

It is easy to illustrate that invoking the new function with a value that doesn’t match the annotation results in an error; if we enter “ABC” then the text “(P)” is not removed and that column cannot be converted into a numeric datatype. Supplying “(P)”, the correct match for the annotation, performs correctly.

xyz

p

Conclusion

As with most programming languages and environment, creating functions permits better code organization and code reuse. Creating functions from automatically generated M scripts is especially easy.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.