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 CY2008 become zero (0) on 1/4/2008.

 

I would like to create a daily average price to buy options 4 years in advance (noting that options cease on 1 April as above).

See my diagram below.

 

 

I've tried using this as a guide. But I'm finding it hard to consistently enact the logic in blue: when price = 0 or blank, pull the most recent price for Date.Year[Time UTC+10] from the respective column.

 

The calculation will also need to pull values along a row for column names that are >= & <= {Date.Year[Time UTC+10 + 3}  , if that makes sense.

 

Cheers

 

 

 

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"

     

12 Replies

  • Payeras_BI's avatar
    Payeras_BI
    Solution Sage
    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"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your response Payeras_BI .

     

    I seem to encouter an EoF error at the line containing:    #Changed Type" = Table.TransformColumnTypes(Source....

     

    Here is my full console for the query:

     

    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),
    
    _t = ((type nullable text) meta [Serialized.Text = True])
    in
        type table [Date = _t, #"2008" = _t, #"2009" = _t, #"2010" = _t, #"2011", #"2012" = _t, #"2013" = _t, #"2014" = _t, #"2015" = _t, #"2016" = _t, #"2017" = _t, #"2018" = _t, #"2019" = _t, #"2020" = _t, #"2021" = _t, #"2022" = _t, #"2023" = _t, #"2024" = _t, #"2025" = _t, #"2026" = _t, #"2027" = _t, #"2028" = _t, #"2029" = _t, #"2030" = _t, #"2031" = _t, #"2032" = _t, #"2033" = _t, #"2035" = _t]
    
        #"Changed Type" = Table.TransformColumnTypes(Source, {{"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 Type", "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
        )
#"Added Custom"

     

     

    Appreciate the help.

     

    Cheers

    • Payeras_BI's avatar
      Payeras_BI
      Solution Sage
      4 years ago

      Hi again Anonymous ,

      It seems when you tried to merge my code into yours you brought some extra redundant lines.

      Please try the following (my code starts at #"Aggregate Index"):

       

      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(Source, {{"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"

       

      If this didn't work consider sharing a sample of your csv file.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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

  • 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"

     

  • 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"

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Has Payeras_BI 's solution solved your problem? if so, would you mind accept his reply as solution? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

    Best Regards,
    Community Support Team _ kalyj