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

Hi Anonymous ,

Find below a different approach, please let me know the result.
Just replace "Directory" with the path to your file to test it. 

let
    Source = Csv.Document(File.Contents(Directory),[Delimiter=",", Columns=78, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Parsed Date" = Table.TransformColumns(#"Promoted Headers",{{"Time (UTC+10)", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Parsed Date", {"Time (UTC+10)"}, "Attribute", "Value"),
    QLD = Table.SelectRows(#"Unpivoted Columns", each Text.StartsWith([Attribute], "ASX Energy Contract QLD")),
    #"Renamed Columns" = Table.RenameColumns(QLD,{{"Value", "Price ($/MWh)"}, {"Time (UTC+10)", "Date"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Price ($/MWh)", Currency.Type}}, "es-US"),
    Year = Table.TransformColumns(#"Changed Type with Locale",{{"Attribute", each Text.Select(_,{"0".."9"}), type text}}),
    #"Sorted Rows" = Table.Sort(Year,{{"Date", Order.Ascending}, {"Attribute", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Price ($/MWh)", List.Sum),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",Table.ColumnNames(#"Pivoted Column")),
    #"4 years average" = Table.AddColumn(#"Filled Down", "Custom", each List.Average(
      let
        year = Date.Year([Date])
      in
        {
          Record.Field(_, Text.From(year)), 
          Record.Field(_, Text.From(year + 1)), 
          Record.Field(_, Text.From(year + 2)), 
          Record.Field(_, Text.From(year + 3))
        }
    ),
    Currency.Type
    )
in
    #"4 years average"