Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi @Gabriel_Pedri, could you provide sample data as table so we can copy/paste and expected result based on sample data please?
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
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
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.