cancel
Showing results for
Did you mean:
Frequent Visitor

[Power Query] Calculate Average For columns of the same Year

Hello, everyone!

I have the following table structure.

 Site Name Account Number 12/1/2018 11/1/2018 10/1/2018 9/1/2018 8/1/2018 7/1/2018 6/1/2018 5/1/2018 4/1/2018 3/1/2018 2/1/2018 1/1/2018 12/1/2017 11/1/2017 10/1/2017 9/1/2017 8/1/2017 Name 12 50 50 50 50 Name 2 123 3919 3919 3332 50 50 3919 3332 50 50 Name 3 123 3919 3919 3332 3919 3919 3332

What I wanto to do is create a new column with the average for the values that are in columns with same Year (Red values on table above that belong to the same line, for example).

I´ve tried this.

1 - Create a function that returns the columns with , for example, 2018 as year

```(Table)=>

let
Custom1 = Record.ToTable(Table{0}),
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Value"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",2),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Ano] = "2018")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Ano"}),
Custom2 = Table.ToList(#"Removed Columns2")
in
Custom2```

2 - Tried to create the new column using the list names above as reference.

`= Table.AddColumn(Table, "Average", each List.Average(ListarAno2018(Table)), Currency.Type)`

I think that I´m close to get it, but the code on step 2 doesn´t accept the list as reference.

Thank you all so much for any help!

2 REPLIES 2
Community Support Team

Re: [Power Query] Calculate Average For columns of the same Year

Hi @heitorleaof,

If it is convenient, could you share your desired output based on your sample data so that we can understand your scenario better.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: [Power Query] Calculate Average For columns of the same Year

Hello, @v-piga-msft!

Here´s the output that I´m looking for..(Image below)

One more thing, the new query must calculate the average regardless the number of columns with a specific year date. Therefore, it must calculate even if the number of columns with "2018" or "2017" be different of 2 as the example illustrates.

Thank you!