cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Min & Max Year for non blank column entries

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

 

ProjectYearBenefit1Benefit2Benefit3
Project12019 435311
Project12020133200141
Project12021281 380
Project12022   
Project22019 367 
Project22020298248262
Project22021255305348
Project22022203  
Project32019   
Project32020462467491
Project32021440 478
Project32022470  

      

Output table

 

ProjectMinYearMaxYear

Project1

20192021
Project220192022
Project320202022

 

Thank you in advance,

 

George

1 ACCEPTED SOLUTION
Super User III
Super User III

Hello @GeorgeGiannakis 

 

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"

Jimmy801_0-1602669200118.png

 

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

 

View solution in original post

6 REPLIES 6
Super User III
Super User III

Hello @GeorgeGiannakis 

 

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"

Jimmy801_0-1602669200118.png

 

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

 

View solution in original post

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

 

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

Hello @GeorgeGiannakis 

 

I can do that for you

 

BR

 

Jimmy

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

Hello

 

no, I cannot give kudoes to my self.

You can retry if you want 🙂

 

BR

 

Jimmy

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors