Forum Discussion

rachaelwalker's avatar
rachaelwalker
Resolver III
3 years ago
Solved

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)

 

 

 

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

**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
Need Help
  • rachaelwalker's avatar
    rachaelwalker
    3 years ago

    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. 

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    3 years ago

    Hi, you want to use Table.Max.

     

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

     

     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. 

    • rachaelwalker's avatar
      rachaelwalker
      Resolver III
      3 years ago

      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.

       

      Here is a screenshot of attempting Table.Max

       

      = 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

       

       

      • edhans's avatar
        edhans
        Community Champion
        3 years ago

        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.