Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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 @Anonymous
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.