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"
Thanks for your response Payeras.
What's tripping me up is the following line
#"Changed Type4" = Table.TransformColumnTypes(Source,
PowerBI can't find the columns (e.g. Data, 2007, 2008...) because the source .csv is not in this format. PowerBI has manipulated the source .csv into this format for me.
How can I substitute source with something else, so that it works with the columns in this query (Date, 2007, 2008...)?
Cheers
Ben
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,
BenHi 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