Forum Discussion
Average based on column name and a date reference
Hi, I have a data source structured as follows. It lists daily prices for calendar year stock options. Prices are valid up to 1 April for the respective year you would buy for, e.g. prices for C...
Hi Anonymous ,
See if this helps you.
let Origen = Table.FromRows( Json.Document(Binary.Decompress(Binary.FromText("dcu7DcAgDIThXVyfhF+AmQWx/xrgoEhpUn3F3T8naRS2osxBIB8wSRXd0oBo2iDjSAuTTL5FoLZbRP0r+BT+Fjkauh8rI+Semz7ntQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"2008" = _t, #"2009" = _t, #"2010" = _t, #"2011" = _t, #"2012" = _t] ), #"Tipo cambiado" = Table.TransformColumnTypes(Origen, {{"Date", type date}, {"2008", Currency.Type}, {"2009", Currency.Type}, {"2010", Currency.Type}, {"2011", Currency.Type}, {"2012", Currency.Type}}), #"Índice agregado" = Table.AddIndexColumn(#"Tipo cambiado", "Index", 0, 1, Int64.Type), #"Personalizada agregada" = Table.AddColumn( #"Índice agregado", "4 years average", each List.Average( { List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date])))), 0, [Index] + 1)), List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 1))), 0, [Index] + 1)), List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 2))), 0, [Index] + 1)), List.Last(List.Range(List.RemoveNulls(Table.Column(#"Índice agregado", Text.From(Date.Year([Date]) + 3))), 0, [Index] + 1)) } ), Currency.Type ) in #"Personalizada agregada"
Hi Anonymous ,
According to your description, modify it like this:
let
Source = Csv.Document(File.Contents("directory"),[Delimiter=",", Columns=78, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Removed Columns" = Table.RemoveColumns(Source,{"Column78"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time (UTC+10)", type datetimezone}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Time (UTC+10)", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Time (UTC+10)"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Price ($/MWh)"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price ($/MWh)", Currency.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type3","ASX Energy Contract ","",Replacer.ReplaceText,{"Attribute"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," Calendar Year Base Load Futures Price ($)","",Replacer.ReplaceText,{"Attribute"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute] = "QLD 2007" or [Attribute] = "QLD 2008" or [Attribute] = "QLD 2009" or [Attribute] = "QLD 2010" or [Attribute] = "QLD 2011" or [Attribute] = "QLD 2012" or [Attribute] = "QLD 2013" or [Attribute] = "QLD 2014" or [Attribute] = "QLD 2015" or [Attribute] = "QLD 2016" or [Attribute] = "QLD 2017" or [Attribute] = "QLD 2018" or [Attribute] = "QLD 2019" or [Attribute] = "QLD 2020" or [Attribute] = "QLD 2021" or [Attribute] = "QLD 2022" or [Attribute] = "QLD 2023" or [Attribute] = "QLD 2024" or [Attribute] = "QLD 2025")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Attribute", Order.Ascending}, {"Time (UTC+10)", Order.Ascending}}),
#"Replaced Value2" = Table.ReplaceValue(#"Sorted Rows","QLD","",Replacer.ReplaceText,{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value2", List.Distinct(#"Replaced Value2"[Attribute]), "Attribute", "Price ($/MWh)", List.Sum),
#"Aggregate Index" = Table.AddIndexColumn(#"Pivoted Column", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(
#"Aggregate Index",
"4 years average",
each List.Average(
{
List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date])))), 0, [Index] + 1)),
List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 1))), 0, [Index] + 1)),
List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 2))), 0, [Index] + 1)),
List.Last(List.Range(List.RemoveNulls(Table.Column(#"Aggregate Index", Text.From(Date.Year([Date]) + 3))), 0, [Index] + 1))
}
),
Currency.Type
)
in
#"Added Custom"Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks v-yanjiang-msft this seems to work.
Issue now is the load is 100's of MB, and hasn't stopped.
Have you experienced this before with other calculations?
Cheers,
Ben
Hi Anonymous ,
Do you mean it loads slowly because the data model is too big.
Best Regards,
Community Support Team _ kalyjThe data source is only 600kB. I'm unsure why introducing a new 'average' column would increase the size of the database >100x?
Cheers
Ben
Hi Anonymous ,
Complex operations do use a lot of CPU and memory, maybe that's causing it to load slowly.
Best Regards,
Community Support Team _ kalyj