Create Table by M Query in Power BI Desktop Based on Parameters

[Asamadi]

I want to use M Query in Power BI Desktop to create table automatically that has 3 parameters as a input:

1. Start Point: like -10

2. End Point: like 100

3. Incremental Rate: Like Number (Ex. 2)  or Percent (Ex. +5%)

and the resault is like this: 

[MarcelBeug · Accepted solution]

You can use the following function and name it e.g. fnCreateTable

I defined "Increment" as text in order to differentiate between numbers and percentages.

If the End value will not be reached exactly, then the last value in the table will be the last value below the End value in case of positive increments or the last value greater than the End value in case of negative increments..

Caution 1: there is no "emergency stop". E.g. Start = -10, End = 0, Increment = "5%" will run forever.

Caution 2: with a positive increment, the End value must be greater than the Start value (and vice versa).

(Start as number, End as number, Increment as text) as table =>
let
    Growth = Number.From(Increment),
    Percentage = Text.EndsWith(Increment,"%"),
    GeneratedList = List.Generate(() => [ID = 1, Data = Start],
                                  each if End >= Start then [Data] <= End else [Data] >= End,
                                  each [ID = [ID] + 1, Data = if Percentage then [Data] * (1 + Growth) else [Data] + Growth]),
    Table = Table.FromRecords(GeneratedList)
in
    Table

[MarcelBeug · Accepted solution]

You can use the following function and name it e.g. fnCreateTable

I defined "Increment" as text in order to differentiate between numbers and percentages.

If the End value will not be reached exactly, then the last value in the table will be the last value below the End value in case of positive increments or the last value greater than the End value in case of negative increments..

Caution 1: there is no "emergency stop". E.g. Start = -10, End = 0, Increment = "5%" will run forever.

Caution 2: with a positive increment, the End value must be greater than the Start value (and vice versa).

(Start as number, End as number, Increment as text) as table =>
let
    Growth = Number.From(Increment),
    Percentage = Text.EndsWith(Increment,"%"),
    GeneratedList = List.Generate(() => [ID = 1, Data = Start],
                                  each if End >= Start then [Data] <= End else [Data] >= End,
                                  each [ID = [ID] + 1, Data = if Percentage then [Data] * (1 + Growth) else [Data] + Growth]),
    Table = Table.FromRecords(GeneratedList)
in
    Table

[Asamadi]

Fantastic, thanks for your very professional Solution.

I am new in power Query, May i ask you to Explain it more. i mean each sentence in Query.

[MarcelBeug · Accepted solution]

The very first line is the function definition with the function arguments and return type.

The function body is between "let" and "in"; the return value follows after "in".

"Growth" is the numeric version of "increment", e.g. "5" will become 5 and "5%" will become 0.05.

"Percentage" will be true if the last character of "Increment" is "%", else false.

"GeneratedList" uses function List.Generate, which is one of the most complex Power Query functions.

It acts as a do..while loop. During each iteration, an item is added to a list.

In this case, this will be a list of records, each with fields ID and Data.

List.Generate takes 4 arguments, each of which is a function The 4th argument is optional; I didnt use this one.

The first argument holds the initial values, i.c. a record with fields ID and Data.

The second argument holds the condition that will stop the loop once it turns to false.
Depending on the direction of the series, the condition will become false once the End value is reached or passed (in either direction).

The third argument defines the action for each iteration: i.c. ID is incremented with 1 and Data is incremented according to Growth and Percentage.

The fourth argument would be a definition of the return field. By default - and in this case - the entire list is returned.

"Table" turns the list of records into a table.

[Asamadi]

Hello Again Marcel

Thanks a million,

Your Explanation is very Professional as Your Solution.

You are a good teacher.

This is a good day for me, Thanks again.