Power Query: how to stop being afraid of functional programming and start working in the advanced query editor
Not so long ago I came across an article on Habré on how to use Power BI and how to carry out with it a simple plan-factual analysis . The author has a huge respect for work - the material is really useful. More than enough for a beginner. However, as far as I understand, for many, working with PQ / PBI ends with clicking on the buttons in the interface.
In principle, this is sufficient for most users to solve simple tasks. Moreover, this is the majority that is called, in everyday life - non-programmers are informed. And, as practice has shown, not everyone knows that PQ has an advanced query editing mode. Meanwhile, the fear (unwillingness / inability) to dig deeper makes it impossible to fully utilize all the pledged PQ / PBI functionality. I will note at least the fact that not all the buttons for which there are functions are present in the interface. I think I’m not much mistaken if I say that there are probably two times more functions than buttons.
If you feel that to solve the existing problems you lack the functionality provided in the interface and / or there is time to satisfy academic interest, welcome to the category ...
')
Leafing through the article, I stumbled upon a fragment where the author proposes to create a table of correspondence between the month of the year and its ordinal number, referring to the fact that "the language" M "does not allow at the current moment to convert the names of the months into dates . "
In principle, the problem is elementary. But looking at the request with which it was implemented, I realized that I could not just walk past.
What does the author do if you translate the code into Russian
It became interesting to me, but how else could a similar problem be solved by setting the following guidelines:
As a result, the interest simply resulted in a whole collection of code fragments and a desire to share this with others.
Disclaimer Immediately make a reservation, I have no complaints about the author and his approaches to composing scripts (it is possible that the scripts were written in haste). The purpose of this article is to show beginners different approaches when writing scripts.
Remark If you want to understand more deeply how this or that function works at one of the stages, remove the brackets with the parameters - and you will see the documentation for it with a description of what it accepts and what gives
So, what immediately embarrassed me:
The list of months is typed manually
First, as mentioned above, typing the text, you can easily make mistakes, and secondly, all these quotes / commas ... Well, they are just confusion. Therefore, instead of creating a list, listing months, I would suggest using a plain text string with a natural separator. Those. comma
Here, it seems, everything is clear - the Text.Split function converts a string into a list, breaking it with the separator ","
You can start an index from any number, including one (if you read the documentation)
Moreover, in the Table.AddIndexColumn function , the last argument can be given a step other than one. Accordingly, it is possible to number the cells and so - 15,20,25,30 ...
From this, in fact, it all started ...
Further I will give examples of code with comments with gradually increasing complexity. To begin with, how can you list a list of months.
The simplest is direct enumeration using the syntax of the M language:
A little more complicated - breaking the line into parts by separator:
Using the date list generation function:
Here, in the first step, the List.Dates function generates a list of dates, taking the starting date as the first argument, the number of elements as the second, and the incrementing step as the third. In this case, adding 32 days to a month, we are guaranteed to get into the next month with every step. Of course, if the calendar were 13 months old or more, such a method would not be possible. But for training purposes it will come down.
The second step is to convert the list of dates into a list of month names. After reviewing the documentation, we find that the Date.MonthName function is responsible for converting the date to the month. What it does is take the date as the first argument and give the name of the month as a string. And if we pass the third item and the optional argument culture ("ru-RU", "en-US", "uk-UA", "ar-LY"), we get the name of the month, taking into account the locale. Ok, then, respectively, we need to apply this function to each element of the list.
Those. get the list {Date.MonthName (Date1), Date.MonthName (Date2), ..., Date.MonthName (DateN)}. Well, or like this {Date.MonthName (Date1, “ru-RU”), Date.MonthName (Date2, “ru-RU”), ..., Date.MonthName (DateN, “ru-RU”)}
Now we need a tool that converts lists. The List.Transform function is responsible for the conversion of lists. What it does is it takes the input list as the first argument and applies a function to each element, which is passed as the second argument.
Ok, we say, and send the second argument to Date.MonthName. The following is an explanation of what is happening - the List.Transform function takes each element of the array and feeds it to the Date.MonthName function, passing each Date as an argument implicitly .
Well, what if we want to get the names of the months based on the locale? Say, "uk-UA". How do we set this parameter? As we remember, List.Transform takes the second argument _function_ (and only the function), and we cannot explicitly pass to this function neither the first argument, nor the second. Accordingly, we need a function that takes, as in the documentation, one parameter. And let's create it! Let's just call it “fn”, do everything we need in it, and give it to the List.Transform. In javascript, this is called “closure”:
Then our code will look like this:
In general, it is not necessary to specify a function name. You can enter an unnamed function directly in brackets:
We also remember that in the initial list the months were written with a small letter. Let's do it all in the same function:
And now let's try to create a list of months in a slightly different way - using the List.Generate function. The documentation states the following:
Those. all three arguments are functions. Fine! Then the task is reduced to just one line:
Well, let it be a few, for clarity:
What's going on here. Calculates the initialization function of the start value given by the first argument. This is the date "January 1, 2016". Further, the function defined by the second argument is calculated, to which the value (x) calculated in the previous step is implicitly passed. This second function performs a simple task — it returns true / false, which means whether it is possible to continue the calculations further. In our case, “January 1, 2016” <“January 1, 2017”, i.e. true, i.e. can continue. In this case, the value of the previous step is sent to the third function, which simply adds the month to the calculated value. We get "February 1, 2016". Further, this value is sent to the second function, where "February 1, 2016" is compared with "January 1, 2017", and the function still returns true, then another month is added, etc. This happens until a month is added to “December 1, 2016”, and the calculated value becomes “January 1, 2017”. When this value is once again sent to the test, we get false, because "January 1, 2017" _ equal to "January 1, 2017", but not _ smaller_.
After calculating the accumulated intermediate values, the last function, Date.MonthName, will be applied to each of the list elements. Just as it was described above for the List.Transform function
But what if, like last time, we want the months to be generated based on the locale, and the text starts with a small letter? Then we create our custom function and do everything we need in it:
What is good about this method of generation is that it does not matter to us how many months in a year. Yes, even if at least 2976, as in Pluto. We set the first day of the first month of one year and the first day of the first month of the next year. Plus the addition of the month to the date and strict inequality.
And what if we know exactly how many months of the year. Then we can, having an array of numbers from 1 to 12, generate an array of months according to their number:
This is already known to us List.Transform, in which the first argument is an array of numbers, and the second is the number-to-month conversion function. Everything is pretty primitive, which should also be clear.
The List.TransformMany function is much more interesting. In principle, everything is the same as in List.Transform, but, unlike List.Transform, another argument is added to List.TransformMany - this is a function to which two arguments are implicitly sent - _ original and _ calculated , which allows us to use both the month number and its calculated name in one pass. We only need to concatenate them in one line:
I will show you how to use this to form a table a little later.
Everything is simple here - Text.Split, Table.AddIndexColumn is used with the start from one and Table.FromList with the simultaneous naming of the column (no need to rename the default "Custom1").
Table.FromList + List.Positions
Here is an atypical use of the Table.FromList function. The second argument is a function that returns the array of cells to the construction sites. The sequence is as follows - an array of strings is created src, an array of pos indices is created for it, then the array of indices is converted into a table using a custom function. This custom function iterates over an array of indices and refers to an array of strings at the selected index.
I will explain it more simply - for example, let's say element No. 0 from the “pos” list and is passed to the function, where from 0 the array {src {0}, 0 + 1} is formed, i.e. {"January", 1}. Next, the Table.FromList function decomposes them into two columns by “Month” and “Index”
If you use only with the interface, then in the Table.FromList the fake function Splitter.SplitByNothing () is passed by default, which is essentially a stub for working with Table.FromList
We merge two parallel lists into one using the Table.FromColumns function. Here we form two parallel lists - a list of months and a list of its indices. If you try to visualize this process, then it “looks like” this in the same way as zipping on clothes;) Before zipping, we transform the list of indexes, adding a unit to each element:
The promised example with List.TransformMany I will paint in detail. Suppose we have a list of numbers from 1 to 12:
And also there is a number-per-month conversion function:
The only thing to take care of is that the function returns not just a value, but a value that is an element of the array:
We will also create a final function, which at each stage, using the starting and calculated values, creates a list of the starting and calculated ones. Type of this - {"march", "3"}:
Now we will transfer to the List.TransformMany function the transformation function of each element and the final function as arguments, and assign them to our array from 1 to 12:
Then create a table from the resulting array of arrays
As a result, we obtain the following table:
Further we can form the final table as we want. For example, multiply the number of the month by 100, add text to the name of the month and add a column with the name of the day of the week on the first day of this month:
And for those who prefer asceticism and Zen to perversions, I propose a solution to the original problem with the same technique in one line:
Well and, perhaps, the last way to create the original table - the List.Accumulate method. Here is what help writes on this topic:
I will add a couple of examples from myself. It is possible to accumulate a summary value from text values. For example, how to merge all the elements of a list into one line, separating them with a full stop, converting the lines to uppercase:
JANUARY FEBRUARY MARCH APRIL.
Here, two parameters are implicitly passed to the fn_accum functions - the result accumulated at this step and the current value from the list. In addition to numbers and text, you can also use lists and tables:
In this example, an empty array is taken and the month numbers converted to a text string are pasted to it. By analogy - for tables:
Here we set the initially empty table and “paste” to it the rows calculated at each iteration.
I hope to clarify some of the subtleties of working with the code directly, I did better than confuse it. In fact, M is a very beautiful and concise language. And if you have this way and that, and in every way you have to turn the data in your daily work, it makes sense to study it a little deeper.
In principle, this is sufficient for most users to solve simple tasks. Moreover, this is the majority that is called, in everyday life - non-programmers are informed. And, as practice has shown, not everyone knows that PQ has an advanced query editing mode. Meanwhile, the fear (unwillingness / inability) to dig deeper makes it impossible to fully utilize all the pledged PQ / PBI functionality. I will note at least the fact that not all the buttons for which there are functions are present in the interface. I think I’m not much mistaken if I say that there are probably two times more functions than buttons.
If you feel that to solve the existing problems you lack the functionality provided in the interface and / or there is time to satisfy academic interest, welcome to the category ...
')
Preamble
Leafing through the article, I stumbled upon a fragment where the author proposes to create a table of correspondence between the month of the year and its ordinal number, referring to the fact that "the language" M "does not allow at the current moment to convert the names of the months into dates . "
In principle, the problem is elementary. But looking at the request with which it was implemented, I realized that I could not just walk past.
let Source={"", "", "", "", "", "", "", "", "", "", "", ""}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1), #"Added to Column" = Table.TransformColumns(#"Added Index", {{"Index", each List.Sum({_, 1})}}), #"Renamed Columns" = Table.RenameColumns(#"Added to Column",{{"Column1", ""}}) in #"Renamed Columns" What does the author do if you translate the code into Russian
- Manually creates a list of month names.
- Converts a list to a table.
- Adds a column with an index, starting from zero, in increments of one
- Transforms the "Index" column by adding one to each cell.
- Renames the default column name "Column1" to "Month"
It became interesting to me, but how else could a similar problem be solved by setting the following guidelines:
- minimum of lines of code due to the use of the pledged functionality (for example, not to rename a column, but to set immediately, not to increment a column, but to start from one, etc.)
- refusal of manual input (you can easily be sealed by typing manually)
- use month names based on locale
As a result, the interest simply resulted in a whole collection of code fragments and a desire to share this with others.
Criticism
Disclaimer Immediately make a reservation, I have no complaints about the author and his approaches to composing scripts (it is possible that the scripts were written in haste). The purpose of this article is to show beginners different approaches when writing scripts.
Remark If you want to understand more deeply how this or that function works at one of the stages, remove the brackets with the parameters - and you will see the documentation for it with a description of what it accepts and what gives
So, what immediately embarrassed me:
The list of months is typed manually
First, as mentioned above, typing the text, you can easily make mistakes, and secondly, all these quotes / commas ... Well, they are just confusion. Therefore, instead of creating a list, listing months, I would suggest using a plain text string with a natural separator. Those. comma
Text.Split(", , , , , , , , , , , ", ", ") Here, it seems, everything is clear - the Text.Split function converts a string into a list, breaking it with the separator ","
You can start an index from any number, including one (if you read the documentation)
Table.AddIndexColumn(tbl, "Index", 1, 1) Moreover, in the Table.AddIndexColumn function , the last argument can be given a step other than one. Accordingly, it is possible to number the cells and so - 15,20,25,30 ...
Table.AddIndexColumn(tbl, "Index", 15, 5) From this, in fact, it all started ...
What criticism has grown
Further I will give examples of code with comments with gradually increasing complexity. To begin with, how can you list a list of months.
The simplest is direct enumeration using the syntax of the M language:
months_list={"", "", "", "", "", "", "", "", "", "", "", ""} A little more complicated - breaking the line into parts by separator:
Text.Split(", , , , , , , , , , , ", ", ") Using the date list generation function:
let gen = List.Dates(#date(2016,1,1), 12, #duration(32,0,0,0)), month_name = List.Transform(gen, Date.MonthName) in month_name Here, in the first step, the List.Dates function generates a list of dates, taking the starting date as the first argument, the number of elements as the second, and the incrementing step as the third. In this case, adding 32 days to a month, we are guaranteed to get into the next month with every step. Of course, if the calendar were 13 months old or more, such a method would not be possible. But for training purposes it will come down.
The second step is to convert the list of dates into a list of month names. After reviewing the documentation, we find that the Date.MonthName function is responsible for converting the date to the month. What it does is take the date as the first argument and give the name of the month as a string. And if we pass the third item and the optional argument culture ("ru-RU", "en-US", "uk-UA", "ar-LY"), we get the name of the month, taking into account the locale. Ok, then, respectively, we need to apply this function to each element of the list.
Those. get the list {Date.MonthName (Date1), Date.MonthName (Date2), ..., Date.MonthName (DateN)}. Well, or like this {Date.MonthName (Date1, “ru-RU”), Date.MonthName (Date2, “ru-RU”), ..., Date.MonthName (DateN, “ru-RU”)}
Now we need a tool that converts lists. The List.Transform function is responsible for the conversion of lists. What it does is it takes the input list as the first argument and applies a function to each element, which is passed as the second argument.
Ok, we say, and send the second argument to Date.MonthName. The following is an explanation of what is happening - the List.Transform function takes each element of the array and feeds it to the Date.MonthName function, passing each Date as an argument implicitly .
Well, what if we want to get the names of the months based on the locale? Say, "uk-UA". How do we set this parameter? As we remember, List.Transform takes the second argument _function_ (and only the function), and we cannot explicitly pass to this function neither the first argument, nor the second. Accordingly, we need a function that takes, as in the documentation, one parameter. And let's create it! Let's just call it “fn”, do everything we need in it, and give it to the List.Transform. In javascript, this is called “closure”:
fn = (x)=>Date.MonthName(x, "uk-UA"), Then our code will look like this:
let gen = List.Dates(#date(2016,1,1), 12, #duration(32,0,0,0)), fn = (x)=>Date.MonthName(x, "uk-UA"), month_name = List.Transform(gen, fn) in month_name In general, it is not necessary to specify a function name. You can enter an unnamed function directly in brackets:
let gen = List.Dates(#date(2016,1,1), 12, #duration(32,0,0,0)), month_name = List.Transform(gen, (x)=>Date.MonthName(x, "uk-UA")) in month_name We also remember that in the initial list the months were written with a small letter. Let's do it all in the same function:
let gen = List.Dates(#date(2016,1,1), 12, #duration(32,0,0,0)), month_name = List.Transform(gen, (x)=>Text.Lower(Date.MonthName(x, "uk-UA"))) in month_name And now let's try to create a list of months in a slightly different way - using the List.Generate function. The documentation states the following:
Those. all three arguments are functions. Fine! Then the task is reduced to just one line:
List.Generate(()=>#date(2016,1,1), (x)=>x<#date(2017,1,1), (x)=>Date.AddMonths(x,1), Date.MonthName) Well, let it be a few, for clarity:
List.Generate( ()=>#date(2016,1,1), (x)=>x<#date(2017,1,1), (x)=>Date.AddMonths(x,1), Date.MonthName ) What's going on here. Calculates the initialization function of the start value given by the first argument. This is the date "January 1, 2016". Further, the function defined by the second argument is calculated, to which the value (x) calculated in the previous step is implicitly passed. This second function performs a simple task — it returns true / false, which means whether it is possible to continue the calculations further. In our case, “January 1, 2016” <“January 1, 2017”, i.e. true, i.e. can continue. In this case, the value of the previous step is sent to the third function, which simply adds the month to the calculated value. We get "February 1, 2016". Further, this value is sent to the second function, where "February 1, 2016" is compared with "January 1, 2017", and the function still returns true, then another month is added, etc. This happens until a month is added to “December 1, 2016”, and the calculated value becomes “January 1, 2017”. When this value is once again sent to the test, we get false, because "January 1, 2017" _ equal to "January 1, 2017", but not _ smaller_.
After calculating the accumulated intermediate values, the last function, Date.MonthName, will be applied to each of the list elements. Just as it was described above for the List.Transform function
But what if, like last time, we want the months to be generated based on the locale, and the text starts with a small letter? Then we create our custom function and do everything we need in it:
List.Generate( ()=>#date(2016,1,1), (x)=>x<#date(2017,1,1), (x)=>Date.AddMonths(x,1), (x)=>Text.Lower(Date.MonthName(x, "en-US")) ) What is good about this method of generation is that it does not matter to us how many months in a year. Yes, even if at least 2976, as in Pluto. We set the first day of the first month of one year and the first day of the first month of the next year. Plus the addition of the month to the date and strict inequality.
And what if we know exactly how many months of the year. Then we can, having an array of numbers from 1 to 12, generate an array of months according to their number:
List.Transform({1..12}, (x)=>Text.Lower(Date.MonthName(#date(2016,x,1), "ar-LY"))) This is already known to us List.Transform, in which the first argument is an array of numbers, and the second is the number-to-month conversion function. Everything is pretty primitive, which should also be clear.
The List.TransformMany function is much more interesting. In principle, everything is the same as in List.Transform, but, unlike List.Transform, another argument is added to List.TransformMany - this is a function to which two arguments are implicitly sent - _ original and _ calculated , which allows us to use both the month number and its calculated name in one pass. We only need to concatenate them in one line:
List.TransformMany({1..12}, (x)=>{#date(2016,x,1)}, (x,y)=>Number.ToText(x)&" - "&Text.Lower(Date.MonthName(y))) I will show you how to use this to form a table a little later.
Go to the tables
Everything is simple here - Text.Split, Table.AddIndexColumn is used with the start from one and Table.FromList with the simultaneous naming of the column (no need to rename the default "Custom1").
let src=Text.Split("|||||||||||","|"), convert = Table.FromList(src, Splitter.SplitByNothing(), {""}), add_index = Table.AddIndexColumn(convert, "Index", 1, 1) in add_index Table.FromList + List.Positions
Here is an atypical use of the Table.FromList function. The second argument is a function that returns the array of cells to the construction sites. The sequence is as follows - an array of strings is created src, an array of pos indices is created for it, then the array of indices is converted into a table using a custom function. This custom function iterates over an array of indices and refers to an array of strings at the selected index.
I will explain it more simply - for example, let's say element No. 0 from the “pos” list and is passed to the function, where from 0 the array {src {0}, 0 + 1} is formed, i.e. {"January", 1}. Next, the Table.FromList function decomposes them into two columns by “Month” and “Index”
let src=Text.Split("|||||||||||","|"), pos = List.Positions(src), // t = Table.FromList(pos, (x)=>{src{x},x+1}, {"","Index"}) in t If you use only with the interface, then in the Table.FromList the fake function Splitter.SplitByNothing () is passed by default, which is essentially a stub for working with Table.FromList
We merge two parallel lists into one using the Table.FromColumns function. Here we form two parallel lists - a list of months and a list of its indices. If you try to visualize this process, then it “looks like” this in the same way as zipping on clothes;) Before zipping, we transform the list of indexes, adding a unit to each element:
let src=Text.Split("|||||||||||","|"), pos = List.Positions(src), transform = List.Transform(pos, (x)=>x+1), t = Table.FromColumns({src, transform},{"","Index"}) in t The promised example with List.TransformMany I will paint in detail. Suppose we have a list of numbers from 1 to 12:
And also there is a number-per-month conversion function:
fn = (x)=>Text.Lower(Date.MonthName(#date(2016,x,1),"en-US"))} The only thing to take care of is that the function returns not just a value, but a value that is an element of the array:
fn = (x)=>{Text.Lower(Date.MonthName(#date(2016,x,1),"en-US"))} We will also create a final function, which at each stage, using the starting and calculated values, creates a list of the starting and calculated ones. Type of this - {"march", "3"}:
final = (start_element, calculated_element)=>{start_element, calculated_element}, Now we will transfer to the List.TransformMany function the transformation function of each element and the final function as arguments, and assign them to our array from 1 to 12:
Then create a table from the resulting array of arrays
: let start_list = {1..12}, fn = (x)=>{Text.Lower(Date.MonthName(#date(2016,x,1),"en-US"))}, final = (start_element, calculated_element)=>{start_element, calculated_element}, transform = List.TransformMany(start_list, fn, final), t = #table({"Index", ""}, transform) in t As a result, we obtain the following table:
Further we can form the final table as we want. For example, multiply the number of the month by 100, add text to the name of the month and add a column with the name of the day of the week on the first day of this month:
let start_list = {1..12}, fn = (x)=>{Date.MonthName(#date(2016,x,1),"en-US")}, final = (x,y)=>{ x*100, y&" has "&Number.ToText(Date.DaysInMonth(#date(2016,x,1)))&" days", "First day of "&y&" is "&Date.DayOfWeekName(#date(2016,x,1), "en-US") }, transform = List.TransformMany(start_list, fn, final), t = #table({"Index", "Month", "FirstDayOfWeek"}, transform) in t And for those who prefer asceticism and Zen to perversions, I propose a solution to the original problem with the same technique in one line:
#table({"","Index"},List.TransformMany({1..12},(x)=>{Text.Lower(Date.MonthName(#date(2016,x,1)))},(x,y)=>{y,x})) Well and, perhaps, the last way to create the original table - the List.Accumulate method. Here is what help writes on this topic:
I will add a couple of examples from myself. It is possible to accumulate a summary value from text values. For example, how to merge all the elements of a list into one line, separating them with a full stop, converting the lines to uppercase:
let fn_accum = (accum,x)=> accum & Text.Upper(x)&".", result = List.Accumulate({"", "", "", ""}, "", fn_accum) in result JANUARY FEBRUARY MARCH APRIL.
Here, two parameters are implicitly passed to the fn_accum functions - the result accumulated at this step and the current value from the list. In addition to numbers and text, you can also use lists and tables:
List.Accumulate({1..12}, {}, (accum,x)=> accum & {Date.MonthName(#date(2016,x,1))}) In this example, an empty array is taken and the month numbers converted to a text string are pasted to it. By analogy - for tables:
let lst = {1..12}, start_table = #table({},{}), fn= (accum,x)=>accum & #table({"","Index"},{{Text.Lower(Date.MonthName(#date(2016,x,1))),x}}), result=List.Accumulate(lst, start_table, fn) in result Here we set the initially empty table and “paste” to it the rows calculated at each iteration.
I hope to clarify some of the subtleties of working with the code directly, I did better than confuse it. In fact, M is a very beautiful and concise language. And if you have this way and that, and in every way you have to turn the data in your daily work, it makes sense to study it a little deeper.
Source: https://habr.com/ru/post/311600/
All Articles