Min & Max Year for non blank column entries

[Anonymous]

Hello Community,

I would appreciated it if you could help me with the below question.

I have the below table that has shows the benefits details per year of three projects.

What I want to create is a table that shows the minimum and maximum year per project, based on the non blank values of all three benefit columns.

Input table

Project	Year	Benefit1	Benefit2	Benefit3
Project1	2019		435	311
Project1	2020	133	200	141
Project1	2021	281		380
Project1	2022
Project2	2019		367
Project2	2020	298	248	262
Project2	2021	255	305	348
Project2	2022	203
Project3	2019
Project3	2020	462	467	491
Project3	2021	440		478
Project3	2022	470

Output table

Project	MinYear	MaxYear
Project1	2019	2021
Project2	2019	2022
Project3	2020	2022

Thank you in advance,

George

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you have to filter out rows where all benefits= null. Afterwards you can apply a Group where you add aggregation. One for min, one for max

Here the M-code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZC9DsIwDIRfBWXu4L/m5y3Yq06IpUslxPsL51oELWbwXSJ/si+ZpnR9rMv99uQ0JCFubhcv09FVmdM8nBghN1bFBWeLKFjlfZ5WChjZu1t99eWYRnOJCWSRVrsaNEtAYd2IFxHU2V9qM43y6DHPnz7SWBZoT2yNA6qnMaP3T5caMJhR6LNrfgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Year = _t, Benefit1 = _t, Benefit2 = _t, Benefit3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Year", Int64.Type}, {"Benefit1", Int64.Type}, {"Benefit2", Int64.Type}, {"Benefit3", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not(([Benefit1] = null) and ([Benefit2] = null) and ([Benefit3]=null))),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Project"}, {{"Min Year", each List.Min([Year]), type number}, {"Max Year", each List.Max([Year]), type number}})
in
    #"Grouped Rows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hello Jimmy801,

Thank you for your reply.

Your solution worked just fine.

Below I have pasted the code I ended up having , which of course is based on your solution.

However, I would also like to ask you if there is a way to work on filtering multiple rows by using only the Power Query Editor commands, without having to code in M language at the advanced editor?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Year", Int64.Type}, {"Benefit1", Int64.Type}, {"Benefit2", Int64.Type}, {"Benefit3", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each not (([Benefit1] = null) and ([Benefit2] = null) and ([Benefit3] = null))),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Project"}, {{"Min Year", each List.Min([Year]), type number}, {"Max Year", each List.Max([Year]), type number}})
in
    #"Grouped Rows"

Thank you,

George

[Anonymous]

Hi Jimmy801,

I face some technical issues with flagging your suggestion as solution and also giving you Kudos.

The error states that :

"Authentication failed for the action you are trying to do. This failure could be due to your browser not supporting JavaScript, JavaScript not being enabled, or trying to use the action URL directly in the browser address bar instead of clicking the link on the page. "

If you know any way to sort it out, please share it.

Kind regards,

George

[Jimmy801]

Hello @Anonymous 

I can do that for you

BR

Jimmy

[Anonymous]

Hello @Jimmy801 ,

Please do so.

Can you also do the same for the Kudos?

If yes, please go for it.

Thank you very much,

George

[Jimmy801]

Hello

no, I cannot give kudoes to my self.

You can retry if you want 🙂

BR

Jimmy