{"id":1712,"date":"2020-08-28T08:13:35","date_gmt":"2020-08-28T08:13:35","guid":{"rendered":"https:\/\/bi.unija.com\/en\/?p=1712"},"modified":"2020-08-28T08:17:15","modified_gmt":"2020-08-28T08:17:15","slug":"power-query-m-functions","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/","title":{"rendered":"Power Query: M functions"},"content":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019).<\/em><\/p>\n

M language enables us to create powerful transformations in Power Query editor. We use custom functions for steps that repeat often in data editing. We already saw how to create custom function in Power Query in Power Query Objects and Dynamic Removal of Rows in Power Query for All the Files in a Folder articles. Let’s look at how to create a function that takes the data from a file and returns an edited table.<\/p>\n

Creating query<\/h1>\n

We often save data in multiple, almost identical fields. In cases like this it is useful to edit a single file, save the steps into a function and use it to edit the all the other files.<\/p>\n

In our example we’re dealing with multiple files containing online ads of used vehicles. We use the following steps.<\/p>\n

1. Import data to Power BI with Get data<\/strong>.<\/p>\n

2. Select Transform Data<\/strong>.<\/p>\n

<\/p>\n

3. A file preview is opened in Power Query.<\/p>\n

<\/p>\n

4. Edit the table. In our case we remove redundant column with Remove Columns <\/strong>and change the column order.<\/p>\n

<\/p>\n

Query is now ready to be turned into a function.<\/p>\n

Creating a function<\/h1>\n

We turn a query into a function. Function can then be used on all the remaining files. We use the following steps.<\/p>\n

1. Select the query, in our case named fn<\/strong>.<\/p>\n

2. Open Advanced Editor<\/strong>.<\/p>\n

<\/p>\n

3. To the start of the code we add (SourceFileName)=><\/strong>. We replace name of the file with SourceFileName <\/strong>all throughout the code. This is now the input of the function.<\/p>\n

<\/p>\n

4. Confirm with Done<\/strong>.<\/p>\n

5. Query is now a function. Icon of the query changes. Name is now written in italic letters, meaning the query will not load to data model.<\/p>\n

<\/p>\n

Calling the function<\/h1>\n

We can now use the function to edit the remaining files. We enter the full path to the next file and confirm with OK<\/strong>. Function edits the data and returns the finished table.<\/p>\n

<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019). M language enables us to create powerful transformations in Power Query editor. We use custom functions for steps that repeat often in data editing. We already saw how to create custom function in<\/p>\n","protected":false},"author":5,"featured_media":1720,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[4],"tags":[7,9],"class_list":["post-1712","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-advanced","tag-m","tag-power-query"],"acf":[],"yoast_head":"\nPower Query: M functions - Bi Unija<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Power Query: M functions - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"For Power BI version 2.76.5678.782 (December 2019). M language enables us to create powerful transformations in Power Query editor. We use custom functions for steps that repeat often in data editing. We already saw how to create custom function in\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-08-28T08:13:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-28T08:17:15+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/08\/21eng.png\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Branka Trifunovi\u0107\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Branka Trifunovi\u0107\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/\",\"url\":\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/\",\"name\":\"Power Query: M functions - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/08\/21eng.png\",\"datePublished\":\"2020-08-28T08:13:35+00:00\",\"dateModified\":\"2020-08-28T08:17:15+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/08\/21eng.png\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/08\/21eng.png\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Power Query: M functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bi.unija.com\/en\/#website\",\"url\":\"https:\/\/bi.unija.com\/en\/\",\"name\":\"Bi Unija\",\"description\":\"Just another Bi site\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bi.unija.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\",\"name\":\"Branka Trifunovi\u0107\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"caption\":\"Branka Trifunovi\u0107\"},\"url\":\"https:\/\/bi.unija.com\/en\/author\/branka\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Power Query: M functions - Bi Unija","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/","og_locale":"en_US","og_type":"article","og_title":"Power Query: M functions - Bi Unija","og_description":"For Power BI version 2.76.5678.782 (December 2019). M language enables us to create powerful transformations in Power Query editor. We use custom functions for steps that repeat often in data editing. We already saw how to create custom function in","og_url":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/","og_site_name":"Bi Unija","article_published_time":"2020-08-28T08:13:35+00:00","article_modified_time":"2020-08-28T08:17:15+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/08\/21eng.png","type":"image\/png"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/","url":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/","name":"Power Query: M functions - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/08\/21eng.png","datePublished":"2020-08-28T08:13:35+00:00","dateModified":"2020-08-28T08:17:15+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/power-query-m-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/08\/21eng.png","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/08\/21eng.png","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/power-query-m-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"Power Query: M functions"}]},{"@type":"WebSite","@id":"https:\/\/bi.unija.com\/en\/#website","url":"https:\/\/bi.unija.com\/en\/","name":"Bi Unija","description":"Just another Bi site","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bi.unija.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10","name":"Branka Trifunovi\u0107","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","caption":"Branka Trifunovi\u0107"},"url":"https:\/\/bi.unija.com\/en\/author\/branka\/"}]}},"_links":{"self":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1712"}],"collection":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/comments?post=1712"}],"version-history":[{"count":3,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1712\/revisions"}],"predecessor-version":[{"id":1723,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1712\/revisions\/1723"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1720"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1712"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1712"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1712"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}