Forum Discussion

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_...

Need Help

Tutorial Requests

dufoq3

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!

dufoq3

Community Champion

2 years ago

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

Forum Discussion

Recursive Function in Power Query

Related content

Recursive function in Power Query

Recursion Strategy

PowerBI Loop/Recursive Function

Recursive hierarchy in power query

AI functionality using Power BI Dataset