Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi.
I've got a very easy question, but somehow I can't get it to work.
I have a table with multiple columns, one of them is called FiscalYear.
I want to add another Calculated Column which shows the highest number of the column FiscalYear.
FiscalYear | HighestFiscalYear |
2015 | 2020 |
2016 | 2020 |
2020 | 2020 |
2014 | 2020 |
2020 | 2020 |
2016 | 2020 |
The outcome should be like above table.
I use the following but it gives back ERROR.
"HighestFiscYear", each ([FiscalYear] = List.Max (Table[FiscalYear]))
I tried using MAX but that's not recognised because it's a DAX measure and not useable in PQE.
Am I close or way off?
Any thoughts?
Solved! Go to Solution.
Hi @Namoh ,
You need to add #”Change Type” before [FiscalYear].
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi,
I am sure there is a better way but this M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FiscalYear", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Max", each List.Max([FiscalYear]), type number}}),
Joined = Table.Join(#"Added Custom", "Custom", #"Grouped Rows", "Custom"),
#"Removed Columns" = Table.RemoveColumns(Joined,{"Custom"})
in
#"Removed Columns"
Thanks
You are welcome.
Hi @Namoh ,
You need to add #”Change Type” before [FiscalYear].
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hello,
I know this one is a bit old, but can you explain to me why adding the previous step to the code makes it work?
Thanks!
Late response, but for anyone else going through this answer, the reason it works is because you need to find the List.Max() of a list. If you just use the column name alone, it will use the value in that column within the same row - a single value - and throw an error since it's not a list. If you instead refer to it by "<previous step>"[column_name] (or <table_name>[column_name] depending on your query) you are looking for the List.Max() of that full array and it will return a value.
Thanks, this methiod worked.
I had to change "Changed Type" into my own column name and that did the trick!
Good to hear, please mark the solution as accepted
@Namoh , refer
http://dataap.org/blog/2019/12/19/min-and-max-value-in-power-query-power-bi/
https://blog.crossjoin.co.uk/2019/08/07/power-query-m-optimisation-part-1/
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
89 | |
87 | |
35 | |
35 |
User | Count |
---|---|
154 | |
100 | |
83 | |
63 | |
54 |