{"id":176488,"date":"2024-01-17T14:11:27","date_gmt":"2024-01-17T13:11:27","guid":{"rendered":"https:\/\/datascientest.com\/en\/?p=176488"},"modified":"2024-01-16T19:15:53","modified_gmt":"2024-01-16T18:15:53","slug":"formulas-in-power-query-formulas-the-best-of-them","status":"publish","type":"post","link":"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them","title":{"rendered":"Formulas in Power Query formulas: The best of them"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"176488\" class=\"elementor elementor-176488\" data-elementor-post-type=\"post\">\n\t\t\t\t\t\t\t<div class=\"elementor-element elementor-element-25d67ab e-flex e-con-boxed e-con\" data-id=\"25d67ab\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-e3f5903 elementor-widget elementor-widget-heading\" data-id=\"e3f5903\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<style>\/*! elementor - v3.15.0 - 02-08-2023 *\/\n.elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-heading .elementor-heading-title[class*=elementor-size-]>a{color:inherit;font-size:inherit;line-height:inherit}.elementor-widget-heading .elementor-heading-title.elementor-size-small{font-size:15px}.elementor-widget-heading .elementor-heading-title.elementor-size-medium{font-size:19px}.elementor-widget-heading .elementor-heading-title.elementor-size-large{font-size:29px}.elementor-widget-heading .elementor-heading-title.elementor-size-xl{font-size:39px}.elementor-widget-heading .elementor-heading-title.elementor-size-xxl{font-size:59px}<\/style><h2 class=\"elementor-heading-title elementor-size-default\">Power Query formulas offer a wide range of possibilities for manipulating data in Excel and Power Query. Find out all you need to know about the different types and how to use them...<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9bc97df elementor-widget elementor-widget-text-editor\" data-id=\"9bc97df\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<style>\/*! elementor - v3.15.0 - 02-08-2023 *\/\n.elementor-widget-text-editor.elementor-drop-cap-view-stacked .elementor-drop-cap{background-color:#69727d;color:#fff}.elementor-widget-text-editor.elementor-drop-cap-view-framed .elementor-drop-cap{color:#69727d;border:3px solid;background-color:transparent}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap{margin-top:8px}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap-letter{width:1em;height:1em}.elementor-widget-text-editor .elementor-drop-cap{float:left;text-align:center;line-height:1;font-size:50px}.elementor-widget-text-editor .elementor-drop-cap-letter{display:inline-block}<\/style>\t\t\t\t<p>With <strong>Power Query, Excel and Power BI<\/strong> users can transform, cleanse, combine and query data from multiple sources.<\/p><p>Its intuitive interface makes data analysis accessible to as many people as possible. The query editor requires no coding, and this is what <a href=\"https:\/\/datascientest.com\/en\/power-bi-microsofts-business-intelligence-solution\">makes Business Intelligence so accessible.<\/a><\/p><p>The tool even offers automatic <a href=\"https:\/\/datascientest.com\/en\/data-cleaning-definition-methods-and-relevance-in-data-science\">data cleansing<\/a> tools for detecting and <strong>removing duplicates,<\/strong> correcting data errors, deleting spaces or normalizing dates.<\/p><p>What&#8217;s more,<strong> Power Query<\/strong> also offers automatic data cleansing tools such as duplicate detection and removal, data error correction, space removal, date normalization and more. These tools enable users to clean up data without having to write formulas.<\/p><p>However, to manipulate data more efficiently and accurately, advanced users can use Power Query formulas. These are written in the <strong>M language.<\/strong><\/p><p>These formulas can be used to perform calculations, manipulate text, aggregate imported data or perform other operations such as merging, filtering, sorting or formatting.<\/p><p>Powerful and flexible, <strong>Power Query formulas<\/strong> enable you to customize data transformations to suit your specific needs. In this article, you&#8217;ll discover the different categories and how they work!<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-8c455e8 e-flex e-con-boxed e-con\" data-id=\"8c455e8\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-03e7f6c elementor-widget elementor-widget-heading\" data-id=\"03e7f6c\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">What is the M language?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-35f01d0 elementor-widget elementor-widget-image\" data-id=\"35f01d0\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<style>\/*! elementor - v3.15.0 - 02-08-2023 *\/\n.elementor-widget-image{text-align:center}.elementor-widget-image a{display:inline-block}.elementor-widget-image a img[src$=\".svg\"]{width:48px}.elementor-widget-image img{vertical-align:middle;display:inline-block}<\/style>\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" src=\"https:\/\/datascientest.com\/wp-content\/uploads\/2023\/03\/langage-M-power-query.jpg\" title=\"\" alt=\"\" loading=\"lazy\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\"><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-fca9d99 elementor-widget elementor-widget-text-editor\" data-id=\"fca9d99\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>The <strong>M or Power Query Formula Language<\/strong> is used in Power Query to describe data transformation steps. It is a functional language, based on functions that take arguments and return values.<\/p><p>Power Query uses this language to write formulas: expressions describing the data transformations to be performed. It&#8217;s<a href=\"https:\/\/datascientest.com\/en\/etl-or-extract-transform-load-definition-and-use\"> a powerful tool for ETL and data analysis.<\/a><\/p><p>Its syntax is simple and intuitive, making it easy to learn for people already familiar with other programming languages.<\/p><p>The various <strong>M language<\/strong> functions are organized in libraries, grouped by theme and category.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-cd2a267 e-flex e-con-boxed e-con\" data-id=\"cd2a267\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-22799b4 elementor-widget elementor-widget-heading\" data-id=\"22799b4\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">What are the text transformation formulas?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-61d259a elementor-widget elementor-widget-text-editor\" data-id=\"61d259a\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Text transformation formulas such as LEFT, RIGHT, UPPER, LOWER, PROPER, SUBSTITUTE, TRIM, CONCATENATE and LEN are used to clean up and<strong> transform text data.<\/strong><\/p><p>They make it easy to manipulate character strings such as names, addresses and telephone numbers. It is also possible to remove unwanted characters such as spaces, hyphens or parentheses.<\/p><p>For example, if a list of names contains unnecessary spaces, the TRIM formula can be used to remove them. Similarly, the formula <strong>SUBSTITUTE<\/strong> can be used to delete all spaces directly.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9264a9a elementor-align-center button-principale elementor-widget elementor-widget-button\" data-id=\"9264a9a\" data-element_type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm elementor-animation-grow\" href=\"https:\/\/datascientest.com\/en\/power-bi-training\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t<span class=\"elementor-button-text\">Learn to use Power Query formulas<\/span>\n\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-b380b6f e-flex e-con-boxed e-con\" data-id=\"b380b6f\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-59ef22f elementor-widget elementor-widget-heading\" data-id=\"59ef22f\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">Date transformation formulas<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9168f86 elementor-widget elementor-widget-text-editor\" data-id=\"9168f86\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>To manipulate date and time data, we use date transformation formulas such as DATE, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, NOW and TODAY.<\/p><p>These formulas are used to easily manipulate time data, or transform it into <strong>different formats as required.<\/strong><\/p><p>For example, if you have a list of dates in different formats, the DATEVALUE formula can be used to transform them into a standard format.<\/p><p>Another use case is to extract the month from each of these dates. Simply use the <strong>MONTH formula.<\/strong><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-6b75341 e-flex e-con-boxed e-con\" data-id=\"6b75341\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-4796893 elementor-widget elementor-widget-heading\" data-id=\"4796893\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">Number transformation formulas<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-2bae7e1 elementor-widget elementor-widget-text-editor\" data-id=\"2bae7e1\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Numerical data manipulation is performed using Power Query number transformation formulas such as <strong>SUM, AVERAGE, MIN, MAX, COUNT and IF.<\/strong><\/p><p>The aim is to calculate basic statistics, or to perform mathematical operations on numerical data.<\/p><p>Let&#8217;s take the example of a price list containing a column for each product. The SUM formula can be applied to calculate total sales for each product.<\/p><p>And to extract price data above a certain value, nothing could be simpler: you can use the IF formula.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-4dd43a4 elementor-widget elementor-widget-image\" data-id=\"4dd43a4\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" src=\"https:\/\/datascientest.com\/wp-content\/uploads\/2023\/03\/formules-power-query-nombres.png\" title=\"\" alt=\"\" loading=\"lazy\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\"><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-186494c e-flex e-con-boxed e-con\" data-id=\"186494c\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-2a547f8 elementor-widget elementor-widget-heading\" data-id=\"2a547f8\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">Search and filter formulas<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-5163212 elementor-widget elementor-widget-text-editor\" data-id=\"5163212\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Within a list of data, it is sometimes necessary to extract specific information. For such tasks, you can use search and filter formulas such as VLOOKUP, HLOOKUP, INDEX, MATCH, FILTER and SORT.<\/p><p>They enable you to quickly find specific information in a table or<a href=\"https:\/\/datascientest.com\/en\/datasets-top-5-places-to-find-quality-datasets\"> other data set.<\/a><\/p><p>For example, on a list of product names, the VLOOKUP formula can be used to find the price of each one. SORT can also be used to sort the list alphabetically.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-cd48710 e-flex e-con-boxed e-con\" data-id=\"cd48710\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-d35751d elementor-widget elementor-widget-heading\" data-id=\"d35751d\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">Merge and join formulas<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-85c3f26 elementor-widget elementor-widget-text-editor\" data-id=\"85c3f26\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>To combine multiple data from different sources, you can use merge and join formulas such as JOIN, MERGE, APPEND and UNION.<\/p><p>This makes it easy to bring all data together in a single table. It&#8217;s the ideal way to get a complete overview.<\/p><p>Let&#8217;s take the example of a list of sales from many different regions. To get a clearer picture, you can use the MERGE formula to combine all sales into a single table. With the APPEND formula, you can add a new column to this table.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-cab2833 elementor-align-center button-principale elementor-widget elementor-widget-button\" data-id=\"cab2833\" data-element_type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm elementor-animation-grow\" href=\"https:\/\/datascientest.com\/en\/power-bi-training\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t<span class=\"elementor-button-text\">Mastering Power Query formulas<\/span>\n\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-35ed8a2 e-flex e-con-boxed e-con\" data-id=\"35ed8a2\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-1407692 elementor-widget elementor-widget-heading\" data-id=\"1407692\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">Pivot table formulas<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-300012b elementor-widget elementor-widget-text-editor\" data-id=\"300012b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Pivot tables are commonly used for data analysis in Excel and Power BI.<\/p><p>However, it is possible to create such tables from large quantities of data using formulas such as <a href=\"https:\/\/datascientest.com\/en\/excel-to-power-bi-how-to-transform-a-pivot-table-in-excel-into-a-dataset-that-can-be-used-by-power-bi\">PIVOT<\/a>, GROUP BY or AGGREGATE.<\/p><p>This can be useful, for example, if you have a large sales list and want to create a table to view sales by region. In this case, simply use the PIVOT formula. Similarly, the GROUP BY formula can be used to group sales by month.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-4c9d43d e-flex e-con-boxed e-con\" data-id=\"4c9d43d\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-93f85b2 elementor-widget elementor-widget-heading\" data-id=\"93f85b2\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">How to use Power Query formulas<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-dfb9383 elementor-widget elementor-widget-text-editor\" data-id=\"dfb9383\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Here&#8217;s how to use <strong>Power Query formulas<\/strong>. First, open the <a href=\"https:\/\/datascientest.com\/en\/power-query-vba-how-does-it-work\">Power Query editor in Excel<\/a> or Power BI.<\/p><p>You can then load the data to be transformed using one of the loading options <strong>offered by Power Query<\/strong>, for example from a file or data source.<\/p><p>To add a transformation step using a formula, click on the &#8220;Add Step&#8221; icon in the <strong>Power Query ribbon.<\/strong><\/p><p>In the &#8220;Formula&#8221; window, you can enter your formula using the M language. It can be based on existing columns in the dataset, but it is also possible to create new columns.<\/p><p>Once you&#8217;ve added the formula, click on &#8220;OK&#8221; to <strong>apply it to the dataset.<\/strong> You can then preview the results of the transformation in the <a href=\"https:\/\/datascientest.com\/en\/power-query-what-is-it-whats-it-for\">Power Query window.<\/a> All that&#8217;s left to do is save and load the data!<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-7a7b366 elementor-widget elementor-widget-image\" data-id=\"7a7b366\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" src=\"https:\/\/datascientest.com\/wp-content\/uploads\/2023\/03\/formules-power-query.png\" title=\"\" alt=\"\" loading=\"lazy\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\"><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-7b52559 e-flex e-con-boxed e-con\" data-id=\"7b52559\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-1cbde25 elementor-widget elementor-widget-heading\" data-id=\"1cbde25\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">Conclusion: M formulas extend the possibilities of Power Query<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-f85edc6 elementor-widget elementor-widget-text-editor\" data-id=\"f85edc6\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Although it is possible to use the graphical interface of the <strong>Power Query editor, M-language formulas<\/strong> offer more possibilities.<\/p><p>However, to use them, technical skills are required. To acquire them, DataScientest training courses are the right choice.<\/p><p>Our dedicated <strong>Power BI course<\/strong> enables you to master the tool in just 5 days. You&#8217;ll learn about Power Query, the <a href=\"https:\/\/datascientest.com\/en\/dax-power-bi-all-about-the-query-programming-language\">DAX language<\/a> and DataViz, as well as more advanced concepts such as DataFlows and incremental updating.<\/p><p>By the end of the course, you&#8217;ll be able to use Power BI to collect and analyze data and create interactive dashboards.<\/p><p>As a Microsoft Learning Partner, our organization also prepares you for the <a href=\"https:\/\/datascientest.com\/en\/pl-300-how-to-obtain-power-bi-analyst-associate-certification\">PL-300 exam<\/a>, leading to Power BI Data Analyst Associate certification.<\/p><p>All our training courses are carried out entirely online and remotely, and are eligible for funding options. Discover DataScientest!<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c211b24 elementor-align-center button-principale elementor-widget elementor-widget-button\" data-id=\"c211b24\" data-element_type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm elementor-animation-grow\" href=\"https:\/\/datascientest.com\/en\/power-bi-training\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t<span class=\"elementor-button-text\">Register for Power BI training<\/span>\n\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-76b4b29 elementor-widget elementor-widget-text-editor\" data-id=\"76b4b29\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Now you know all about Power Query formulas. For more information on the same subject, take a look at our complete <a href=\"https:\/\/datascientest.com\/en\/power-query-what-is-it-whats-it-for\">guide to Power Query<\/a> and our <a href=\"https:\/\/datascientest.com\/en\/power-bi-microsofts-business-intelligence-solution\">dossier on Power BI.<\/a><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-2b6a7e4 e-flex e-con-boxed e-con\" data-id=\"2b6a7e4\" data-element_type=\"container\" data-settings=\"{&quot;content_width&quot;:&quot;boxed&quot;}\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-4df7ac2 elementor-widget elementor-widget-html\" data-id=\"4df7ac2\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<script type=\"application\/ld+json\">\n{\n  \"@context\": \"https:\/\/schema.org\",\n  \"@type\": \"FAQPage\",\n  \"mainEntity\": [{\n    \"@type\": \"Question\",\n    \"name\": \"What is the M language?\",\n    \"acceptedAnswer\": {\n      \"@type\": \"Answer\",\n      \"text\": \"The M programming language, or Power Query Formula Language, is used in Power Query to describe data transformation steps. It is a functional language, i.e. based on functions taking arguments and returning values.\"\n    }\n  },{\n    \"@type\": \"Question\",\n    \"name\": \"What are the text transformation formulas?\",\n    \"acceptedAnswer\": {\n      \"@type\": \"Answer\",\n      \"text\": \"To clean up and transform text data, we use text transformation formulas such as LEFT, RIGHT, UPPER, LOWER, PROPER, SUBSTITUTE, TRIM, CONCATENATE and LEN. They make it easy to manipulate character strings such as names, addresses and telephone numbers. It's also possible to remove unwanted characters such as spaces, hyphens or parentheses.\"\n    }\n  },{\n    \"@type\": \"Question\",\n    \"name\": \"How do I use Power Query formulas?\",\n    \"acceptedAnswer\": {\n      \"@type\": \"Answer\",\n      \"text\": \"To use Power Query formulas, follow these steps. First open the Power Query editor in Excel or Power BI. You can then load the data to be transformed using one of the loading options offered by Power Query, for example from a file or data source.\"\n    }\n  }]\n}\n<\/script>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Power Query formulas offer a wide range of possibilities for manipulating data in Excel and Power Query. Find out all you need to know about the different types and how to use them&#8230; With Power Query, Excel and Power BI users can transform, cleanse, combine and query data from multiple sources. Its intuitive interface makes [&hellip;]<\/p>\n","protected":false},"author":67,"featured_media":176489,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"content-type":"","inline_featured_image":false},"categories":[2420],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v21.7) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Formulas in Power Query formulas: The best of them<\/title>\n<meta name=\"description\" content=\"Power Query is a powerful tool with a wide range of capabilities and specific Power Query formulas that can lead to...\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Formulas in Power Query formulas: The best of them\" \/>\n<meta property=\"og:description\" content=\"Power Query formulas offer a wide range of possibilities for manipulating data in Excel and Power Query. Find out all you need to know about the different\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them\" \/>\n<meta property=\"og:site_name\" content=\"Data Science Courses | DataScientest\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataScientestEN\" \/>\n<meta property=\"article:published_time\" content=\"2024-01-17T13:11:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-16T18:15:53+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/datascientest.com\/en\/files\/2024\/01\/formeln-power-query.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"600\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/webp\" \/>\n<meta name=\"author\" content=\"Melanie\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataScientest__\" \/>\n<meta name=\"twitter:site\" content=\"@DataScientest__\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Melanie\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them#article\",\"isPartOf\":{\"@id\":\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them\"},\"author\":{\"name\":\"Melanie\",\"@id\":\"https:\/\/datascientest.com\/en\/#\/schema\/person\/19d5f7ade9407eb69af3bf6d1ae1731d\"},\"headline\":\"Formulas in Power Query formulas: The best of them\",\"datePublished\":\"2024-01-17T13:11:27+00:00\",\"dateModified\":\"2024-01-16T18:15:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them\"},\"wordCount\":1165,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/datascientest.com\/en\/#organization\"},\"articleSection\":[\"Business Intelligence\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them#respond\"]}],\"copyrightYear\":\"2024\",\"copyrightHolder\":{\"@id\":\"https:\/\/datascientest.com\/#organization\"}},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them\",\"url\":\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them\",\"name\":\"Formulas in Power Query formulas: The best of them\",\"isPartOf\":{\"@id\":\"https:\/\/datascientest.com\/en\/#website\"},\"datePublished\":\"2024-01-17T13:11:27+00:00\",\"dateModified\":\"2024-01-16T18:15:53+00:00\",\"description\":\"Power Query is a powerful tool with a wide range of capabilities and specific Power Query formulas that can lead to...\",\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them\"]}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/datascientest.com\/en\/#website\",\"url\":\"https:\/\/datascientest.com\/en\/\",\"name\":\"Data Science Courses | DataScientest\",\"description\":\"Leader in Data Science Courses\",\"publisher\":{\"@id\":\"https:\/\/datascientest.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/datascientest.com\/en\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/datascientest.com\/en\/#organization\",\"name\":\"DataScientest\",\"url\":\"https:\/\/datascientest.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/datascientest.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/datascientest.com\/en\/wp-content\/blogs.dir\/9\/files\/2024\/02\/DataScientest_icon_blue-2.png\",\"contentUrl\":\"https:\/\/datascientest.com\/en\/wp-content\/blogs.dir\/9\/files\/2024\/02\/DataScientest_icon_blue-2.png\",\"width\":301,\"height\":301,\"caption\":\"DataScientest\"},\"image\":{\"@id\":\"https:\/\/datascientest.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/DataScientestEN\",\"https:\/\/twitter.com\/DataScientest__\",\"https:\/\/www.linkedin.com\/school\/datascientest\/mycompany\/\",\"https:\/\/www.tiktok.com\/@datascientest\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/datascientest.com\/en\/#\/schema\/person\/19d5f7ade9407eb69af3bf6d1ae1731d\",\"name\":\"Melanie\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/datascientest.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8d3d660a0698de06f13ecf4823519da1?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8d3d660a0698de06f13ecf4823519da1?s=96&d=mm&r=g\",\"caption\":\"Melanie\"},\"url\":\"https:\/\/datascientest.com\/en\/author\/melaniekrause\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Formulas in Power Query formulas: The best of them","description":"Power Query is a powerful tool with a wide range of capabilities and specific Power Query formulas that can lead to...","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:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them","og_locale":"en_GB","og_type":"article","og_title":"Formulas in Power Query formulas: The best of them","og_description":"Power Query formulas offer a wide range of possibilities for manipulating data in Excel and Power Query. Find out all you need to know about the different","og_url":"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them","og_site_name":"Data Science Courses | DataScientest","article_publisher":"https:\/\/www.facebook.com\/DataScientestEN","article_published_time":"2024-01-17T13:11:27+00:00","article_modified_time":"2024-01-16T18:15:53+00:00","og_image":[{"width":1200,"height":600,"url":"https:\/\/datascientest.com\/en\/files\/2024\/01\/formeln-power-query.webp","type":"image\/webp"}],"author":"Melanie","twitter_card":"summary_large_image","twitter_creator":"@DataScientest__","twitter_site":"@DataScientest__","twitter_misc":{"Written by":"Melanie","Estimated reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them#article","isPartOf":{"@id":"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them"},"author":{"name":"Melanie","@id":"https:\/\/datascientest.com\/en\/#\/schema\/person\/19d5f7ade9407eb69af3bf6d1ae1731d"},"headline":"Formulas in Power Query formulas: The best of them","datePublished":"2024-01-17T13:11:27+00:00","dateModified":"2024-01-16T18:15:53+00:00","mainEntityOfPage":{"@id":"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them"},"wordCount":1165,"commentCount":0,"publisher":{"@id":"https:\/\/datascientest.com\/en\/#organization"},"articleSection":["Business Intelligence"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them#respond"]}],"copyrightYear":"2024","copyrightHolder":{"@id":"https:\/\/datascientest.com\/#organization"}},{"@type":"WebPage","@id":"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them","url":"https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them","name":"Formulas in Power Query formulas: The best of them","isPartOf":{"@id":"https:\/\/datascientest.com\/en\/#website"},"datePublished":"2024-01-17T13:11:27+00:00","dateModified":"2024-01-16T18:15:53+00:00","description":"Power Query is a powerful tool with a wide range of capabilities and specific Power Query formulas that can lead to...","inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datascientest.com\/en\/formulas-in-power-query-formulas-the-best-of-them"]}]},{"@type":"WebSite","@id":"https:\/\/datascientest.com\/en\/#website","url":"https:\/\/datascientest.com\/en\/","name":"Data Science Courses | DataScientest","description":"Leader in Data Science Courses","publisher":{"@id":"https:\/\/datascientest.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datascientest.com\/en\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-GB"},{"@type":"Organization","@id":"https:\/\/datascientest.com\/en\/#organization","name":"DataScientest","url":"https:\/\/datascientest.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/datascientest.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/datascientest.com\/en\/wp-content\/blogs.dir\/9\/files\/2024\/02\/DataScientest_icon_blue-2.png","contentUrl":"https:\/\/datascientest.com\/en\/wp-content\/blogs.dir\/9\/files\/2024\/02\/DataScientest_icon_blue-2.png","width":301,"height":301,"caption":"DataScientest"},"image":{"@id":"https:\/\/datascientest.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataScientestEN","https:\/\/twitter.com\/DataScientest__","https:\/\/www.linkedin.com\/school\/datascientest\/mycompany\/","https:\/\/www.tiktok.com\/@datascientest"]},{"@type":"Person","@id":"https:\/\/datascientest.com\/en\/#\/schema\/person\/19d5f7ade9407eb69af3bf6d1ae1731d","name":"Melanie","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/datascientest.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8d3d660a0698de06f13ecf4823519da1?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8d3d660a0698de06f13ecf4823519da1?s=96&d=mm&r=g","caption":"Melanie"},"url":"https:\/\/datascientest.com\/en\/author\/melaniekrause"}]}},"_links":{"self":[{"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/posts\/176488"}],"collection":[{"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/users\/67"}],"replies":[{"embeddable":true,"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/comments?post=176488"}],"version-history":[{"count":0,"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/posts\/176488\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/media\/176489"}],"wp:attachment":[{"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/media?parent=176488"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/categories?post=176488"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datascientest.com\/en\/wp-json\/wp\/v2\/tags?post=176488"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}