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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rachaelwalker
Resolver III
Resolver III

Add column showing max value from another column

I am in power query creating custom columns. I have an table that is grouped by opportunity number. I successfully added a column that shows the date from previous row. Now, I need to add a column with the MAX value of [Index]. I am not sure why I am getting errors. I have tried MAX and List.Max using similar code below. 

 

Added date from previous row = success

 

 

= Table.AddColumn(#"Grouped Rows", "PrevDateTable", each let 
AllDataTable = [FullTable],
PrevRowValue = 
  Table.AddColumn(
   AllDataTable, "Date Previous UTC", 
   each try AllDataTable [Date Updated UTC] { [Index] - 1}    otherwise [Date Became Lead UTC]
   )
     in 
        PrevRowValue)

 

 

rachaelwalker_0-1673573283861.png

 

Here is what I am trying to accomplish. Is there something I can add onto the code above to avoid combine another table. 

rachaelwalker_1-1673573498065.png

**there are multiple opportunties but this is only showing the one. So I need MAX index for each opp. 

 

Table

IndexOpportunity Numberaudit_tokenStage CurrentStage Audit ValueDate Updated UTCDate Previous UTCIndex Max
07994old_valueQualified ProspectStrong Upside5/6/2022 14:171/25/2022 16:509
17994new_valueQualified ProspectQuoted5/6/2022 14:175/6/2022 14:179
27994old_valueQualified ProspectQuoted5/6/2022 14:175/6/2022 14:179
37994new_valueQualified ProspectQualified Prospect5/6/2022 14:175/6/2022 14:179
47994old_valueQualified ProspectQualified Prospect6/17/2022 13:475/6/2022 14:179
57994new_valueQualified ProspectStrong Upside6/17/2022 13:476/17/2022 13:479
67994old_valueQualified ProspectStrong Upside8/3/2022 1:186/17/2022 13:479
77994new_valueQualified ProspectQuoted8/3/2022 1:188/3/2022 1:189
87994old_valueQualified ProspectQuoted8/3/2022 1:188/3/2022 1:189
97994new_valueQualified ProspectQualified Prospect8/3/2022 1:188/3/2022 1:189
1 ACCEPTED SOLUTION

A quick work around was to create a separate SQL query and group by Opportunity and MAX index then merged the queries. Not sure if it's the most efficient but I am now getting the data I need. Thank you for taking the time. 

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

Hi, you want to use Table.Max.

 

Table.Max([All Rows],"Value")[Value]

 

edhans_1-1673574334032.png

 The Table.Max([All Rows],"Value") function returns 1 record based on the max value of the "Value" field. The [Value] at the end pulls the actual value. Full code sample

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJLEnLL8pV0lFyzkksLgbSYYk5palKsTrRSq5BzkA+hDSEiSQWw8WMYGLBrm5wQWOwYCKQlVickgakTMACSQgBU7BAMkLADCyQghAwV4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Class"}, {{"All Rows", each _, type table [Platform=nullable text, Class=nullable text, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([All Rows],"Value")[Value])
in
    #"Added Custom"

 How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you for taking the time to help me. For the example you provided, I need the MAX index within the grouped by row so I can find the MAX index for each Opportunity. Please see screenshot. I tried using Table.Max and was able to get a little further but still receiving an error.

rachaelwalker_0-1673621731858.png

 

Here is a screenshot of attempting Table.Max

rachaelwalker_1-1673621844401.png

 

= Table.AddColumn(#"Added PrevDateTable", "MaxIndex", each let 
AllDataTable = [PrevDateTable],
MaxIndex = 
  Table.AddColumn(
   AllDataTable, "Index Max", 
   each Table.Max(AllDataTable, "Index MAX")[Index]
   )
     in 
        MaxIndex)

 

After that step, I combine all the tables. Im sure there is an easier way to go about this. I am still learning

 

 

You are going to need to provide some code samples or what the errors are. You just show the word "error" which may be a problem with how you are using it, or you may be missing a comma. No idea. 

Paste your data into the ENTER DATA feature of Excel or Power BI, do your code through the error place, and paste that code.

I cannot use your code sample above because I have no clue what the code through PrevDateTable did.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

A quick work around was to create a separate SQL query and group by Opportunity and MAX index then merged the queries. Not sure if it's the most efficient but I am now getting the data I need. Thank you for taking the time. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.