Showing results for 
Search instead for 
Did you mean: 
heitorleaof Frequent Visitor
Frequent Visitor

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

Hello, everyone!


I have the following table structure.


Site NameAccount Number12/1/201811/1/201810/1/20189/1/20188/1/20187/1/20186/1/20185/1/20184/1/20183/1/20182/1/20181/1/201812/1/201711/1/201710/1/20179/1/20178/1/2017
Name 125050    5050         
Name 2123391939193332 5050 39193332     5050 
Name 3123    391939193332       39193919




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


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


           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!



Community Support Team
Community Support Team

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

Hi @heitorleaof,


I have a little confused about your requirement.


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,


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.
heitorleaof Frequent Visitor
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!



Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 48 members 909 guests
Please welcome our newest community members: