Recursive Function in Power Query

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

Gabriel_Pedri

Resolver I

2 years ago

Hi dufoq3, I would like to ask for your help again.

I need to add two more "groupers" to this total of BRL, USD, and EUR.

I tried to use your method but I'm having trouble merging the Final/Initial calculation with the Base.

I want to add the BFS and Unit columns so that I can filter them in the Power BI matrix.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVdNa1xHEPwrRudZ0t/dc5QNC74kB5GT0UGEQAzGF+f/k5qZ/VBWb9+CtGiE1K+rpqq637dvT8S/4UtI9Kk9fcb3n78zPr/+/P7v97cfn768/frn0+e3H28///obv2YOrta5JHASydSWhDNO1EgoSFifXttN4eeHlSV7y3BXcpycKJqxVe84mY/HZHb5WPnlUWG0RaShJP18SuvldT5Flrp/rHx8VPkQotZ6dhQcRzY1AwjzPo/SM1oXA1cfii8+ZIdpMeJGXXwybQp+emnnybuAEHYyu0v1TmlR0Wyl4TLJdUtwjfNixFhos+eXR4Xde3NlitEkE7G0kj6I7oxnuHttNHx8VHbdmQRd7oyt9+Q4n9Qj+R7HunOBIDlAqxcP0R2kooBAlp4P4t21lZVv6O75YXWxImlczpPmSrDTIbZ5MrdoSlR1j+edylBWzb4mt3iO9iZhJ09aUkt8bjR9fFh5XploybjCA4pA0MN346AMT270e/X2H1/GEw5DREYZvex0pK5RSXw+lqXrFq3XWsdZCzctoK1zjFLSzVv5uCyJPno12+/o66oSgps0KHNQpOJIF0BUmTUrDYVSNgo9f8DGSn1yYksjyENIxqsvBRlBUJTquVtsgRNCkDZNZNPQBFKqWVrMBARLimQNtw1lP98CPLiQtw7vzsAcHDMOXDS77B7Vsucm5S+3EIV6VWOJnLVQM9sgcCbRQcsTXg5Iea/WQgjRAKEjYLUud4+YZ/wzjkXugNzV7sbNO4wawDGop9lIIgwbkrtYZxhDpmCAhTdqHW8wcmYNvmQOBRRJbkU2A0aHtFrEGBE7hU4ARbWJQqAnsgpn7xErQhDbcCYa3iL+eAMwQDHgQYqjFNTZIXajntPyjMAoaKPvDZOrATkx8qDSM+uakNblDlTB1JayrqUWPkfOwn4R8/KZs5qazHjHNTZWXMfudDshwyzTkVA2kRGCEX5znTW7weFa/b7Q30HzMbs6GlmhBJUnWBL1JfSQodVuO2a+gitMlYany3QJVhuDVBFasvyH9UMrtuT0fAsQnhlOxh9fKDZEVs6/hmXQlSMothh/uUWINPeGiFoD7gCrIFFAoC3AjhzGppV1135XgAdGzCJI1NaAm1eP8KNlP8Ui0RjS1btT6B1EjHBgTKyAsxEZZnTDMrGSLzkbB1ndtd8VI4oY2MLiOFVlIognwQqy1IG1KSJtY4883gLscF+H385kddgPmQPqF3zsZyiHlN7dO1Yaj/tXZp3Kwh4DqWNvrHGjmLXo0ID27i6n/7MfJUGpnOcj5hh2WbkcEabku7VOIwLPR3JXTNINZkRPsyPFDEN8wgm7m88JGhHuynMmJ2bF2Czhf541x2aZ7rGxAz9/wIbhANuD34UNw4EaksrmLB3DAaUd8XrfzFdw0F9vcHLMi48x29X7wjdYws9udn+SXgHCNMPJBh1cDMgIraq1ivvwDKXn3TnzDqIiTBrWV6yDT3OwYBoIupmAg5EZYI63+nq5AXjAytOwZ2jEZfVhI7wSLHkKLhfjEHNtdws8JV9neLkspt94rA7Im64rBmtMB9zG3ReZK0ApMA0x69yGfEw7q0rVqQ0lmC+wK+wVOsFDFMMmsNtiarwNakJni/WxWmMI4TVlt9ZpRaPRlOBtZFAjjMREbPnc9TAh4Gq8TWGcvb7+Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#Date" = _t, #"#BFS" = _t, #"#Unit" = _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

Give me your opinion about this challenge, I believe that this is not the best way, mainly due to the lack of dynamism.

Thank you very much for your help already!

Forum Discussion

Related content

Recursive function in Power Query

Recursion Strategy

PowerBI Loop/Recursive Function

Recursive hierarchy in power query

AI functionality using Power BI Dataset