In the last lesson introduced the custom calculations using simple arithmetic functions in conjunction with conditional statements. Custom formulas can go far beyond the realm of basic math calculations, and Microsoft has a whole library available for creating complex calculations. As with excels formula library, the M reference library can be pretty intimidating. But unlike Excel, I think the get and transform formulas are far easier to learn and incorporate due to the power of the ribbon commands. To demonstrate, let's use this fairly simple data set. It's a listing of bonds with their purchase date, face value, interest rate and redemption date.

Let's assume that the interest needs to grow on a continuous basis. So we need to use r p times e to the rt formula or principal times the rate times time and days. We could do this with a series of fibers individual steps, but we really want it in one to encapsulate the calculation. But to learn how to do this, let's go ahead and do the long individual step approach. First, we need to calculate the number of days between the purchase date and the redemption date. We can do this by selecting the two fields go into our Add Column command and choosing subtract days.

The result shows us the formula that we need to calculate the number of days in our formula bar. The formula bar shows us the generated formula that we've used. So we have to ration that days of our redemption date minus purchase date. Let's mentally bookmark that and make the next calculation, we need to multiply the date difference by the interest rate divided by 365. Fairly simple arithmetic computation. So we'll do the whole thing as a quick custom formula.

Once we have the rate multiplied by time down, we need to raise E to that value. This can be done through the ad copy A scientific drop down exponent command. Here we can see the function that comes out of it is number, XP rate mult time, the final step will be to multiply the fixed value against the interest factor. And once we've done that, we have all of the components we need to construct one single calculation to do all of these steps at once. So let's go ahead and make the multiplication so that we have a comparison and create our custom formula. To create this in one go, we'll start with our number XP function as the input, we'll put our interest divided by 365 and multiply this by our duration days function.

Within the duration days function, we'll input redemption date minus purchase date, as we've noted earlier, and then we'll close all of our parentheses. Finally, we'll multiply this against our face value to finalize the result. As I've just demonstrated, you can use the different Add Column and transform functions along the ribbon to help you understand and find functions that you can use for other things, and then you can use them to later mixed together and generate more complex results. Beyond the functions immediately available in the ribbon, there are a whole host of functions that can be found in the M reference library. So let's visit it and I'll show you how it works. For our purposes, we'll stick to the function reference.

But there's a whole bunch of other information about the language that's available here. To help you expand your transform capabilities. The functions are generally grouped based around the type of result they return. For example, each of our functions was preceded by a type, whether it was duration.or number dot. Within this library, the drop down of each will list not only the function but the general library that the function belongs to. The number of functions for example, contained functions create bytes currency, double, different types of integers, as well as your other generic number of functions following the link to any one of the functions will give you a list of information about it.

So let's dive into the number ESP function to compare our experience against the documentation. Each function is laid out fairly consistently, there's a brief description of the function. And in this case, it expresses that the function returns e raised to some power. Then the syntax is outlined. Here we have number dot exe, with number as nullable number. And then outside of the parentheses it also says as nullable number.

In English, this just means that you input a number which is the number inside the parentheses, and that is your input to the function and it can be null. And then the result is another nullable number. That's the asthma bill number outside the prison. What that means is that the result will be a number or a null value. Beyond the general description, a table will lay out each of the inputs and describe their purpose in more detail. Finally, the functional offer some brief examples demonstrating the results of the functions.

That concludes this lesson and chapter with the various column creation tools we've discussed, you can create all kinds of powerful and complex calculation tools within your get transform queries. Now that you've concluded this lesson, we've also concluded the training portion of this course, we've covered a really wide array of tools, and the following chapter will help you apply those tools through practical applications to help you figure out how to work and intermingle the tools together and generate real results.