Forum Discussion

Gabriel_Pedri

Resolver I

2 years ago

Solved

Recursive Function in Power Query

I'm new to the M language, so I need help creating a recursive code.

I'm trying to calculate the Final Cash Balance, which consists of:

Initial_Default = Value in the base

Final_Cb =Initial_Cb + Agg_Account

Initial_Cb = Previous value of Final_Cb

Agg_Account = Aggregation of various accounts

In my situation, there are dates where Initial_Default doesn't exist and dates where it does. With that in mind, we can create recursion with the following logic:

Example:

Dates {03/04, 04/04}

Let's suppose we are on the date 04/04 where there's no Initial_Default. We then need to go back one day and calculate Initial_Default -1 on 03/04 where there's a value.

So,

Initial_Default + Agg_Account = Final_Cb (03/04)

Now we calculate for the day 04/04:

Initial_Default + Agg_Account = Final_Cb (04/04)

Return Final_Cb

fnFinal_Cb

(Data as date) =>
let

    Initial_Cb =
        if SumInitial = Data then
            SumInitial
        else
            @fnFinal_Cb(Date.AddDays(Data, -1)),

    Final_Cb = Initial_Cb + SumAccount
in
    Final_Cb

SumInitial

// (Table as table, Category as text, Currency as text) =>
let
    Source = factCashFlowCONS,
    FilteredTable = Table.SelectRows(Source, each List.Contains({"Initial Cash Balance"}, [#"#Account"])),
    GroupedTable = Table.Group(FilteredTable, {"Date"}, {{"InitialDefault", each List.Sum([#"#USD"]), type number}}),
    Result = GroupedTable
in
    Result

SumAccount

// (Table as table, Category as text, Currency as text) =>
let
    Source = factCashFlowCONS,
    FilteredTable = Table.SelectRows(Source, each List.Contains({
        "Clients - National",
        "Scrap Metal",
        "Aux. Materials",
        "Gases",
        "Metal Alloys",
        "Sand",
        "Resin",
        "Pig Iron",
        "Other Raw Mat.",
        "Packaging"
    }, [#"#Account"])),
    GroupedTable = Table.Group(FilteredTable, {"Date"}, {{"Agg Account", each List.Sum([#"#USD"]), type number}}),
    Result = GroupedTable
in
    Result

Hi Gabriel_Pedri, check this

Edit1: 27.3.2024 15:45 GMT+1: I've editet the code. Now it matches your expected order and is much faster.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZLdasMwDEZfpeT6C5Msy7JuVwjsag9QehHGYIXSm+39mX8ySuNRCIkIPvYnHZ9OE/FLeQKFOGF6u11+Luv1cFy/vw6v63W9fXyW355dYHUBu2VDzLXMnKFxOmO3yftxKe85ekzIVss7Pzs5gnUojJBbVORQS/bo4NRKc4Zoo5gGKnkmONdM4gnc0oUs4J6OZUAIRLtPW6ljJKOyO1PLETmBejqhEqk3EmykApXzRdogCpTaHCQHh/SjZOxjFhJHalAig7dKtUycevPyz5wLVPJxhxIhbFRyqI52lmf9PyhZNo/ZQp9utQOt052za+1pENIZiU6I2tWAvBaRoTbqeBrnQccWRzRSHUc3A+qNKyH5KGNjmMrpXUHxsmlh54iko4w/SNm3q1hGDG83MVi5yRpGGXdIQWnnJViOsDydz78=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#Date" = _t, #"#Account" = _t, #"#BRL" = _t, #"#USD" = _t, #"#EUR" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"#Date", type date}, {"#Account", type text}, {"#BRL", Currency.Type}, {"#USD", Currency.Type}, {"#EUR", Currency.Type}}),
    RenameColumns = Table.TransformColumnNames(ChangedType, each Text.Remove(_, "#")),
    InitialCashBalance = Table.SelectRows(RenameColumns, each ([Account] = "Initial Cash Balance")),
    StepBack = RenameColumns,
    FilteredOutInitialCashBalance = Table.SelectRows(StepBack, each ([Account] <> "Initial Cash Balance")),
    GroupedRows = Table.Group(FilteredOutInitialCashBalance, {"Date"}, {{"All", each _, type table}, {"BRL Sum", each List.Sum([BRL]), Currency.Type}, {"USD Sum", each List.Sum([USD]), Currency.Type}, {"EUR Sum", each List.Sum([EUR]), Currency.Type} }),
    SortedRows = Table.Sort(GroupedRows,{{"Date", Order.Ascending}}),
    Buffer = Table.Buffer(Table.SelectColumns(SortedRows,{"BRL Sum", "USD Sum", "EUR Sum"})),
    GenerateInitlalAndFinal = List.Generate(
        ()=> [ x = 0,
               initial_BRL = InitialCashBalance{0}[BRL],
               final_BRL = initial_BRL + Buffer{x}[BRL Sum],
               initial_USD = InitialCashBalance{0}[USD],
               final_USD = initial_USD + Buffer{x}[USD Sum],
               initial_EUR = InitialCashBalance{0}[EUR],
               final_EUR = initial_EUR + Buffer{x}[EUR Sum] ],
        each [x] < Table.RowCount(Buffer),
        each [ x = [x]+1,
               initial_BRL = [final_BRL],
               final_BRL = initial_BRL + Buffer{x}[BRL Sum],
               initial_USD = [final_USD],
               final_USD = initial_USD + Buffer{x}[USD Sum],
               initial_EUR = [final_EUR],
               final_EUR = initial_EUR + Buffer{x}[EUR Sum] ]
),
    ToTable = Table.RemoveColumns(Table.FromRecords(GenerateInitlalAndFinal), {"x"}),
    AddedIndex = Table.AddIndexColumn(ToTable, "Index", 0, 1, Int64.Type),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, {"Index"}, "Attribute", "Value"),
    SplitColumnByDelimiter = Table.SplitColumn(UnpivotedOtherColumns, "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Account", "Attribute.2"}),
    PivotedColumn = Table.Pivot(SplitColumnByDelimiter, List.Distinct(SplitColumnByDelimiter[Attribute.2]), "Attribute.2", "Value"),
    TransformedAccount = Table.TransformColumns(PivotedColumn, {{"Account", each Text.Proper(_) & " Cash Balance", type text}}),
    ReplacedIndex = Table.ReplaceValue(TransformedAccount,each [Index],null,Replacer.ReplaceValue,{"Index"}),
    // Initial first, Final second
    TableSplitAndReverseOrder = List.Transform(Table.Split(ReplacedIndex, 2), each Table.FromRows(List.Reverse(Table.ToRows(_)), Table.ColumnNames(RenameColumns))),
    CombinedWith_SortedRows = Table.Combine(List.Transform(List.Zip({ SortedRows[All], TableSplitAndReverseOrder }), Table.Combine)),
    FilledDown = Table.FillDown(CombinedWith_SortedRows,{"Date"})
in
    FilledDown

5 Replies

dufoq3

Community Champion

2 years ago

Hi Gabriel_Pedri, could you provide sample data as table so we can copy/paste and expected result based on sample data please?

Gabriel_Pedri

Resolver I

2 years ago

Fact Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZLdasMwDEZfpeT6C5Msy7JuVwjsag9QehHGYIXSm+39mX8ySuNRCIkIPvYnHZ9OE/FLeQKFOGF6u11+Luv1cFy/vw6v63W9fXyW355dYHUBu2VDzLXMnKFxOmO3yftxKe85ekzIVss7Pzs5gnUojJBbVORQS/bo4NRKc4Zoo5gGKnkmONdM4gnc0oUs4J6OZUAIRLtPW6ljJKOyO1PLETmBejqhEqk3EmykApXzRdogCpTaHCQHh/SjZOxjFhJHalAig7dKtUycevPyz5wLVPJxhxIhbFRyqI52lmf9PyhZNo/ZQp9utQOt052za+1pENIZiU6I2tWAvBaRoTbqeBrnQccWRzRSHUc3A+qNKyH5KGNjmMrpXUHxsmlh54iko4w/SNm3q1hGDG83MVi5yRpGGXdIQWnnJViOsDydz78=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#Date" = _t, #"#Account" = _t, #"#BRL" = _t, #"#USD" = _t, #"#EUR" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"#Date", type date}, {"#Account", type text}, {"#BRL", type number}, {"#USD", type number}, {"#EUR", type number}})
in
    #"Changed Type"

Initial Value

let
    Source = factCashFlow,
    FilteredTable = Table.SelectRows(Source, each List.Contains({"Initial Cash Balance"}, [#"#Account"])),
    GroupedTable = Table.Group(FilteredTable, {"#Date"}, {{"InitialDefault", each List.Sum([#"#USD"]), type number}}),
    Result = GroupedTable
in
    Result

Sum Account

let
    Source = factCashFlow,
    FilteredTable = Table.SelectRows(Source, each List.Contains({
        "OCF",
        "FCF"
    }, [#"#Account"])),
    GroupedTable = Table.Group(FilteredTable, {"#Date"}, {{"Value", each List.Sum([#"#USD"]), type number}})
    
in
    GroupedTable

Function Calculate Final Cash Balance

(Data as date) =>
let

    Initial_Cb =
        if ValueInitial = Data then
            ValueInitial
        else
            @fnFinal_Cb(Date.AddDays(Data, -1)),

    Final_Cb = Initial_Cb + AggAccount
in
    Final_Cb

I believe this might not be the best way to calculate the Final Cash Balance, but since I lack knowledge in M language, I had to improvise.

I created separate tables to return me the sums or the value of Initial Cash Balance Default. If there's a way to integrate these calculations into the function, it would be better.

It's noticeable that we have different days not necessarily in sequence (1, 2, 3, ... 31). I wish for the calculation of the Final Cash Balance to be returned for each existing day in the database.

In the future, I would like to implement a row in the fact table itself through an Insert.Rows(), so we would have both the values of the accounts and the value of the Final Calculated.

Expected output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVDLasQwDPyVxeddKtmypLl2IdBTP2DJIZRCA0su7f+zsdOats5Ca/wYjEaamcslED+sO1KUcAzP52G9TwJRt4LgSCYVEaKF8fiLMVQGtdMVPC3zxzxdD+fp/e3wOF2n5eV1/W6NGeYmXpCzZ/nZIZcR89Lzm8TW6JvA2FuCSfZYEEPAWpGBU+4pw2cKbhHcyAU4su7MuOPxzxp3HZ6gxl6EKoyINsXr2hQzdSYVTlUwJyjXSKMnlr5+c5gEJPmLWF7hbH31HXv/Ebjr0IBMNRWVWHJiUWgYxxs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#Date" = _t, #"#Account" = _t, #"#BRL" = _t, #"#USD" = _t, #"#EUR" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"#Date", type date}, {"#Account", type text}, {"#BRL", Int64.Type}, {"#USD", Int64.Type}, {"#EUR", Int64.Type}})
in
    #"Changed Type"

Initial Cash Balance = Last Final Cash Balance

Final Cash Balance = SumAccount + Initial Cash Balance

dufoq3

Community Champion

2 years ago

Hi Gabriel_Pedri, check this

Edit1: 27.3.2024 15:45 GMT+1: I've editet the code. Now it matches your expected order and is much faster.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZLdasMwDEZfpeT6C5Msy7JuVwjsag9QehHGYIXSm+39mX8ySuNRCIkIPvYnHZ9OE/FLeQKFOGF6u11+Luv1cFy/vw6v63W9fXyW355dYHUBu2VDzLXMnKFxOmO3yftxKe85ekzIVss7Pzs5gnUojJBbVORQS/bo4NRKc4Zoo5gGKnkmONdM4gnc0oUs4J6OZUAIRLtPW6ljJKOyO1PLETmBejqhEqk3EmykApXzRdogCpTaHCQHh/SjZOxjFhJHalAig7dKtUycevPyz5wLVPJxhxIhbFRyqI52lmf9PyhZNo/ZQp9utQOt052za+1pENIZiU6I2tWAvBaRoTbqeBrnQccWRzRSHUc3A+qNKyH5KGNjmMrpXUHxsmlh54iko4w/SNm3q1hGDG83MVi5yRpGGXdIQWnnJViOsDydz78=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#Date" = _t, #"#Account" = _t, #"#BRL" = _t, #"#USD" = _t, #"#EUR" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"#Date", type date}, {"#Account", type text}, {"#BRL", Currency.Type}, {"#USD", Currency.Type}, {"#EUR", Currency.Type}}),
    RenameColumns = Table.TransformColumnNames(ChangedType, each Text.Remove(_, "#")),
    InitialCashBalance = Table.SelectRows(RenameColumns, each ([Account] = "Initial Cash Balance")),
    StepBack = RenameColumns,
    FilteredOutInitialCashBalance = Table.SelectRows(StepBack, each ([Account] <> "Initial Cash Balance")),
    GroupedRows = Table.Group(FilteredOutInitialCashBalance, {"Date"}, {{"All", each _, type table}, {"BRL Sum", each List.Sum([BRL]), Currency.Type}, {"USD Sum", each List.Sum([USD]), Currency.Type}, {"EUR Sum", each List.Sum([EUR]), Currency.Type} }),
    SortedRows = Table.Sort(GroupedRows,{{"Date", Order.Ascending}}),
    Buffer = Table.Buffer(Table.SelectColumns(SortedRows,{"BRL Sum", "USD Sum", "EUR Sum"})),
    GenerateInitlalAndFinal = List.Generate(
        ()=> [ x = 0,
               initial_BRL = InitialCashBalance{0}[BRL],
               final_BRL = initial_BRL + Buffer{x}[BRL Sum],
               initial_USD = InitialCashBalance{0}[USD],
               final_USD = initial_USD + Buffer{x}[USD Sum],
               initial_EUR = InitialCashBalance{0}[EUR],
               final_EUR = initial_EUR + Buffer{x}[EUR Sum] ],
        each [x] < Table.RowCount(Buffer),
        each [ x = [x]+1,
               initial_BRL = [final_BRL],
               final_BRL = initial_BRL + Buffer{x}[BRL Sum],
               initial_USD = [final_USD],
               final_USD = initial_USD + Buffer{x}[USD Sum],
               initial_EUR = [final_EUR],
               final_EUR = initial_EUR + Buffer{x}[EUR Sum] ]
),
    ToTable = Table.RemoveColumns(Table.FromRecords(GenerateInitlalAndFinal), {"x"}),
    AddedIndex = Table.AddIndexColumn(ToTable, "Index", 0, 1, Int64.Type),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, {"Index"}, "Attribute", "Value"),
    SplitColumnByDelimiter = Table.SplitColumn(UnpivotedOtherColumns, "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Account", "Attribute.2"}),
    PivotedColumn = Table.Pivot(SplitColumnByDelimiter, List.Distinct(SplitColumnByDelimiter[Attribute.2]), "Attribute.2", "Value"),
    TransformedAccount = Table.TransformColumns(PivotedColumn, {{"Account", each Text.Proper(_) & " Cash Balance", type text}}),
    ReplacedIndex = Table.ReplaceValue(TransformedAccount,each [Index],null,Replacer.ReplaceValue,{"Index"}),
    // Initial first, Final second
    TableSplitAndReverseOrder = List.Transform(Table.Split(ReplacedIndex, 2), each Table.FromRows(List.Reverse(Table.ToRows(_)), Table.ColumnNames(RenameColumns))),
    CombinedWith_SortedRows = Table.Combine(List.Transform(List.Zip({ SortedRows[All], TableSplitAndReverseOrder }), Table.Combine)),
    FilledDown = Table.FillDown(CombinedWith_SortedRows,{"Date"})
in
    FilledDown

Forum Discussion

Recursive Function in Power Query

5 Replies

Related content

Recursive function in Power Query

Recursion Strategy

PowerBI Loop/Recursive Function

Recursive hierarchy in power query

AI functionality using Power BI Dataset