Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Namoh
Post Partisan
Post Partisan

Max value from a column in Power Query Editor

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.

FiscalYearHighestFiscalYear
20152020
20162020
20202020
20142020
20202020
20162020

 

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?

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @Namoh ,

 

You need to add #”Change Type” before [FiscalYear].

 

max1.jpg

 

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.

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

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"

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-zhenbw-msft
Community Support
Community Support

Hi @Namoh ,

 

You need to add #”Change Type” before [FiscalYear].

 

max1.jpg

 

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!

Anonymous
Not applicable

Good to hear, please mark the solution as accepted 

amitchandak
Super User
Super User

@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/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

 this should work. Just do it on the data tab. 
 
Max of date = MAX('Table'[Date])
 
Karlos_0-1595926008828.png

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.