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.
Hi all,
I've spent my last week exploring the dephts of Power Query, aiming to process accounting data for a nice stacked visualization of the developments on some bank and liabilitity accounts:
This video tutorial by @MarcelBeug helped me a great deal in achieving this. Starting from a table with an "Inflow" and an "Outflow" value for each transaction, I could calculate the running totals / "Balance" values for each "Account".
// Calculate Balance (Translated to English) OnlyBalanceAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) < 3)), WithNetFlow = Table.AddColumn(OnlyBalanceAccounts, "Net Flow", each (if Number.FromText(Text.At([Account Number], 0)) = 1 then [Inflow] - [Outflow] else 0) + (if Number.FromText(Text.At([Account Number], 0)) = 2 then [Outflow] - [Inflow] else 0), type number), OnlyDateColumn = OnlyBalanceAccounts[Date], FirstDate = List.Min(OnlyDateColumn), LastDate = List.Max(OnlyDateColumn), NumberOfDates = Number.From(LastDate - FirstDate) +1, EachDate = List.Dates(FirstDate, NumberOfDates, #duration(1, 0, 0, 0)), EachDateTable = Table.FromColumns({EachDate},{"Date"}), OnlyAccountColumn = OnlyBalanceAccounts[Account Number], WithoutDuplicates = List.Distinct(OnlyAccountColumn), EachAccountTable = Table.FromColumns({WithoutDuplicates},{"Account Number"}), CombinedTable = Table.AddColumn(EachAccountTable, "Date", each EachDateTable), ExpandedDatesXAccounts = Table.ExpandTableColumn(CombinedTable, "Date", {"Date"}, {"Date"}), Stacked = Table.Combine({WithNetFlow , ExpandedDatesXAccounts}), Sorted = Table.Buffer(Table.Sort(Stacked,{{"Account Number", Order.Ascending}, {"Date", Order.Ascending}, {"OriginalID", Order.Ascending}})), GroupedWithFunction = Table.Group(Sorted, {"Account Number"}, {{"Tables", AddBalance, Value.Type(AddBalance(Sorted))}}), Expanded = Table.ExpandTableColumn(GroupedWithFunction, "Tables", {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}, {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}), Cleaned = Table.RemoveColumns(Expanded ,{"Net Flow"}), OnlyPLAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) >= 3)), AllAccounts = Table.Combine({Cleaned, OnlyPLAccounts})
And the function for the "Balance" calculation:
(Table as table) as table => let Source = Table, NetFlows = List.Buffer(Source[Net Flow]), Custom = Source, WithIndex = Table.AddIndexColumn(Custom, "Index", 1, 1), WithBalance = Table.AddColumn(WithIndex, "Balance", each Number.Round(List.Sum(List.FirstN(NetFlows,[Index])),2), type number), Cleaned = Table.RemoveColumns(WithBalance,{"Index"}) in Cleaned
However, there are some things that I didnt'get:
Grateful for everything I can learn from you!
yours,
Luke
Hi all,
I've spent my last week exploring the dephts of Power Query, aiming to process accounting data for a nice stacked visualization of the developments on some bank and liabilitity accounts:
This video tutorial by @MarcelBeug helped me a great deal in achieving this. Starting from a table with an "Inflow" and an "Outflow" value for each transaction, I could calculate the running totals / "Balance" values for each "Account".
As I also needed a "Balance" for each "Date" with no bookings, this is the code I ended up with:
// Calculate Balance (Translated to English) OnlyBalanceAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) < 3)), WithNetFlow = Table.AddColumn(OnlyBalanceAccounts, "Net Flow", each (if Number.FromText(Text.At([Account Number], 0)) = 1 then [Inflow] - [Outflow] else 0) + (if Number.FromText(Text.At([Account Number], 0)) = 2 then [Outflow] - [Inflow] else 0), type number), OnlyDateColumn = OnlyBalanceAccounts[Date], FirstDate = List.Min(OnlyDateColumn), LastDate = List.Max(OnlyDateColumn), NumberOfDates = Number.From(LastDate - FirstDate) +1, EachDate = List.Dates(FirstDate, NumberOfDates, #duration(1, 0, 0, 0)), EachDateTable = Table.FromColumns({EachDate},{"Date"}), OnlyAccountColumn = OnlyBalanceAccounts[Account Number], WithoutDuplicates = List.Distinct(OnlyAccountColumn), EachAccountTable = Table.FromColumns({WithoutDuplicates},{"Account Number"}), CombinedTable = Table.AddColumn(EachAccountTable, "Date", each EachDateTable), ExpandedDatesXAccounts = Table.ExpandTableColumn(CombinedTable, "Date", {"Date"}, {"Date"}), Stacked = Table.Combine({WithNetFlow , ExpandedDatesXAccounts}), Sorted = Table.Buffer(Table.Sort(Stacked,{{"Account Number", Order.Ascending}, {"Date", Order.Ascending}, {"OriginalID", Order.Ascending}})), GroupedWithFunction = Table.Group(Sorted, {"Account Number"}, {{"Tables", AddBalance, Value.Type(AddBalance(Sorted))}}), Expanded = Table.ExpandTableColumn(GroupedWithFunction, "Tables", {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}, {"OriginalID", "Date", "Reference", "Booking Description", "Document Link", "Business Partner", "Project", "Item Description", "Other Stuff", "Currency", "FX Rate", "Tag is ""Local""", "VAT Category", "Booking Type is ""Reset""", "Inflow", "Outflow", "Net Flow", "Balance"}), Cleaned = Table.RemoveColumns(Expanded ,{"Net Flow"}), OnlyPLAccounts = Table.SelectRows(Input, each (Number.FromText(Text.At([Account Number], 0)) >= 3)), AllAccounts = Table.Combine({Cleaned, OnlyPLAccounts})
And the function for the "Balance" calculation:
(Table as table) as table => let Source = Table, NetFlows = List.Buffer(Source[Net Flow]), Custom = Source, WithIndex = Table.AddIndexColumn(Custom, "Index", 1, 1), WithBalance = Table.AddColumn(WithIndex, "Balance", each Number.Round(List.Sum(List.FirstN(NetFlows,[Index])),2), type number), Cleaned = Table.RemoveColumns(WithBalance,{"Index"}) in Cleaned
However, there are some things that I didnt'get:
Grateful for everything I can learn from you!
yours,
Luke
HI @LukeCH,
#1, If you mean extract value from multiple iterators, I think you need to use custom function to extract value from external looping.
#2, Please share some sample data to help us clarify your data structure for test.
#3, You can try to write a measure to calculate result based on last value.(if you are work with table visual, you can setup summary mode as 'last' to show last records based current category)
Regards,
Xiaoxin Sheng
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.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |