Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Else if function displaying the max date

Hi there   I want to create a calculated column which includes text and also a max date. Below is what I want to do, but M Query doesn't like it because I can only see options for a List.Max which ...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    M language is based on the row levels to get the maximum of a column for example as you refer you need to pick up the List.Max.

     

    Assuming that you want to pick up the maximum value of the duration column you need to do your column like:

    = Table.AddColumn(Between, "Column", each if [Duration] >= 60 then "60 to " & 
Number.ToText (List.Max(#"PreviousStepName"[Duration])) else if [Duration] >= 30 then "30 to 59" else if [Duration] >= 11 then "11 to 29" else "0 to 10")

    I'm assuming that your duration column is in number format that is why we need to use the Number.ToText.

     

    Also using the List.Max you need to reference the step you want to calculate the maximum and the column thats the part 

    List.Max(#"PreviousStepName"[Duration])

     

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
6 years ago

Anonymous 

I think it will be better to use DAX to create the column.

Could you please the sample data and expected output?

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous yes this can be done in PQ, I guess you want MAX across the whole data table?

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous here is a sample script that you can apply in your table, start a blank query, click advanced editor and paste the code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rcy5DQAwDALAXahT5HEez2J5/zWCQpHuhIAINBS0iiyBTnZxkEO0X5g/XaSJm1ziIY8/OulKudZFXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Value", Int64.Type}}),
    #"Group" = Table.Group(#"Changed Type", {}, {{"MaxValue", each List.Max([Value]), type number}}),
    #"Max Value" = #"Group"{0}[MaxValue],
    #"Add Column" = Table.AddColumn(#"Changed Type", "Range",each if [Value] >= 60 then "60 - " & Number.ToText(#"Max Value") else "0-60", type text)
in
    #"Add Column"

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi again parry2k  yes indeed I do.