Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • Payeras_BI's avatar
    Payeras_BI
    4 years ago

    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"

     

Payeras_BI's avatar
Payeras_BI
Solution Sage
4 years ago

Anonymous ,

In case you still need transforming column types after pivoting.

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),
    #"Changed Type4" = Table.TransformColumnTypes(#"Pivoted Column", {{"Date", type date}, {"2008, Currency.Type"}, {"2009, Currency.Type"}, {"2010, Currency.Type"}, {"2011, Currency.Type"}, {"2012, Currency.Type"}, {"2013, Currency.Type"}, {"2014, Currency.Type"}, {"2015, Currency.Type"}, {"2016, Currency.Type"}, {"2017, Currency.Type"}, {"2018, Currency.Type"}, {"2019, Currency.Type"}, {"2020, Currency.Type"}, {"2021, Currency.Type"}, {"2022, Currency.Type"}, {"2023, Currency.Type"}, {"2024, Currency.Type"}, {"2025, Currency.Type"}, {"2026, Currency.Type"}, {"2027, Currency.Type"}, {"2028, Currency.Type"}, {"2029, Currency.Type"}, {"2030, Currency.Type"}, {"2031, Currency.Type"}, {"2032, Currency.Type"}, {"2033, Currency.Type"}, {"2034, Currency.Type"}, {"2035, Currency.Type"}}),
    #"Aggregate Index" = Table.AddIndexColumn(#"Changed Type4", "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"