Hey, Thanks for the reply.
Great question, hopefully the below picture better shows it, I simplified the formula from the post to make it clearer for the screenshot.
Please check this result. I believe your last number is incorrect.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"zZTfT4MwEMf/lYYnTQb0x2DjEYfGTBeTzT0te6hQWTPWkrbM8N9b2IyaEY0PBp56l1y/97m73G02zoKqPTPOyHkQ8k2AWFvz9siEAQk1zDoz++RS1Y1ZKcVEWoOScgWu7iqRGi4FLdyZFEbR1FzbqIU8skMjYOqyEUiYThUvm0jrLa0cMBJUuhU//wPph3R8kJUwzna0cZANmK9iSIg1xj7yMURRI/G0tpy6pZ4vEne9SqwVZxk/53hWVOjWAYbuGajKJlUhNRe5tVBAwnHk4agrC4E/p0lYqVjKaas+21GVN3Useb4zQL6CtWanTyD7EmgjbgomMpaBe66NVDwFnzwuwhB7wfQSJ/DJL1X/D84Ye2F4iRMOqzuTYXVnOiycaFjDQvAPC7y0B4TqSp3uiGV5tC4Dj5y+8IKb+ts6u2QaES8MOnP2MxIYeUHHdUGon5mgCfSCSQcPHlh/TjgYDgMH+zjqAQcR5AUdC016646z3b4D",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Column1 = _t,
Column2 = _t,
Column3 = _t,
Column4 = _t,
Column5 = _t,
Column6 = _t,
Column7 = _t,
Column8 = _t,
Column9 = _t
]
),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(
#"Added Index",
{{"Event Date", type date}, {"Contract currency Amount", Currency.Type}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each List.Accumulate(
{0 .. [Index]},
[a = 0, c = 0, v = 0, f = 0],
(state, current) => [
a = state[a] + #"Changed Type"[Contract currency Amount]{current},
f =
if #"Changed Type"[Rate to use]{current} = "Closing" then
Table.SelectRows(
Lookup_FX_Rates_table,
each [Currency Pair]
= #"Changed Type"[#"Currency pair (Functional-Contract)"]{current}
and [Period] = Date.EndOfMonth(#"Changed Type"[Event Date]{current})
)[Value]{0}
else
state[c] / state[a],
v = #"Changed Type"[Contract currency Amount]{current} * f,
c = state[c] + v
]
)
),
#"Expanded Custom" = Table.ExpandRecordColumn(
#"Added Custom",
"Custom",
{"f", "v"},
{"Relevant FX Rate", "Functional Currency Amount"}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Expanded Custom",
{{"Relevant FX Rate", type number}, {"Functional Currency Amount", Currency.Type}}
)
in
#"Changed Type1"
Hi Ibendlin,
Thanks very much for your response, I can see that works perfectly in that PBIX file and seems ideal (the difference on the last number is due to a slight change in the contract currency amount in your binary file from the data by $0.59).
Unfortunately when I try to utilise the same code in my model it is taking a signifcant amount of time to process the query. I have 4 years of journal CSV files that are being pulled into the query, each around 100k rows and size 110,000KB. The query has been running for over 1hr30mins currently on my filtered journals to only those in the example and is loading a vast amount of data (current progress):
This is also only the first file. I think if trying to run at the scale I need it would likely take a signficant amount of processing time.
Do you have a recommendation for how I may be able to more efficiently perform this calculation, potentially outside of PBI and then directly load the data post calculation? Willing to chat with our IT dept to allow using other tools for this calc pre-PBI load if it is significantly more efficient.
If the above is surprising - then I'll go away, complete my planned rework of the model and try it again incase I've some embedded inefficencies in there that the planned rework can remove.
Hi Ibendlin,
Thanks for the reply and apologies for the delay - I was pulled onto a few other things and had to put a pause on this.
Coming back to it now I have provided updated sample data which covers the issue with there being different leases in different currencies, and provided a comprehensive FX table.
PowerQuery Forum Help file - Blended historic FX rate 2.xlsx
In this data set you can see there are different leases which are denoted by the different "Known As" reference, and these have their own currency pairs depending on the market and contract. My FX table also has average rates within it, so have included them as it would need a different filter.
I tried to modify your code so that I could group the data by the individual leases based on the "Known as" and then run the function only on that subset of rows for each lease before expanding them back into the table (code below) but it didn't work as I get an error on the "records" column within the table.
"Expression.Error: We cannot convert the value 0 to type List.
Details:
Value=0
Type=[Type]"
#"Grouped Rows" = Table.Group(
#"Reordered Columns",
{"Known As"},
{
{"All Data", each _, type table[
Index = Int64.Type,
Market = text,
#"Event Date" = date,
Amount = number,
Description = text,
#"Reporting-Contract currency Pair" = text,
Category = text,
#"Movement type" = text,
#"Rate to use" = text
]}
}
),
#"Testing Function" = Table.AddColumn(#"Grouped Rows", "custom2", each Table.AddColumn([All Data], "records", each List.Accumulate(
{0 .. List.Count([Index]) - 1},
[a = 0.0, c = 0.0, v = 0.0, f = 0.0],
(state, current) => [
a = state[a] + [Amount]{current},
f = if [#"Rate to use"]{current} = "Closing" then
Table.SelectRows(
Lookup_FX_Rates_table,
each [Currency Pair] = [#"Reporting-Contract currency Pair"]{current} and [Period] = Date.EndOfMonth([#"Event Date"]{current}) and [Currency Rate Type] = "Current"
)[Value]{0}
else state[c] / state[a],
v = [Amount]{current} * state[f],
c = state[c] + state[v]
]
))),
#"Removed Columns" = Table.RemoveColumns(#"Testing Function",{"All Data"}),
#"Expanded custom2" = Table.ExpandTableColumn(#"Removed Columns", "custom2", {"Index”, "Market", "Known As", "Event Date", "Amount", "Description", " Reporting-Contract currency Pair", "Category", "Rate to use", "records"}, {"Index”, "Market", "Known As", "Event Date", "Amount", "Description", " Reporting-Contract currency Pair", "Category", "Rate to use", "records"}),
// Expand the Custom column into separate fields
#"Expanded Custom" = Table.ExpandRecordColumn(
#"Expanded custom2",
"records",
{"f", "v"},
{"Relevant FX Rate", "Functional Currency Amount"}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Expanded Custom",
{{"Relevant FX Rate", type number}, {"Functional Currency Amount", Currency.Type}}
)
I look forward to your response.
Thanks!
Hi @toBeTaughtIfPos, are you sure that your excel formula is correct?
I found different logic in your excel file.
- For row 34 you use sum also for row 27
- but for row 20 you sum from row 15
Hi @toBeTaughtIfPos, check this query, but don't you want to reset sumifs after each closing?
Result (same as in your excel file)
let
Source = Excel.Workbook(File.Contents("C:\Users\dufoq3\Downloads\PowerQuery Forum Help file - Blended historic FX rate.xlsx"), null, true),
FX_RatesTable = Table.PromoteHeaders(Source{[Item="Lookup_FX_Rates_table",Kind="Sheet"]}[Data]),
FX_Rates = Record.Combine(Table.AddColumn(FX_RatesTable, "R", each Record.AddField([], [Currency Pair] & "_" & Date.ToText(Date.EndOfMonth([Period]), [Format="yyyyMMdd"]), [Value]), type record)[R]),
Data = [ a = Table.PromoteHeaders(Source{[Item="Data_Journals",Kind="Sheet"]}[Data]),
b = Table.SelectColumns(a, List.Select(Table.ColumnNames(a), each not Text.StartsWith(_, "Column"))),
c = Table.SelectRows(b, each not List.Contains({null, ""}, [Event Date]))
][c],
L = List.Buffer(Table.ToRows(Table.SelectColumns(Data,{"Event Date", "Currency pair (Functional-Contract)", "Rate to use", "Contract currency Amount"}))),
Gen = List.Generate(
()=> [ x = 0, r = L{x}, w = {0, 0}, y = if r{2} = "Closing" then Record.FieldOrDefault(FX_Rates, r{1} & "_" & Date.ToText(Date.EndOfMonth(r{0}), [Format="yyyyMMdd"])) else null, z = y * r{3} ],
each [x] < List.Count(L),
each [ x = [x]+1, r = L{x},
w = { [w]{0} + [z], [w]{1} + L{x-1}{3} },
y = if r{2} = "Closing" then Record.FieldOrDefault(FX_Rates, r{1} & "_" & Date.ToText(Date.EndOfMonth(r{0}), [Format="yyyyMMdd"])) else w{0} / w{1},
z = y * r{3} ],
each {[y], [z]}
),
Merged = Table.FromColumns(Table.ToColumns(Data) & {List.Transform(Gen, each _{0}), List.Transform(Gen, each _{1})}, Value.Type(Table.FirstN(Data,0) & #table(type table[Relevant FX Rate_Gen=number, Functional Currency Amount_Gen=number], {})))
in
Merged
Helpful resources
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
