Recursive Function in Power Query

[Gabriel_Pedri]

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

[dufoq3]

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]

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 · Accepted solution]

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]

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!

[dufoq3]

Try this:

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(_, "#")),
    GroupedRowsMain = Table.Group(RenameColumns, {"BFS", "Unit"}, {{"All", each _, type table [Date=nullable date, BFS=nullable text, Unit=nullable text, Account=nullable text, BRL=nullable number, USD=nullable number, EUR=nullable number]}}),
    
    fn_Transform = 
        (t as table)=>
        let
            InitialCashBalance = Table.SelectRows(t, each ([Account] = "Initial Cash Balance")),
            StepBack = t,
            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(ReplacedIndex))),
            CombinedWith_SortedRows = Table.Combine(List.Transform(List.Zip({ SortedRows[All], TableSplitAndReverseOrder }), Table.Combine)),
            FilledDown = Table.FillDown(CombinedWith_SortedRows,{"Date", "BFS", "Unit"}),
            RemovedIndex = Table.RemoveColumns(FilledDown, {"Index"})
        in 
            RemovedIndex,
    Ad_Transform = Table.AddColumn(GroupedRowsMain, "Transform", each fn_Transform([All]), type table),
    Transform = Table.Combine(Ad_Transform[Transform])
in
    Transform