Forum Discussion

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

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"

Average based on column name and a date reference.xlsx19 KB

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

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

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

v-yanjiang-msft

Community Support

4 years ago

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.

Anonymous
Not applicable
4 years ago
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

Forum Discussion

Average based on column name and a date reference

Related content

Average excepts only column reference

UDF with variable table reference and fixed column reference

DAXUDF Reference a column

Weekly Average

Referring Same Column - Previous Value